Копирование базы данных MS SQL Server

sql.ru

По материалам статьи Rahul Sharma на sqlservercentral.com: " Copying a Database from Server to Server"

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

Метод 1: самый быстрый способ копирования - отсоединение базы данных от исходного сервера и затем прикрепление вместе с журналом на нужный сервер.

Отсоедините базу данных на исходном сервере (Измените, соответственно, имя базы данных):

Use Master GO Exec sp_detach_db 'database_name', 'true' GO

sp_detach_db отсоединяет базу данных от исходного сервера (у неё два параметра: @dbname, который является именем базы данных и @skipchecks, который является указанием для обновления статистики) и указав значение 'true' для второго параметра (@skipchecks) этой хранимой процедуры, что бы удостоверится в том, что если модификация статистики не была выполнена перед отсоединением базы данных от сервера, она обновится после присоединения, что потребует некоторого времени. Скопируйте данные и журналы из каталога Data исходного сервера в каталог данных на новом сервере. Удостоверитесь, что Вы не имеете точно таких же баз данных на сервере адресата.... Если это так, отключите их.
Прикрепите данные и журналы на новый сервер. Выполните на этом сервере:

Use Master GO PRINT 'Attaching Database' EXEC sp_attach_db @dbname = 'database_name', @filename1 = 'c:mssql7datadatabase_name.mdf', -- Это путь к файлу данных @filename2 = 'd:mssql7datadatabase_name_log.ldf' -- Это путь к журналу

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

/* Установите связанный сервер (используя sp_addlinkedserver и sp_addlinkedsrvlogin), назвав его: sourceserver, и из которого стандартные логины входа в систему должны быть перенесены. Вы можете называть его, как Вам удобно и изменить также имя связанного сервера. Чтобы обеспечить доступу к данным связанного сервера, Вы должны использовать sp_serveroption */

declare @login sysname , @password sysname declare sourcelogins cursor for select name , password from sourceserver.master.dbo.syslogins where isntname = 0 and charindex( 'repl_' , name ) = 0 and charindex( 'distributor' , name ) = 0 and name != 'sa' open sourcelogins while ( @@fetch_status = 0) begin fetch sourcelogins into @login , @password exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption' end close sourcelogins deallocate sourcelogins go

При откреплении и прикреплении баз данных, я столкнулся с ещё одной проблемой, кроме потери связи пользователей и их логинов, описанной выше. Я обнаружил, что статистика останется не эффективной, если Вы выполните sp_updatestats на прикрепляемой базе данных. Так, я рекомендовал бы обновить статистику после того, как Вы уже прикрепили эту базу. Или воспользуйтесь альтернативным вариантом: когда Вы открепляете базу данных, удостоверьтесь, что второй параметр установлен в Ложь, что заставит статистику обновиться, и Вам не придётся обновлять её позже на новом сервере. Любой из этих путей прекрасно работает.
Этот метод также великолепно работает, если необходимо переместить базы данных на диск и затем прикрепить их на другой сервер, который не находится в сети. Также, это - очень быстрый путь копирования баз данных с сервера на сервер. Не забудьте прикрепить копируемую базу назад на исходный сервер, как только Вы скопировали данные и журналы с исходного сервера на сервер адресат.

Метод 2: Использование DTS.

DTS очень часто используется для перемещения баз данных с сервера на сервер. Вы можете использовать мастер экспорта/импорта DTS (SQL 7.0 и 2000). Мастер может использоваться для копирования схем, объектов (хранимые процедуры, представления и триггеры и т.д.), данных и также логинов. Или Вы можете использовать DTS Designer и создать задачу перемещения базы данных и задачу перемещения логинов (доступно только для SQL 2К). Также, Вы можете использовать мастер копирования баз данных (Copy Database Wizard), чтобы решить задачу перемещения базы (доступно только для SQL 2К). DTS - довольно мощный инструмент и если Вы его пока не использовали, Вы лишаете себя многих функциональных возможностей и лёгкости, с которой Вы могли бы решать сложные задачи.
В случае если Вы ищете хорошую ссылку на DTS, я предложил бы Вам почитать:
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/dts_overview.htm

Метод 3: Создайте схему и механизм переноса данных, использующий bcp/bulk insert.

Создайте схему на сервере приёмнике данных, если Вы уже имеете скрипт для создания схемы и затем используете bcp или bulk insert, чтобы скачать данные. Оба операции - нерегистрируемые, так что они отработают очень быстро. Главное различие между bcp и bulk insert - это то, что bulk insert не может экспортировать данные, а bcp может.
Вы можете использовать bcp, чтобы экспортировать данные в плоский файл и затем импортировать данные в новую базу из плоского файла, используя bcp или bulk insert. Даже при том, что bulk insert является быстрым способом закачки данных, он имеет большое количество ограничений.

Метод 4: Традиционный путь: Backup и Restore.

Сделайте полную копию базы данных, и затем восстановите её на новом сервере.

Метод 5: Использование распределённых запросов.

Вы сначала должны создать схему на сервере приёмнике данных, используя ваши скрипты создания схем. После этого Вы можете организовать связанный сервер и написать инструкции вставки, которые будут вставлять данные из источника на новый сервер, используя функции openrowset и openquery для запросов к связанным серверам. Вы должны удостовериться, что foreign key и check constraints отключены до того, как Вы начнёте закачивать данные и затем подключить их, когда всё будет сделано. Этот метод самый медленный из всех упомянутых в этой статье. Зато, с помощью него можно переносить данные на SQL Server из гетерогенных источников, например: Oracle, Sybase, DB2 и т.д.