Как восстановить несколько баз данных в SQL Server (3 метода)

Вы можете восстановить несколько баз данных SQL одновременно

К сожалению, графический интерфейс SSMS поддерживает только восстановление одной базы данных за один раз. Если вы создали резервные копии нескольких баз данных в нескольких файлах bak, можно ли их все восстановить одновременно?

Ответ - ДА. В общем, вы можете использовать действующий сценарий T-SQL для восстановления нескольких баз данных в одном проходе, но только если файлы резервной копии названы точно так же, как соответствующие базы данных и не содержат дополнительной информации, например, даты/времени.

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

Необходимые условия восстановления нескольких баз данных в SQL Server

Восстановление нескольких баз данных в SQL Server может быть сложным процессом. Чтобы минимизировать время простоя, перед началом требуется выполнение нескольких предварительных условий:

• Иметь административные привилегии и доступ к файлам резервных копий.
• Должна быть только одна резервная копия базы данных в каждом файле резервной копии.
• В каждом файле резервной копии базы данных должны присутствовать только два файла: файл журнала (log) и файл данных (data).
• Поскольку мы используем имя базы данных для именования ее физических файлов на диске, символы, которые могут использоваться для имен физических файлов новой восстанавливаемой базы данных, должны быть разрешены.

Способ 1. Восстановление нескольких баз данных из файлов bak с использованием xp_cmdshell

Вы можете использовать сценарий для восстановления нескольких баз данных в SQL Server из папки. Но перед этим, пожалуйста, подключитесь к экземпляру и включите xp_cmdshell.

1. Нажмите Новый запрос и введите следующую команду:

-- Чтобы разрешить изменение расширенных параметров.
EXEC sp_configure "show advanced options", 1;
GO
-- Чтобы обновить текущее значение для расширенных параметров.
RECONFIGURE;
GO
-- Чтобы включить функцию.
EXEC sp_configure "xp_cmdshell", 1;
GO
-- Чтобы обновить текущее значение для этой функции.
RECONFIGURE;
GO

Выполните запрос, и функция будет включена.

2. Затем вы можете восстановить несколько баз данных SQL Server с помощью скрипта (замените "D:\backup\" на свою собственную папку, которая содержит все файлы резервных копий):

DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell "dir /B D:\backup\*.bak"
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX) = "RESTORE DATABASE [" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) "] FROM DISK = N"'D:\backup\" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) ".bak"' WITH FILE = 1, NOUNLOAD, STATS = 10"
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

✎Примечание: Если вы получаете ошибку «Конец журнала для базы данных не был резервирован. Используйте BACKUP LOG WITH NORECOVERY, чтобы создать резервную копию журнала, если он содержит работы, которые вы не хотите потерять. Используйте REPLACE или STOPAT параметры оператора RESTORE для перезаписи содержимого журнала», решение такое, как сообщение подсказки.

Например, вы можете изменить команду восстановления на "...WITH FILE = 1, REPLACE, NOUNLOAD, STATS = 10"

Способ 2. Создание скрипта для восстановления всех баз данных в SQL Server

В качестве альтернативы вы можете использовать скрипт для генерации команд восстановления всех баз данных в SQL Server и объединить их в один новый скрипт для выполнения.

1. Нажмите Новый Запрос и введите следующие команды в окно SQLQuery:

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = "D:\Backup\" -- Расположение резервных копий
SELECT "RESTORE DATABASE[" NAME "] FROM DISK = "'" @folderpath name ".bak"' WITH NORECOVERY,
REPLACE, STATS = 5"
FROM master.sys.databases
WHERE name NOT IN ("master","model","msdb","tempdb","distribution")

Это сгенерирует несколько команд для восстановления нескольких баз данных в SQL Server из файлов bak с соответствующими именами.

2. Щелкните правой кнопкой мыши на любой команде в Результаты, выберите Выбрать все и затем Копировать их (или используйте Ctrl A и Ctrl C).

3. Вставьте эти команды в окно SQLQuery в качестве нового скрипта для Выполнения. Он восстановит все базы данных SQL Server из файлов bak с соответствующими именами файлов.

Способ 3. Резервное копирование и восстановление нескольких баз данных SQL с помощью графического интерфейса (самый простой)

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

AOMEI Cyber Backup - надежное решение для централизованного управления резервным копированием и восстановлением SQL баз данных на всех настольных компьютерах, ноутбуках и серверах в пределах локальной сети (LAN).

