Как восстановить базу данных SQL Server из .bak файла в другую базу?

← На главную

При работе с базами данных SQL Server возникает частая задача: восстановить резервную копию (файл .bak) не в исходную базу, а в совершенно новую или уже существующую, но с другим именем. Это может быть необходимо для тестирования обновлений, разработки, создания тестовых сред или просто для переноса данных. Многие пользователи сталкиваются с затруднениями, особенно когда попытка восстановления завершается ошибкой. Рассмотрим подробно, как правильно выполнить эту операцию и избежать распространенных проблем. При регулярном обслуживании таких окружений автоматическое архивирование, обновление и тестирование нескольких баз значительно экономит время администратора.

Первоначальный вопрос, можно ли восстановить базу данных SQL Server из файла .bak в другую базу, однозначно имеет утвердительный ответ — да, это возможно и является стандартной операцией. Однако, как показывает практика, без знания определенных нюансов процесс может быть осложнен ошибками.

Одной из наиболее частых ошибок, с которой мы можем столкнуться, является следующая:

ЗАГОЛОВОК: Microsoft SQL Server Management Studio

Действие Восстановить завершилось неудачно для объекта "Сервер" "SQL-SERV\GB4SQLSERV". (Microsoft.SqlServer.SmoExtended)

ДОПОЛНИТЕЛЬНЫЕ СВЕДЕНИЯ:

При выполнении инструкции или пакета Transact-SQL возникло исключение. (Microsoft.SqlServer.ConnectionInfo)

Резервный набор данных содержит копию базы данных, отличной от существующей базы данных "zikgu_31_old_1".

RESTORE DATABASE прервано с ошибкой. (Microsoft SQL Server, ошибка: 3154)

Эта ошибка, известная как Ошибка: 3154, означает, что вы пытаетесь восстановить резервную копию базы данных, которая по своим внутренним метаданным отличается от целевой базы данных, в которую вы пытаетесь ее восстановить. SQL Server по умолчанию предотвращает такие действия, чтобы избежать случайной перезаписи данных. Давайте разберем по шагам, как правильно выполнить восстановление и обойти эту ошибку.

Подготовка к восстановлению базы данных

Прежде чем приступить к процессу восстановления, важно убедиться, что все предварительные условия соблюдены. Это поможет избежать многих потенциальных проблем, а для очистки тестовых сред от лишней информации часто применяется выборочное удаление данных организаций:

Проверка целостности резервной копии: Если вы сомневаетесь в качестве файла .bak, особенно если он был создан во время активной работы базы данных, рекомендуем сначала проверить его целостность. Для этого воспользуйтесь командой RESTORE VERIFYONLY. Она не восстанавливает базу данных, но проверяет, можно ли использовать файл резервной копии для восстановления.


    RESTORE VERIFYONLY FROM DISK = N'C:\Путь\К\Вашему\Файлу\Backup.bak';

Если эта команда завершится без ошибок, значит, файл резервной копии читаем и, скорее всего, пригоден для восстановления.

Достаточное место на диске: Убедитесь, что на диске, где будут размещены файлы данных (.mdf) и журнала транзакций (.ldf) новой базы данных, достаточно свободного места. Недостаток места приведет к сбою восстановления.
Права доступа к файлам и каталогам: Учетная запись службы SQL Server должна иметь полные разрешения на чтение файла .bak и на запись в каталоги, где будут созданы файлы .mdf и .ldf новой базы данных.
Отсутствие активных соединений: Если вы восстанавливаете базу данных поверх существующей, убедитесь, что к ней нет активных соединений. При наличии соединений восстановление может быть заблокировано. В SSMS есть специальная опция для принудительного закрытия таких соединений.

Восстановление через SQL Server Management Studio (SSMS)

Для большинства пользователей самым удобным способом является использование графического интерфейса SQL Server Management Studio. Разберем процесс по шагам:

