🥇 Пренос на данни от резервно копие на нова версия на MS SQL Server към по-стара версия

Предистория

Един ден, за да възпроизведа бъг, ми трябваше архив на production-базата.

Към моето учудване се сблъсках с следните ограничения:

Архивът на базата беше направен на версия SQL Server 2016 и не беше съвместим с моята SQL Server 2014.
На моя работен компютър като операционна система беше използвано Windows 7, затова не можех да актуализирам SQL Server до версия 2016
Поддържаният продукт беше част от по-голяма система с силно свързана легаси-архитектура и също така взаимодействаше с други продукти и бази, затова разгръщането му на друга станция можеше да отнеме много време.

Предвид гореизложеното, стигнах до извода, че е настъпило време за импровизирани решения.

Възстановяване на данни от архива

Реших да използвам виртуална машина Oracle VM VirtualBox с Windows 10 (може да се вземе тестово изображение за браузъра Edge оттук). На виртуалната машина беше инсталиран SQL Server 2016 и на него от архива беше възстановена базата данни на приложението (инструкция).

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

Следваше да предприема някои стъпки, за да може достъпът до SQL Server да е възможен отвън:

За защитната стена добавете правило да пропуска запитвания към порта 1433.
Препоръчително е достъпът до сървъра да не става чрез Windows аутентикация, а чрез SQL с име и парола (по-лесно е да се настрои достъп). Въпреки това в този случай не трябва да забравяте да включите в свойствата на SQL Server възможността за SQL аутентикация.
В настройките на потребителя на SQL Server на таба User Mapping да зададете за възстановената база роля на потребителя db_securityadmin.

Пренос на данни

Собствено преносът на данни се състои от два етапа:

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

Пренос на схемата на данни

Изпълняваме следните операции:

Избираме Tasks -> Generate Scripts за преносимата база.
Избирайте нужните за пренос обекти или оставете стойността по подразбиране (в този случай ще бъдат създадени скриптове за всички обекти на базата).
Указваме настройки за запазване на скрипта. Най-удобно е да се запази скриптът в един файл в кодировка Unicode. Тогава при проблеми няма да е необходимо да повтаряте всички стъпки.

След запазване на скрипта, той може да бъде изпълнен на изходния SQL Server (стара версия), за да се създаде необходимата база.

Внимание: След изпълнението на скрипта е необходимо да се провери съответствието на настройките на базата данни от резервното копие и базата данни, създадена от скрипта. В моя случай в скрипта липсваше настройка за COLLATE, което водеше до сбой при прехвърляне на данни и трудности при пресъздаването на базата с помощта на допълнения скрипт.

Пренос на данни

Преди прехвърляне на данни е необходимо да се деактивира проверката на всички ограничения в базата:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

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

Указваме настройки за връзка с източника (SQL Server 2016 на виртуална машина). Аз използвах Data Source SQL Server Native Client и гореспоменатата SQL-аутентификация.
Указваме настройки за връзка с мястото за предназначение (SQL Server 2014 на хост-машината).
След това настройваме мапинга. Необходимо е да се изберат всички не read-only обекти (например, представления не трябва да се избират). Като допълнителни опции следва да се избере „Разреши вмъкване в identity-колони“, ако такива се използват.
Внимание: ако при опит да се изберат няколко таблици и да им се зададе свойство „Разреши вмъкване в identity-колони“ свойството вече е било установено поне за една от избраните таблици, в диалога ще се отбележи, че свойството вече е установено за всички избрани таблици. Този факт може да обърка и да доведе до грешки при прехвърлянето.
Стартираме прехвърлянето.
Възстановяваме проверката на ограниченията:
```
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'
```

Ако възникнат някакви грешки, проверяваме настройките, изтриваме създадената с грешки база, отново я създаваме от скрипта, нанасяме корекции и повтаряме прехвърлянето на данни.

Заключение

Тази задача среща сравнително рядко и възниква само заради горепосочените ограничения. Най-често решението е в обновлението на SQL Server или свързването с отдалечен сървър, ако архитектурата на приложението позволява. Въпреки това, никой не е застрахован от остарял код и некачествена разработка. Надявам се, че тази инструкция няма да ви е необходима, а ако все пак възникне нужда от нея, ще помогне за икономия на много време и нерви. Благодаря за вниманието!