Оно работает на Windows 11/10/8.1/8/7, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2) и поддерживает резервное копирование SQL Server с версии 2005 по 2022. Кроме того, оно также поддерживает резервное копирование виртуальных машин Hyper-V и VMware.

• Запустите загруженный файл .exe для установки AOMEI Cyber Backup и запустите его основной интерфейс. Кликните Источник данных -> Microsoft SQL -> Добавить Microsoft SQL.

• Выберите Скачать программу-прокси или Скопировать ссылку для загрузки и установки программы-агентана устройстве с установленным SQL Server. Затем переключитесь на Already installed proxy, чтобы выбрать устройство.
• Далее, кликните -> Аутентификация для проверки базы данных. Вы можете выбрать Windows аутентификацию или SQL аутентификацию.

Вот и все, теперь вы можете запланировать резервное копирование данных для добавленного SQL Server.

◉ Как сделать резервное копирование нескольких баз данных в SQL Server:

1. Кликните Задача резервного копирования -> Создать новую задачу, и выберите тип резервного копирования Microsoft SQL резервное копирование.

2. Нажмите на Имя устройства, чтобы указать экземпляр SQL и базы данных для резервного копирования. Вы можете выбрать одну или несколько баз данных по мере необходимости.

3. Выберите Цель хранения для сохранения резервных копий SQL. Вы можете указать локальный путь или сетевой путь.

4. Настройте Расписание для выполнения резервного копирования базы данных SQL ежедневно, еженедельно или ежемесячно, и выберите метод резервного копирования - Полный, Инкрементный или Дифференциальный.

Нажмите Начать резервное копирование, чтобы создать и выполнить задачу. Как только она завершится, вы найдете ее в разделе Задачи резервного копирования.

✍Более полезные функции:

• Очистка резервных копий помогает автоматически удалять старые версии резервных копий и тем самым экономить место на диске.
• Email-уведомление позволяет получать электронные уведомления при возникновении ошибок или успешном выполнении задачи.
• Архивация помогает архивировать файлы резервных копий на хранилище AWS S3.

◉ Как восстановить несколько баз данных из резервной копии:

1. Нажмите Задачи резервного копирования на панели меню слева, найдите задачу, которую вы хотите восстановить, и нажмите -> Восстановить.

2. Выберите версию резервной копии, из которой вы хотите восстановить базы данных SQL.

3. Затем выберите целевое место, на которое вы хотите восстановить. Имеется 2 варианта:

• Восстановление на исходное место (по умолчанию): восстановление на месте, которое позволяет вернуть базы данных SQL к более старой версии.
• Восстановление на новое место: выберите другое место для выполнения восстановления из-за нехватки места. Вы можете указать имя новой базы данных и изменить место хранения.

Перезаписать базу данных с тем же именем: Если вы установите эту опцию, резервные копии баз данных перезапишут целевые базы данных с теми же именами. Если вы не установите эту опцию, целевые базы данных с теми же именами будут пропущены во время восстановления.

4. После всех настроек нажмите Начать восстановление, чтобы начать процесс восстановления, и дождитесь его завершения. Вы можете нажать на кнопку , чтобы увидеть подробности восстановления или отменить его.

✍Заметки:

• "Восстановить в исходное местоположение" должно подтверждение существования исходного местоположения. В противном случае вы можете выбрать только "Восстановить в новое местоположение".
• "Восстановить в исходное местоположение" перезапишет или удалит исходные данные базы данных, если в исходной базе данных есть важные данные, рекомендуется выбрать "Восстановить в новое местоположение".

Вывод

Графический интерфейс SSMS для восстановления позволяет восстанавливать только одну базу данных за раз. Если вы хотите одновременно восстановить несколько баз данных в SQL Server, наиболее распространенным подходом является использование сценария T-SQL. Однако у него существуют жесткие ограничения на имена файлов резервных копий и затруднения при восстановлении баз данных на другом экземпляре. Поэтому вы также можете попробовать программное обеспечение для резервного копирования SQL - AOMEI Cyber Backup.

Оно сделает вашу работу гораздо проще. Например, вы можете автоматически создавать резервные копии SQL-баз данных всего несколькими простыми кликами и одинаково простой операцией восстанавливать несколько баз данных одновременно.