Disclaimer:
Скрипты написаны для одной конкретной ситуации: это dev-окружение, все базы в простой модели восстановления, файлы данных и журналы транзакций лежат в одной куче.
Всё, что написано дальше относится только к этой ситуации, но вы можете без особых усилий допилить их под себя (свои условия).
В скриптах не используются новомодные STRING_AGG и прочие приятные штуки, поэтому работать всё должно начиная с SQL Server 2008 (или 2008 R2, не помню где появилось сжатие бэкапов). Для более старых версий нужно убрать WITH COMPRESSION из команды бэкапа, но тогда разницы по времени с копированием файлов может уже и не быть.
Конечно, самым быстрым способом было бы просто переподключить полку с дисками к новому серверу, но это был не наш вариант. Detach — копирование — Attach рассматривался, но не подошёл, поскольку канал был довольно узким и перенос БД в несжатом виде занял бы довольно большой промежуток времени.
В итоге, решили, что будем делать бэкап с компрессией на шару на новом сервере, а там уже восстанавливать. Железо и на старой, и на новой локации неплохое, бэкап жмётся неплохо, выигрыш по времени тоже неплохой.
Так был написан «генератор скриптов»:
DECLARE @unc_backup_path AS varchar(max) = '\\newServer\backup_share\' , @local_backup_path AS varchar(max) = 'E:\Backup\' , @new_data_path as varchar(max) = 'D:\SQLServer\data\'; SELECT name , 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command , 'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' AS offline_command , 'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @local_backup_path + name + '.bak'' WITH ' + ( SELECT 'MOVE ''' + mf.name + ''' TO ''' + @new_data_path + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name))-1)) + ''', ' FROM sys.master_files mf WHERE mf.database_id = d.database_id FOR XML PATH('') ) + 'REPLACE, RECOVERY, STATS = 5;' AS restore_command FROM sys.databases d WHERE database_id > 4 AND state_desc = N'ONLINE'; На выходе получаем готовые команды для создания бэкапов в нужное место, перевода БД в offline, чтобы их пользователи не могли с ними работать на старом сервере и скрипты для восстановления полученных бэкапов на новом сервере (с автоматическим перемещением всех файлов данных и журналов транзакций в указанное место).
Проблема с этим такая — либо кто-то должен сидеть и по очереди выполнять все скрипты (бэкап-офлайн-восстановление), либо кто-то должен сначала запустить все бэкапы, потом отключить все базы, потом всё восстановить — действий меньше, но нужно сидеть и отслеживать.
Хотелось автоматизировать все эти операции. С одной стороны, всё просто — уже есть готовые команды, заворачивай в курсор и выполняй. И, в принципе, я так и сделал, добавил новый сервер как linked server на старом и запустил. На локальном сервере команда выполнялась через EXECUTE (@sql_text);, на linked server — EXECUTE (@sql_text) AT [linkedServerName].
Таким образом, последовательно выполнялись операции — бэкап локально, перевод локальной БД в офлайн, восстановление на Linked server. Всё завелось, ура, но мне показалось, что можно немного ускорить процесс, если бэкапы и восстановления выполнять независимо друг от друга.
Тогда придуманный курсор был разделён на две части — на старом сервере в курсоре каждая база бэкапится и переводится в офлайн, после чего второй сервер-таки должен понять, что появилось новое задание и выполнить восстановление БД. Для реализации этого механизма я использовал запись в таблицу на linked server и бесконечный цикл (мне было лень придумывать критерий остановки), который смотрит не появилось ли новых записей и пытается восстановить что-нибудь, если появились.
Решение
На старом сервере создаётся и заполняется глобальная временная таблица ##CommandList, в которой собираются все команды и там же можно будет отслеживать статус выполнения бэкапов. Таблица глобальная, чтобы в любой момент из другой сессии можно было посмотреть — что там сейчас происходит.
DECLARE @unc_backup_path AS varchar(max) = 'D:\SQLServer\backup\' --путь к шаре для бэкапа на новом сервере , @local_backup_path AS varchar(max) = 'D:\SQLServer\backup\' --локальный путь на новом сервере к папке с бэкапами , @new_data_path as varchar(max) = 'D:\SQLServer\data\'; --локальный путь на новом сервере к папке, где должны оказаться данные SET NOCOUNT ON; IF OBJECT_ID ('tempdb..##CommandList', 'U') IS NULL CREATE TABLE ##CommandList ( dbName sysname unique --имя БД , backup_command varchar(max) --сгенерированная команда для бэкапа , offline_command varchar(max) --сгенерированная команда для перевода БД в офлайн после бэкапа , restore_command varchar(max) --сгенерированная команда для восстановления БД на новом сервере , processed bit --признак обработки: NULL - не обработано, 0 - обработано успешно, 1 - ошибка , start_dt datetime --когда начали обработку , finish_dt datetime --когда закончили обработку , error_msg varchar(max) --сообщение об ошибке, при наличии ); INSERT INTO ##CommandList (dbname, backup_command, offline_command, restore_command) SELECT name , 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command --включает INIT - бэкап в месте назначения будет перезаписываться , 'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' AS offline_command , 'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @local_backup_path + name + '.bak'' WITH ' + ( SELECT 'MOVE ''' + mf.name + ''' TO ''' + @new_data_path + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name))-1)) + ''', ' FROM sys.master_files mf WHERE mf.database_id = d.database_id FOR XML PATH('') ) + 'REPLACE, RECOVERY, STATS = 5;' AS restore_command FROM sys.databases d WHERE database_id > 4 AND state_desc = N'ONLINE' AND name NOT IN (SELECT dbname FROM ##CommandList) AND name <> 'Maintenance'; --у меня linked server - это тот же экземпляр, поэтому исключаю БД, которая используется на "linked server" Посмотрим что там оказалось (SELECT * FROM ##CommandList):
Отлично, там собираются все команды для бэкапа/восстановления всех нужных БД.
На новом сервере была создана БД Maintenance и в ней таблица CommandList, которая будет содержать в себе информацию о восстановлении баз:
USE [Maintenance] GO CREATE TABLE CommandList ( dbName sysname unique --имя БД , restore_command varchar(max) --команда для восстановления , processed bit --статус выполнения , creation_dt datetime DEFAULT GETDATE() --время добавления записи , start_dt datetime --время начала обработки , finish_dt datetime --время окончания обработки , error_msg varchar(max) --текст ошибки, при наличии );На старом сервере был настроен linked server, смотрящий на новый экземпляр SQL Server. Скрипты, которые приведены в этом посте, я писал дома и не заморачивался с новым экземпляром, использовал один и его же подключил как linked server сам к себе. Поэтому тут у меня и пути одинаковые и unc-path локальный.
Теперь можно объявлять курсор, в котором бэкапить базы, отключать их и писать на linked server команду для восстановления:
DECLARE @dbname AS sysname , @backup_cmd AS varchar(max) , @restore_cmd AS varchar(max) , @offline_cmd AS varchar(max); DECLARE MoveDatabase CURSOR FOR SELECT dbName, backup_command, offline_command, restore_command FROM ##CommandList WHERE processed IS NULL; OPEN MoveDatabase; FETCH NEXT FROM MoveDatabase INTO @dbname, @backup_cmd, @offline_cmd, @restore_cmd; WHILE @@FETCH_STATUS = 0 BEGIN --имя БД и команды получены, теперь нужно: -- сделать бэкап -- добавить в таблицу-приёмник на новом экземпляре команду для восстановления -- перевести БД в офлайн, чтобы к ней не могли подключиться -- получить следующую БД из списка --делаем отметку о начале работ UPDATE ##CommandList SET start_dt = GETDATE() WHERE dbName = @dbname; BEGIN TRY RAISERROR ('Делаем бэкап %s', 0, 1, @dbname) WITH NOWAIT; --сообщения на вкладке messages будут появляться сразу -- делаем бэкап EXEC (@backup_cmd); RAISERROR ('Добавляем команду на восстановления %s', 0, 1, @dbname) WITH NOWAIT; -- добавляем запись в таблицу-приёмник на linked server INSERT INTO [(LOCAL)].[Maintenance].[dbo].[CommandList] (dbName, restore_command) VALUES (@dbname, @restore_cmd); RAISERROR ('Переводим %s в OFFLINE', 0, 1, @dbname) WITH NOWAIT; -- переводим БД в офлайн EXEC (@offline_cmd); --Ставим успешный статус, проставляем время окончания работы UPDATE ##CommandList SET processed = 0 , finish_dt = GETDATE() WHERE dbName = @dbname; END TRY BEGIN CATCH RAISERROR ('ОШИБКА при работе с %s. Необходимо проверить error_msg в ##CommandList', 0, 1, @dbname) WITH NOWAIT; -- если что-то пошло не так, ставим ошибочный статус и описание ошибки UPDATE ##CommandList SET processed = 1 , finish_dt = GETDATE() , error_msg = ERROR_MESSAGE(); END CATCH FETCH NEXT FROM MoveDatabase INTO @dbname, @backup_cmd, @offline_cmd, @restore_cmd; END CLOSE MoveDatabase; DEALLOCATE MoveDatabase; --выводим результат SELECT dbName , CASE processed WHEN 1 THEN 'Ошибка' WHEN 0 THEN 'Успешно' ELSE 'Не обработано' END as Status , start_dt , finish_dt , error_msg FROM ##CommandList ORDER BY start_dt; DROP TABLE ##CommandList; Каждое действие «логируется» на вкладке Messages в SSMS — там можно наблюдать за текущим действием. Если использовать WITH LOG в RAISERROR, в принципе, можно засунуть это всё в какой-нибудь job и потом смотреть логи.
Во время выполнения курсора можно обращаться к ##CommandList и смотреть в табличном виде что и как происходит.
На новом сервере, параллельно, крутился бесконечный цикл:
SET NOCOUNT ON; DECLARE @dbname AS sysname , @restore_cmd AS varchar(max); WHILE 1 = 1 --можно придумать условие остановки, но мне было лень BEGIN SELECT TOP 1 @dbname = dbName, @restore_cmd = restore_command FROM CommandList WHERE processed IS NULL; --берём случайную БД из таблицы, среди необработанных IF @dbname IS NOT NULL BEGIN --добавляем сообщение о начале обработки UPDATE CommandList SET start_dt = GETDATE() WHERE dbName = @dbname; RAISERROR('Начали восстановление %s', 0, 1, @dbname) WITH NOWAIT; BEGIN TRY --пробуем восстановить БД, если что-то не так, в CATCH запишем что не так EXEC (@restore_cmd); --добавляем информацию в журнал UPDATE CommandList SET processed = 0 , finish_dt = GETDATE() WHERE dbName = @dbname; RAISERROR('База %s восстановлена успешно', 0, 1, @dbname) WITH NOWAIT; END TRY BEGIN CATCH RAISERROR('Возникла проблема с восстановлением %s', 0, 1, @dbname) WITH NOWAIT; UPDATE CommandList SET processed = 1 , finish_dt = GETDATE() , error_msg = ERROR_MESSAGE(); END CATCH END ELSE --если ничего не выбрали, то просто ждём BEGIN RAISERROR('waiting', 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:30'; END SET @dbname = NULL; SET @restore_cmd = NULL; END Всё что он делает — смотрит в таблицу CommandList, если там есть хотя бы одна необработанная запись — берёт имя БД и команду для восстановления и пытается выполнить с помощью EXEC (@sql_text);. Если записей нет, ждёт 30 секунд и пробует снова.
И курсор, и цикл обрабатывают каждую запись только один раз. Не получилось? Пишем сообщение об ошибке в таблицу и больше сюда не возвращаемся.
Про условие остановки — мне на самом деле было лень. Пока набирал текст, придумал минимум три решения — как вариант — добавление флагов «Готов к восстановлению \ Не готов к восстановлению \ Завершён», заполнение списка БД и команд сразу, при заполнении ##CommandList на старом сервере и обновление флага внутри курсора. Останавливаемся, когда не осталось «готовых к восстановлению» записей, так как нам сразу известен весь объём работ.
Выводы
А нет никаких выводов. Подумал, что кому-то может быть полезно/интересно посмотреть как использовать метаданные для формирования и выполнения dynamic sql. Приведённые в посте скрипты в том виде, как есть, мало пригодны для использования на проде, однако, их можно немного допилить под себя и использовать, например, для массовой настройки log shipping / database mirroring / availability groups.
При выполнении бэкапа на шару, у учётной записи, под которой запущен SQL Server, должны быть права для записи туда.
В посте не раскрыто создание Linked Server’a (мышкой в GUI интуитивно настраивается за пару минут) и перенос логинов на новый сервер. Те, кто сталкивался с переносом пользователей знают, что простое пересоздание sql-логинов не очень помогает, поскольку у них есть sid’ы, с которыми и связаны пользователи БД. Скрипты для генерации sql-логинов с текущими паролями и корректными sid’ами есть на msdn.
ссылка на оригинал статьи https://habr.com/ru/post/488478/
Добавить комментарий