Копирование баз данных на другие серверы
В некоторых случаях можно скопировать базу данных с одного компьютера на другой и использовать ее для тестирования, проверки согласованности данных, разработки ПО, выполнения отчетов, создания зеркальной базы данных или предоставления доступа к базе данных сотрудникам удаленного филиала.
Скопировать базу данных можно одним из следующих способов.
- Использование мастера копирования баз данных Мастер копирования баз данных можно использовать для копирования или перемещения баз данных между серверами или обновления базы данных SQL Server до более поздней версии. Дополнительные сведения см. в статье Use the Copy Database Wizard.
- Восстановление базы данных из резервной копии Для копирования всей базы данных можно использовать инструкции BACKUP и RESTORE Transact-SQL. Выбор методики восстановления базы данных из полной резервной копии для копирования базы данных с одного компьютера на другой может быть мотивирован разными причинами. Сведения о копировании базы данных путем восстановления из резервной копии см. в статье Копирование баз данных путем создания и восстановления резервных копий.
Заметка Чтобы настроить зеркальную базу данных для зеркального отображения базы данных, необходимо восстановить базу данных на зеркальном сервере с помощью restore DATABASE WITH NORECOVERY. Дополнительные сведения см. в статье Подготовка зеркальной базы данных к зеркальному отображению (SQL Server).
Перенос базы данных на другой SQL-сервер
Иногда возникает необходимость перенести SQL базу данных с одного SQL-сервера на другой. Обычно процесс переноса данных заключается в создании резервной копии базы данных и восстановлении ее на другом SQL-сервере. Однако, казалось бы, в такой простой операции могут возникать разного рода трудности. В данной статье мы попробуем разобраться с некоторыми из них.
1. Если база данных с таким именем уже существует
Если при переносе базы данных обнаружится, что база данных с таким именем уже существует, или при восстановлении возникает ошибка, что файл базы данных с таким именем уже существует, требуется вручную указать новое имя базы данных и/или папку, в которой будут размещаться физические файлы БД. Это можно сделать в SQL Server Managment Studio, указав при восстановлении новое имя базы данных на закладке Общие
и папку, в которой должны будут размещаться файлы БД (закладка Файлы)
2. Перенос базы данных Альта-ГТД вместе с дополнительными базами ЭД
Если требуется перенести базу данных Альта-ГТД вместе с дополнительными базами ЭД, то необходимо:
1. Создать средствами программы Альта-ГТД резервную копию базы данных вместе с дополнительными базами. Для этого необходимо выполнить Сервис — Администратор SQL — Бэкап SQL базы данных, после чего утвердительно ответить на вопрос о необходимости резервного копирования дополнительных ЭД-баз. После того, как операция по созданию резервных копий дополнительных баз будет завершена, программа выдаст информационное окно с описанием всех созданных бэкап-файлов. Эти файлы, а также бэкап-файл рабочей базы данных необходимо перенести на другой SQL-сервер.
2. Восстановить базы данных из резервных копий. Если на сервере уже имеется БД с таким названием, то ее необходимо восстановить с другим названием (см. раздел 1).
3. Если при восстановлении была переименована рабочая база данных, тогда необходимо для всех дополнительных баз выполнить скрипт:
UPDATE [Имя_Доп_Базы].[dbo].[Version] SET [Program] = '_gtd_ed@Имя_Основной_Базы'
4. Если при восстановлении была переименована одна или несколько дополнительных баз, то для каждой из них необходимо выполнить скрипт
UPDATE [Имя_Основной_Базы].[dbo].[EdMsgs] SET [MsgDB] = 'Новое_Имя_Доп_Базы' WHERE [MsgDB] = 'Старое_Имя_Доп_Базы'
Как перенести базу данных sql server на другой компьютер
Приведенный ниже порядок переноса данных применяется при необходимости передать аудиторской группе (например, руководителю аудиторской группы) с сервера аудиторской компании базу данных аудируемого лица при работе на его территории.
Обратите внимание
Перед переносом баз данных убедитесь, что на компьютере-источнике (компьютер, с которого переносятся файлы базы данных) и компьютере-приемнике (компьютер, куда переносятся файлы базы данных) установлена программа IT Audit одной версии
На компьютере-источнике (сервер) необходимо сделать архив:
Базы данных Справочники (IT Audit — Справочники)
При приобретение нескольких ключей защиты и использовании нескольких баз данных, клиентские базы данных необходимо создавать только на одном компьютере (например, компьютер руководителя компании или сервер). Затем архив общей и клиентской базы передается аудиторской группе для ее наполнения в ходе аудита. По завершению аудита аудиторская группа создает архив и возвращает клиентскую базу данных на сервер
Если каждый из аудиторов в своей базе данных будет создавать клиентские базы данных, то в последующем не получится объединить все клиентские базы данных на одном компьютере
Перенос всех баз данных 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.
- Microsoft SQL Server
- Администрирование баз данных