🥇Przenoszenie danych kopii zapasowej nowej wersji MS SQL Server na starszą wersję

Tło

Pewnego razu, aby powtórzyć błąd, potrzebowałem kopii zapasowej bazy production.

Ku mojemu zdziwieniu napotkałem na następujące ograniczenia:

Kopia zapasowa bazy została zrobiona w wersji SQL Server 2016 i była niekompatybilna z moim SQL Server 2014.
Na moim komputerze roboczym jako system operacyjny używana była Windows 7, dlatego nie mogłem zaktualizować SQL Server do wersji 2016
Obsługiwany produkt był częścią większego systemu z silnie powiązaną architekturą legacy i również odnosił się do innych produktów i baz, dlatego jego wdrożenie na innej stacji mogło zająć bardzo dużo czasu.

Biorąc pod uwagę powyższe, doszedłem do wniosku, że nadszedł czas na nietypowe rozwiązania.

Odzyskiwanie danych z kopii zapasowej

Postanowiłem użyć maszyny wirtualnej Oracle VM VirtualBox z Windows 10 (można użyć obrazu testowego dla przeglądarki Edge stąd). Na maszynie wirtualnej zainstalowano SQL Server 2016 i z kopii zapasowej odzyskano bazę danych aplikacji (instrukcja).

Konfiguracja dostępu do SQL Server na maszynie wirtualnej

Następnie konieczne było podjęcie kilku kroków, aby umożliwić dostęp do SQL Server z zewnątrz:

Dla zapory dodać regułę zezwalającą na zapytania na porcie 1433.
Najlepiej, aby dostęp do serwera odbywał się nie przez uwierzytelnianie Windows, lecz przez SQL za pomocą loginu i hasła (łatwiej skonfigurować dostęp). Jednak w tym przypadku nie należy zapominać o włączeniu w właściwościach SQL Server możliwości uwierzytelniania SQL.
W ustawieniach użytkownika na SQL Server w zakładce User Mapping należy wskazać dla odzyskanej bazy rolę użytkownika db_securityadmin.

Transfer danych

Transfer danych składa się właściwie z dwóch etapów:

Transfer schematu danych (tabele, widoki, procedury składowane itd.)
Transfer samych danych

Transfer schematu danych

Wykonujemy następujące operacje:

Wybieramy Tasks -> Generate Scripts dla przenoszonej bazy.
Wybieramy odpowiednie obiekty do przeniesienia lub pozostawiamy wartość domyślną (w tym przypadku zostaną utworzone skrypty dla wszystkich obiektów bazy).
Określamy ustawienia dotyczące zapisu skryptu. Najwygodniej jest zapisać skrypt w jednym pliku w kodowaniu Unicode. Wtedy podczas awarii nie będzie konieczności ponownego wykonywania wszystkich kroków.

Po zapisaniu skryptu można go wykonać na bazowym SQL Server (starszej wersji), aby utworzyć wymaganą bazę.

Uwaga: Po wykonaniu skryptu należy sprawdzić zgodność ustawień bazy z kopii zapasowej i bazy utworzonej przez skrypt. W moim przypadku skrypt nie zawierał ustawienia COLLATE, co prowadziło do błędów przy przenoszeniu danych i problemów z ponownym tworzeniem bazy za pomocą zmodyfikowanego skryptu.

Transfer danych

Przed przeniesieniem danych należy wyłączyć sprawdzanie wszystkich ograniczeń na bazie:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Przenoszenie danych odbywa się za pomocą kreatora importu danych Tasks -> Import Data na SQL Server, gdzie znajduje się utworzona przez skrypt baza:

Podajemy ustawienia połączenia z źródłem (SQL Server 2016 na maszynie wirtualnej). Użyłem Data Source SQL Server Native Client i powyżej wymienionej autoryzacji SQL.
Podajemy ustawienia połączenia z miejscem docelowym (SQL Server 2014 na maszynie hosta).
Następnie konfigurujemy mapowanie. Należy zaznaczyć wszystkie nie tylko do odczytu obiekty (na przykład, nie trzeba wybierać widoków). Jako dodatkowe opcje należy wybrać „Zezwól na wstawianie do kolumn identity”, jeśli takie są używane.
Uwaga: jeśli podczas próby zaznaczenia kilku tabel i nadania im właściwości „Zezwól na wstawianie do kolumn identity” właściwość była już wcześniej ustawiona przynajmniej dla jednej z zaznaczonych tabel, w oknie dialogowym zostanie zaznaczone, że właściwość ta jest już ustawiona dla wszystkich zaznaczonych tabel. Fakt ten może wprowadzić w błąd i doprowadzić do błędów przenoszenia.
Rozpoczynamy przenoszenie.

Przywracamy sprawdzanie ograniczeń:

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Jeśli wystąpią jakiekolwiek błędy, sprawdzamy ustawienia, usuwamy utworzoną z błędami bazę, ponownie ją tworzymy ze skryptu, wprowadzamy poprawki i powtarzamy przenoszenie danych.

Podsumowanie

Zadanie to występuje dość rzadko i pojawia się tylko z powodu powyższych ograniczeń. Najczęściej rozwiązaniami są aktualizacja SQL Servera lub połączenie z zdalnym serwerem, jeśli to umożliwia architektura aplikacji. Jednak nikt nie jest wolny od legacy code i błędów wynikających z niskiej jakości rozwoju. Mam nadzieję, że ta instrukcja nie będzie Ci potrzebna, a jeśli jednak powstanie taka konieczność, to pomoże zaoszczędzić mnóstwo czasu i nerwów. Dziękuję za uwagę!