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

Предистория

В един момент за възпроизвеждане на бъг, ми беше необходим бекап на продукционната база.

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

Бекапът на базата беше направен на версия 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 или свързване с отдалечен сървър, ако архитектурата на приложението позволява това. Въпреки това, никой не е застрахован от легаси код и некоректна разработка. Надявам се, че тази инструкция няма да ви е нужна и ако все пак възникне необходимост от нея, ще помогне да спестите много време и нерви. Благодаря за вниманието!