Открытие мастера восстановления:
- В Object Explorer (Обозреватель объектов) SSMS щелкните правой кнопкой мыши по узлу Databases (Базы данных).
- Выберите Restore Database... (Восстановить базу данных...).
Выбор источника и назначения:
- На вкладке General (Общие) в разделе Source (Источник) выберите Device (Устройство) и нажмите кнопку с многоточием (...).
- В открывшемся окне Select backup devices (Выбор устройств резервного копирования) нажмите Add (Добавить) и укажите путь к вашему файлу .bak. Нажмите OK.
- После выбора файла, в разделе Destination (Назначение) в поле Database (База данных) вы увидите имя базы данных, из которой был сделан бэкап. Это крайне важный момент! Здесь вам необходимо изменить имя на то, которое вы хотите присвоить новой базе данных. Например, если бэкап был сделан из OldDatabaseName, а вы хотите восстановить его как NewDatabaseName, введите NewDatabaseName в этом поле. Если вы восстанавливаете поверх существующей базы данных с другим именем, здесь также нужно указать имя целевой базы данных.
Перемещение файлов данных и журнала (MOVE):
- Перейдите на вкладку Files (Файлы).
- В таблице Restore database files as (Восстановить файлы базы данных как) вы увидите логические имена файлов данных (.mdf) и журнала (.ldf) из резервной копии, а также их текущие пути.
- Для каждого файла обязательно измените путь в столбце "Restore As" на новое местоположение. Если вы восстанавливаете базу данных под новым именем, или если по исходным путям уже существуют другие файлы, это изменение критично. Например, измените C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\OldDatabaseName.mdf на D:\SQLData\NewDatabaseName.mdf и аналогично для файла журнала.
- Если этого не сделать, SQL Server попытается использовать исходные пути, что приведет к ошибке, если файлы по этим путям уже существуют или пути недоступны.
Параметры восстановления (OPTIONS):
- Перейдите на вкладку Options (Параметры).
- Здесь находятся ключевые настройки для устранения ошибки 3154:
  - Установите флажок Overwrite the existing database (WITH REPLACE) (Перезаписать существующую базу данных (WITH REPLACE)). Эта опция позволяет SQL Server принудительно перезаписать целевую базу данных, даже если ее имя или содержимое не соответствует резервной копии. Без этой опции вы получите ошибку 3154.
  - Установите флажок Close existing connections to destination database (Закрыть существующие соединения к базе данных назначения). Это гарантирует, что все активные сессии, подключенные к целевой базе данных, будут принудительно завершены, что позволит процессу восстановления получить эксклюзивный доступ.
  - Убедитесь, что для параметра Recovery state (Состояние восстановления) выбрано RESTORE WITH RECOVERY (Восстановить с восстановлением). Это состояние по умолчанию и необходимо для того, чтобы база данных стала полностью доступной после завершения восстановления.
Запуск восстановления:
- Нажмите OK. SQL Server начнет процесс восстановления.
- Если все параметры указаны верно, процесс должен завершиться успешно, и новая база данных появится в списке в Object Explorer.

Восстановление с помощью Transact-SQL

Для более опытных пользователей или для автоматизации процессов восстановления часто используется Transact-SQL. Это дает более точный контроль над каждым параметром. Рассмотрим процесс на примере:

Получение логических имен файлов из резервной копии:
Прежде чем выполнить команду RESTORE DATABASE, нам необходимо узнать логические имена файлов данных и журнала, которые содержатся в файле .bak. Это можно сделать с помощью команды RESTORE FILELISTONLY:
```
RESTORE FILELISTONLY
FROM DISK = N'C:\Путь\К\Вашему\Файлу\Backup.bak';
```
Результатом выполнения этой команды будет таблица с информацией о файлах в резервной копии. Нас будут интересовать столбцы LogicalName, PhysicalName, Type. Обычно файл данных имеет Type = 'D', а файл журнала Type = 'L'.

