Хабрахабр

Перенос базы данных в более старую версию MS SQL Server

Вы уже сделали бэкап и радостно приступаете к восстановлению. У вас есть база данных MS SQL Server, которую нужно перенести на другой физический комп. Stack Overflow уверяет вас, что всё плохо. Но тут обнаруживается, что на том компе, куда нужно перенести базу, установлена более старая версия MS SQL Server. Но так ли это на самом деле?

Но зачастую базы данных создаются такими, что они поддерживают все более новые версии SQL, начиная с какой-то, например с 2008 R2, т.к. Конечно, перенос базы из более новой версии в старую — это не классический и не самый правильный сценарий работы. И, например, ваш клиент уже поставил себе MS SQL 2016, а у вас на тестовом сервере для разработки стоит MS SQL 2014. прямая совместимость у MS SQL более чем отличная. А вы хотите развернуть себе базу клиента, чтобы разобраться, где у него путаница с данными.

Резервную копию, созданную в более новом сервере, невозможно восстановить на более старом сервере. Microsoft открестились от проблемы — мол нет у них обратной совместимости, и баста. Но они настолько неудобны и громоздки, что нормальный перенос большой БД с множеством таблиц сделать с их помощью не особо удобно. Да, у них есть инструменты типа DTS, копирование базы, экспорт-импорт и т.п. Во всяком случае лично у меня не получилось.

Но представьте, у вас в базе куча блоб-полей с большими данными, и вообще размер всей базы 500+ ГБ. Да, можно сгенерировать SQL-скрипты всей базы, включая данные. Представляете, сколько будет занимать такой скрипт, сколько времени он будет генерироваться и исполняться.

Я придумал довольно простое решение, которым хочу поделиться. Итак, задача — в точности воссоздать базу данных (структуру и данные) из новой версии MS SQL сервера в более старой версии. Конечно, данное решение обладает значительным количеством ограничений, но всё же на мой взгляд это лучше, чем DTS и скрипты.

Если это не возможно, то должна быть возможность на той машине, откуда нужно перенести базу, установить ту версию SQL, в которую нужно перенести базу, чтобы перенести базу сначала в эту версию локально, а потом уже перетащить её через бэкап или непосредственно через *df файлы базы данных (через Detach/Attach) на новую машину (версия SQL Server'а в этом случае уже будет совпадать). Ограничение номер один заключается в том, что вам нужен доступ через MS SQL Management Studio к обоим серверам — старому и новому.

Еще одним ограничением является то, что вам будет необходим скрипт схемы базы данных (всех объектов, включая таблицы, индексы, констрейнты, хранимые процедуры, триггеры и т.п.) без данных, причем инструкции создания Foreign Key Constraints должны в этом скрипте идти в самом конце, отдельно от скрипта создания самих таблиц.

Все действия выполняются в сеансе Management Studio, подключенной к серверу, на который нужно перенести базу. Опишу кратко сам алгоритм переноса данных.

1) На новом сервере создаем пустую базу данных с такими же файлами и файловыми группами, как переносимая база.

Создавать FK на этом этапе нельзя, т.к. 2) Скриптом схемы базы данных создаем все объекты базы (таблицы, индексы, представления, триггеры, хранимые процедуры и функции), но без создания Foreign Key Constraints. они будут мешать вставке данных.

3) Подключаем базу данных, из которой будем переносить данные, в качестве Linked Server'а, чтобы можно было использовать в запросах к новой базе данных обращения к старой базе данных.

EXEC sp_addlinkedserver @server=N'LinkedServerAlias', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'LinkedServerHost\LinkedServerName'; EXEC sp_addlinkedsrvlogin 'LinkedServerUser', 'false', null, 'RealUser', 'RealUserPassword';

4) Т.к. структуры баз совпадают, воспользуемся встроенной хранимой процедурой sp_msforeachtable, которая позволяет выполнить запрос над каждой таблицей БД, чтобы сгенерировать скрипт переноса данных из старой базы в новую через запрос вида

INSERT INTO ? SELECT * FROM ?

Вместо знака вопроса sp_msforeachtable подставляет имя каждой таблицы и выполняет запрос несколько раз (по одному разу на каждую таблицу).

Здесь я натолкнулся на самое большое количество граблей.

а) Проблема номер один заключается в том, что для таблиц с IDENTITY-полями необходимо вызывать:

SET IDENTITY_INSERT ON; --INSERT INTO ... (сама вставка);
SET IDENTITY_INSERT OFF;

б) Проблема номер два заключается в том, что на таблицах, в которых нет IDENTITY-полей, делать данный вызов нельзя, поэтому требуется динамически определять, есть в таблице IDENITY-колонка или нет.

Это можно сделать таким запросом:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME='SomeTable') AND (COLUMNPROPERTY(object_id('dbo.SomeTable'), COLUMN_NAME, 'IsIdentity') = 1)

в) Проблема номер три заключается в том, что, как оказалось, в режиме IDENITY_INSERT ON нельзя делать

INSERT INTO ... SELECT * FROM ...

, а нужно перечислять конкретные поля.

Перечислить поля таблицы в строку можно таким запросом:

SELECT SUBSTRING( (SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable' ORDER BY ORDINAL_POSITION FOR XML path('')), 3, 200000);

4) Генерируем скрипт вставки по все таблицы:

Процедура генерации скрипта

EXEC sp_msforeachtable N'
DECLARE @command varchar(MAX);
DECLARE @name varchar(200);
SET @name=''?'';
SET @name = SUBSTRING(@name, 8, LEN(@name)-8); SET @command = '''';
SELECT @command= SUBSTRING( (SELECT '', '' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''' + @name + '''' ORDER BY ORDINAL_POSITION FOR XML path('''')), 3, 200000); SET @command = ''INSERT INTO ''+ @name +'' (''+ @command + '') SELECT '' + @command + '' FROM '' + ''LinkedServerAlias.SourceDatabase.'' + ''?''; SET @command= ''IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME='''''' + @Name + '''''') AND (COLUMNPROPERTY(object_id(''''dbo.''+@Name+''''''), COLUMN_NAME, ''''IsIdentity'''') = 1)) SET IDENTITY_INSERT '' + @name + '' ON; '' +@command;
SET @command=@command+'';'' + ''IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME='''''' + @Name + '''''') AND (COLUMNPROPERTY(object_id(''''dbo.''+@Name+''''''), COLUMN_NAME, ''''IsIdentity'''') = 1)) SET IDENTITY_INSERT '' + @name + '' OFF;''; PRINT (@command);
--EXEC(@command); // Если раскомментировать, скрипт будет сразу исполняться, а не только выводиться на экран '

5) Исполняем сгенерированный скрипт переноса данных

6) Исполняем скрипт на создание всех Foreign Key Constraints (теперь уже можно).

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

8) Прибираемся за собой (отключаем Linked Server):

EXEC sp_droplinkedsrvlogin 'LinkedServerUser', null;
sp_dropserver 'LinkedServerAlias';

Ограничения метода.

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

Надеюсь, кому-то поможет. Спасибо за внимание!

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть