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

Предыстория

Как-то раз для воспроизведения бага мне потребовался бэкап production-базы.

К моему удивлению я столкнулся со следующими ограничениями:

1. Бэкап базы был сделан на версии SQL Server 2016 и не был совместим с моей SQL Server 2014.
2. На моем рабочем компьютере в качестве ОС использовалась Windows 7, поэтому я не мог обновить SQL Server до версии 2016
3. Поддерживаемый продукт был частью более крупной системы с сильно связанной легаси-архитектурой и также обращался к другим продуктам и базам, поэтому его развертывание на другой станции могло занять очень много времени.

Учитывая вышеизложенное, я пришел к выводу, что настало время костылей нестандартных решений.

Восстановление данных из бэкапа

Я решил использовать виртуальную машину Oracle VM VirtualBox с Windows 10 (можно взять тестовый образ для браузера Edge отсюда). На виртуальную машину был установлен SQL Server 2016 и на нем из бэкапа была восстановлена база данных приложения (инструкция).

Настройка доступа к SQL Server на виртуальной машине

Далее было необходимо предпринять некоторые шаги, чтобы появилась возможность доступа к SQL Server извне:

1. Для фаервола добавить правило пропускать запросы на порт 1433.
2. Желательно, чтобы доступ к серверу шел не через windows-аутентификация, а через SQL по логину и паролю (проще настроить доступ). Однако в этом случае нужно не забыть включить в свойствах SQL Server возможность SQL-аутентификации.
3. В настройках пользователя на SQL Server на вкладке User Mapping указать для восстановленной базы роль пользователя db_securityadmin.

Перенос данных

Собственно сам перенос данных состоит из двух этапов:

1. Перенос схемы данных (таблицы, представления, хранимые процедуры и т.д.)
2. Перенос самих данных

Перенос схемы данных

Выполняем следующие операции:

1. Выбираем Tasks -> Generate Scripts для переносимой базы.
2. Выбираем нужные для переноса объекта или оставляем значение по умолчанию (в этом случае будут созданы скрипты для всех объектов базы).
3. Указываем настройки для сохранения скрипта. Удобнее всего сохранить скрипт в единый файл в кодировке Unicode. Тогда при сбое не понадобится заново повторять все шаги.

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

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

Перенос данных

Перед переносом данных необходимо отключить проверку всех ограничений на базе:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Перенос данных осуществляем с помощью мастера импорта данных Tasks -> Import Data на SQL Server, где находится созданная скриптом база:

1. Указываем настройки подключения к источнику (SQL Server 2016 на виртуальной машине). Я использовал Data Source SQL Server Native Client и вышеупомянутую SQL-аутентификацию.
2. Указываем настройки подключения к месту назначения (SQL Server 2014 на хост-машине).
3. Далее настраиваем маппинг. Необходимо выбрать все не read-only объекты (например, представления выбирать не нужно). В качестве дополнительных опций следует выбрать «Разрешить вставку в identity-столбцы», если такие используются.
   Внимание: если при попытке выделить несколько таблиц и проставить им свойство «Разрешить вставку в identity-столбцы» свойство уже было ранее установлено хотя бы для одной из выделенных таблиц, в диалоге будет отмечено, что свойство уже установлено для всех выделенных таблиц. Данный факт может сбить с толку и привести к ошибкам переноса.
4. Запускаем перенос.
5. Восстанавливаем проверку ограничений:

   EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Если возникли какие-либо ошибки, проверяем настройки, удаляем созданную с ошибками базу, заново создаем ее из скрипта, вносим исправления и повторяем перенос данных.

Заключение

Данная задача встречается довольно редко и возникает только из-за вышеуказанных ограничений. Чаще всего решение заключается в обновлении SQL Server или подключению к удаленному серверу, если это позволяет архитектура приложения. Однако от легаси-кода и кривых рук некачественной разработки никто не застрахован. Надеюсь, что Вам эта инструкция не понадобится, а если все же в ней возникнет необходимость, то поможет сэкономить кучу времени и нервов. Спасибо за внимание!

Список использованных источников

• How do I deal with FK constraints when importing data using DTS Import/Export Wizard?
• The column «Column 2» cannot be processed because more than one code page (65001 and 1252) are specified for it.
• How can I connect to SQLServer running on VirtualBox from my host Macbook.
• SQL SERVER – Enable Identity Insert – Import Expert Wizard
• Troubleshooting Microsoft SQL Server Error 18456, Login failed for user

Источник: habr.com