Перенос всех баз данных MS SQL Server на другую машину

    Недавно возникла необходимость переноса всех БД (>50 на одном экземпляре SQL Server) из dev-окружения на другой экземпляр SQL Server, который располагался на другом железе. Хотелось минимизировать ручной труд и сделать всё как можно быстрее.

    Disclaimer


    Скрипты написаны для одной конкретной ситуации: это dev-окружение, все базы в простой модели восстановления, файлы данных и журналы транзакций лежат в одной куче.

    Всё, что написано дальше относится только к этой ситуации, но вы можете без особых усилий допилить их под себя (свои условия).

    В скриптах не используются новомодные STRING_AGG и прочие приятные штуки, поэтому работать всё должно начиная с SQL Server 2008 (или 2008 R2, не помню где появилось сжатие бэкапов). Для более старых версий нужно убрать WITH COMPRESSION из команды бэкапа, но тогда разницы по времени с копированием файлов может уже и не быть.

    Это не инструкция — «как надо» делать такой перенос. Это демонстрация того, как можно использовать метаданные в dynamic SQL.

    Конечно, самым быстрым способом было бы просто переподключить полку с дисками к новому серверу, но это был не наш вариант. 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.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 27

      +3
      dbatools.io?
        –1
        Я не очень люблю powershell. А велосипеды люблю :). Но вообще, инструмент, конечно, крутой
        +2
        При выполнении бэкапа на шару, у учётной записи, под которой запущен SQL Server, должны быть права для записи туда.

        можно вот так сначала сделать
        net use L:
        -- enable xp_cmdshell
        EXEC sp_configure 'show advanced options', 1
        RECONFIGURE
        GO
        EXEC sp_configure 'xp_cmdshell', 1
        RECONFIGURE
        GO
        
        EXEC xp_cmdshell 'net use l: \\10.1.1.1\backups /user:domain\username PASSWORD'
        --EXEC xp_cmdshell 'net use l: \\storage\backup'
        
        GO
        
        --EXEC xp_cmdshell 'net use l: /delete'
        
        -- disable xp_cmdshell
        EXEC sp_configure 'xp_cmdshell', 0
        RECONFIGURE
        GO


        и соответственно все бэкапы делать на диск L
          –1

          Есть такая поговорка: Code is a liability not an asset. Вы написали простыню business-cricial кода, в котором пустое множество тестов. Если вы такое оставите после себя работодателю, ваш вклад в компанию будет отрицательным.


          Откуда вы знаете, что эта штука работает?

            +3
            wut? Я написал пару скриптов для развового выполнения. Это не оставляют работадателю — это выполняют и выбрасывают, область применения очень узкая
              0

              Вы написали статью для общественности, и там простыня кода, которую вы написали и выкинули. Где-то тут что-то не сходится.

                +1
                Не читатель?
                Выводы
                А нет никаких выводов. Подумал, что кому-то может быть полезно/интересно посмотреть как использовать метаданные для формирования и выполнения dynamic sql.
              +3

              Человек чем-то работающим поделился, а вы ещё и недовольны :-). Ну юзайте платное ПО, может там меньше ошибок или лучше тесты, но вы это никогда не узнаете, т. к. оно чёрный ящик. А тут простой скрипт, всё на виду. Хочется сделать ещё более безопасно — допишите и поделитесь результатом. Хаять чужие решения больно все горазды.

              +1

              При узком канале я бы пожалуй rar-ом воспользовался, а не встроенной компрессией. Ну и скрипт в шелле, запускающий бекапы через sqlcmd. Наверное :)

                0
                Тогда уж сразу offline > 7zip > copy > unpack > attach
                Detach — копирование — Attach рассматривался, но не подошёл, поскольку канал был довольно узким и перенос БД в несжатом виде занял бы довольно большой промежуток времени.

                Кстати, действительно, что мешало сделать промежуточное сжатие выключенной БД перед копированием.
                Тут месяц назад как раз была тема выбора оптимальной стратегии сжатия.
                habr.com/en/company/homecredit/blog/484414
                  0
                  Ничего не мешало, но нужен дополнительный инструмент и вызов его через xpcmdshell, либо какая-то обёртка — cmd/powershell. А тут всё стандартными средствами.
                  Этот вариант удобен тем, что время простоя минимально — пока бэкап льётся по узкому каналу, бд доступна на старом экземпляре. При желании, можно дополнительно заморочиться на бэкапы журналов транзакций и сделать простой ещё меньше.
                    0
                    Ну понятно, что можно и так и этак.
                    >А тут всё стандартными средствами
                    Проверил на первой попавшейся базе, sql жмет чуть лучше, и значительно быстрее, чем даже 7zip. И это с файлами базы, которые уже обрезаны.
                    Другое дело, когда есть счастливчики с 2005 или Express-версиями.
                    >бд доступна на старом экземпляре.
                    Но ведь, тогда бэкап перестанет быть актуальным, если пользователи что-то запишут в базу, и эти данные пропадут. Опять-таки в вашем случае, может это и не было критично.
                      0

                      Бэкап в SQL server актуален на момент завершения операции backup database.

                  +1
                  Разница в сжатии раром на максимальной компрессии и встроенного сжатия бекапов — менее 10%, а обычно 2-3%.
                  При том, что сжатый штатными средствами бекап — мгновенно доступен для восстановления, и не требует дополнительного места для декомпрессии.
                  0
                  Зачем делится своим кривым опытом? SQL server все умеет из коробки, а если вы не курсе, то читайте и читайте до просветления.
                    0

                    Великий Гуру, молю, укажите направление! Очень хочу просветления

                      0

                      ms sql server backup в гугле набрать, если не забанен.

                        0

                        нельзя к sql server относится как к обычной СУРБД, это скорее комплекс, в отличие например от разных посгесов и марий, поверьте, там все идет в коробке, а если Вы об этом не знаете, то это не вина продукта, а только ваше незнание, прошу не обижаться. Если бы Вы хоть теорию знали, вы не пошли бы таким путем, а мне лень писать в 2 раза больше, чем Вы накатали. Все поймете с возрастом и опытом.

                          0

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

                            +3
                            В этом плане гораздо больше нравится подход линукс систем и maridb/mysql. Можно позвать mysqldump, отправить его вывод в gzip, отправить вывод gzip в канал ssh/netcat, на другой стороне принять его и провести обратный процесс. При этом вообще не требуется место под локальное хранение копии базы.
                      0

                      Могу посоветовать интересную (решаемую) задачу. Разверните 3 например экземпляра на одном ипе и одном порту. Вы очень много нового сами для себя узнаете.

                        +1
                        работать всё должно начиная с SQL Server 2008 (или 2008 R2, не помню где появилось сжатие бэкапов)
                        В 2008 сжатие работало только в Enterprise версии, начиная с 2008 R2 оно работает и в версии Standard

                        При выполнении бэкапа на шару, у учётной записи, под которой запущен SQL Server, должны быть права для записи туда.
                        Верно, если только учетная запись доменная. Если она локальная, то на шару нужно добавлять доступ для учетки сервера на которой запущен SQL сервер.

                        За статью, спасибо :)
                          0

                          Когда мне довелось решать такую задачу, мне помог Data Migration Assistant. Утилита скачивается с сайта Microsoft. Все тоже самое: сделать бэкапы и залить на шару, восстановить из бэкапа на целевом сервере. + Переносит логины (с паролями). И при этом ни строчки кода не писал для этого.

                            0
                            тут не про то «как надо», а про то «как можно»
                            0
                            Мы в свое время такие задачи решали через реплики. Создается подписка, вторая база временно в RO и туда льются копии всех транзакций с основной, потом основная стопается, транзакции доливаются, резервная база переводится в RW и на нее переключается приложуха. Минус — сначала делается большой бэкап основной базы, плюс — практически без даунтайма :)
                              0
                              Вы весь процесс переноса в одно окно обслуживания уложили? И сколько приложений эти базы используют?
                                0
                                это dev-стенд. На проде перенос самих баз был бы самой маленькой проблемой

                              Only users with full accounts can post comments. Log in, please.