Предположим, логические имена ваших файлов: LogicalNameDataFile (для .mdf) и LogicalNameLogFile (для .ldf).
Выполнение команды RESTORE DATABASE:
Теперь мы можем использовать команду RESTORE DATABASE с необходимыми опциями:
```
USE master;
GO

ALTER DATABASE [NewDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE [NewDatabaseName]
FROM DISK = N'C:\Путь\К\Вашему\Файлу\Backup.bak'
WITH
    FILE = 1,                 -- Номер резервного набора данных в файле .bak (обычно 1)
    MOVE N'LogicalNameDataFile' TO N'D:\SQLData\NewDatabaseName.mdf',
    MOVE N'LogicalNameLogFile' TO N'D:\SQLLogs\NewDatabaseName_log.ldf',
    NOUNLOAD,                 -- Оставляет резервную копию загруженной для потенциальных дополнительных действий
    REPLACE,                  -- Разрешает перезапись существующей базы данных
    STATS = 10;               -- Показывает прогресс восстановления каждые 10 процентов
GO

ALTER DATABASE [NewDatabaseName] SET MULTI_USER;
GO
```
Разберем параметры команды:
- USE master; и ALTER DATABASE [NewDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;: Эти команды используются для принудительного отключения всех пользователей от целевой базы данных [NewDatabaseName] и перевода ее в однопользовательский режим перед восстановлением. Это аналог опции "Close existing connections" в SSMS. Если целевой базы данных еще не существует, то ALTER DATABASE можно пропустить.
- RESTORE DATABASE [NewDatabaseName]: Указываем имя новой или целевой базы данных.
- FROM DISK = N'C:\Путь\К\Вашему\Файлу\Backup.bak': Указываем полный путь к файлу резервной копии.
- WITH FILE = 1: Если файл .bak содержит несколько резервных копий, этим параметром мы указываем, какую именно копию использовать (по умолчанию 1, если копия одна).
- MOVE N'LogicalNameDataFile' TO N'D:\SQLData\NewDatabaseName.mdf': Эта опция (и аналогичная для файла журнала) обязательна для восстановления в другое место или под другим именем. LogicalNameDataFile (и LogicalNameLogFile) — это логические имена файлов, полученные из RESTORE FILELISTONLY, а пути после TO — это новые физические пути, куда будут помещены файлы.
- NOUNLOAD: Указывает, что лента (или файл) остается смонтированной после завершения операции RESTORE. В контексте файловых бэкапов это обычно некритично, но является частью стандартной практики.
- REPLACE: Это эквивалент флажка "Overwrite the existing database" в SSMS. Эта опция необходима для перезаписи существующей базы данных или для восстановления базы данных с другим именем.
- STATS = 10: Показывает индикатор выполнения восстановления в окне сообщений SSMS, обновляя его каждые 10 процентов.
- ALTER DATABASE [NewDatabaseName] SET MULTI_USER;: После успешного восстановления переводит базу данных обратно в многопользовательский режим, чтобы пользователи могли к ней подключаться.

Важные аспекты и возможные трудности

Резервная копия, сделанная во время работы: Если резервная копия была создана "на лету" без остановки базы данных или использования корректных методов резервного копирования (например, простое копирование файлов базы данных), это может привести к повреждению или несогласованности данных. Резервные копии, созданные средствами SQL Server (через SSMS или T-SQL BACKUP DATABASE), всегда являются транзакционно согласованными, даже если база данных активна. Ошибка 3154 обычно не связана с повреждением, а с несоответствием имен, но стоит иметь в виду.
Версии SQL Server: Обычно можно восстановить резервную копию, созданную в более старой версии SQL Server, на более новой версии. Однако невозможно восстановить резервную копию, созданную в более новой версии SQL Server, на более старой версии. Всегда убедитесь, что целевой сервер SQL Server либо той же версии, либо более новой.
Состояние базы данных после восстановления: По умолчанию SQL Server восстанавливает базу данных в режиме WITH RECOVERY, что делает ее полностью доступной сразу после завершения операции. Опции WITH NORECOVERY или WITH STANDBY используются в более сложных сценариях, таких как восстановление цепочки бэкапов (полный + дифференциальный + журналы транзакций) или настройка зеркалирования/log shipping. Для стандартного восстановления в новую базу данных WITH RECOVERY является правильным выбором.

Таким образом, восстановление базы данных SQL Server из .bak файла в другую базу — это стандартная и относительно простая операция при соблюдении нескольких ключевых правил: изменение имени целевой базы данных, корректировка путей файлов (MOVE) и использование опции перезаписи (REPLACE). Понимание этих моментов позволяет успешно выполнять задачу без лишних затруднений. После завершения миграции учета автоматическое сравнение бухгалтерских и налоговых проводок упрощает поиск расхождений между базами.