Přenos dat zálohy nové verze MS SQL Server na starší verzi

Pozadí

Jednou jsem potřeboval zálohu produkční databáze k reprodukci chyby.

K mému překvapení jsem narazil na následující omezení:

1. Záloha databáze byla vytvořena ve verzi SQL Server 2016 a nebyla kompatibilní s mým SQL Server 2014.
2. Na mém pracovním počítači byla jako OS použita Windows 7, takže jsem nemohl přejít SQL Server na verzi 2016
3. Podporovaný produkt byl součástí většího systému s silně propojenou legacy architekturou a také se odkazoval na další produkty a databáze, proto by jeho nasazení na jinou stanici mohlo trvat velmi dlouho.

Vzhledem k výše uvedenému jsem dospěl k závěru, že nastal čas na improvizovaná řešení.

Obnovení dat ze zálohy

Rozhodl jsem se použít virtuální stroj Oracle VM VirtualBox s Windows 10 (můžete vzít zkušební obraz pro prohlížeč Edge odsud). Na virtuální stroj byl nainstalován SQL Server 2016 a na něm byla ze zálohy obnovena databáze aplikace (příručka).

Nastavení přístupu k SQL Serveru na virtuálním stroji

Dále bylo nutné učinit několik kroků, aby byl možný přístup k SQL Serveru zvenčí:

1. Pro firewall přidat pravidlo pro povolení požadavků na port 1433.
2. Je lepší, aby přístup na server fungoval ne přes Windows autentizaci, ale přes SQL pomocí uživatelského jména a hesla (snadnější nastavení přístupu). V tomto případě je však třeba nezapomenout povolit v nastavení SQL Serveru možnost SQL autentizace.
3. V nastavení uživatele na SQL Serveru na kartě User Mapping určit pro obnovenu databázi roli uživatele db_securityadmin.

Přenos dat

Přenos dat se vlastně skládá ze dvou fází:

1. Přenos schéma dat (tabulky, pohledy, uložené procedury atd.)
2. Přenos samotných dat

Přenos schéma dat

Provádíme následující operace:

1. Vyberte Tasks -> Generate Scripts pro přenášenou databázi.
2. Vybereme požadované objekty k přenosu nebo ponecháme výchozí hodnotu (v tomto případě budou vytvořeny skripty pro všechny objekty databáze).
3. Uvedeme nastavení pro uložení skriptu. Nejpohodlnější je uložit skript do jednoho souboru v kódování Unicode. Pak při selhání není potřeba znovu opakovat všechny kroky.

Po uložení skriptu ho lze vykonat na původním SQL Serveru (staré verzi), abychom vytvořili požadovanou databázi.

Pozor: Po provedení skriptu je nutné zkontrolovat shodu nastavení databáze z zálohy a databáze vytvořené skriptem. V mém případě ve skriptu chybělo nastavení pro COLLATE, což vedlo k chybě při přenosu dat a komplikovanému přestavění databáze za pomoci upraveného skriptu.

Přenos dat

Před přenosem dat je nutné vypnout kontrolu všech omezení na databázi:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Přenos dat provádíme pomocí průvodce importem dat Úkoly -> Importovat data na SQL Serveru, kde se nachází databáze vytvořená skriptem:

1. Uvedeme nastavení připojení ke zdroji (SQL Server 2016 na virtuálním stroji). Použil jsem Data Source SQL Server Native Client a výše zmíněnou SQL autentizaci.
2. Uvedeme nastavení připojení k cíli (SQL Server 2014 na hostitelském stroji).
3. Dále nastavíme mapování. Je nutné vybrat všechny ne read-only objekty (např. pohledy není nutné vybírat). Jako další možnosti by mělo být vybráno „Povolit vkládání do identity sloupců“, pokud takové existují.
   Pozor: pokud se při pokusu o vybrání několika tabulek a nastavení jejich vlastnosti „Povolit vkládání do identity sloupců“ vlastnost již byla dříve nastavena alespoň pro jednu z vybraných tabulek, v dialogu bude uvedeno, že vlastnost již byla nastavena pro všechny vybrané tabulky. Tento fakt může zmást a vést k chybám při přenosu.
4. Spustíme přenos.
5. Obnovujeme kontrolu omezení:

   EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Pokud se objeví nějaké chyby, zkontrolujeme nastavení, odstraníme vytvořenou databázi s chybami, znovu ji vytvoříme ze skriptu, provedeme opravy a zopakujeme přenos dat.

Závěr

Tento úkol se vyskytuje poměrně zřídka a vzniká pouze z výše uvedených omezení. Nejčastěji spočívá řešení v aktualizaci SQL Serveru nebo připojení k vzdálenému serveru, pokud to umožňuje architektura aplikace. Nicméně od lélegacy kódu a špatně provedeného vývoje nikdo není chráněn. Doufám, že budete tuto instrukci potřebovat co nejméně, a pokud se přece jen objeví, pomůže vám ušetřit spoustu času a nervů. Děkuji za pozornost!

Seznam použitých zdrojů

• Jak se vypořádat s FK omezeními při importu dat pomocí DTS Import/Export Wizard?
• Sloupec „Sloupec 2“ nelze zpracovat, protože pro něj je specifikováno více než jedno kódování (65001 a 1252).
• Jak se mohu připojit k SQL Serveru běžícímu na VirtualBoxu z mého hostitelského Macbooku.
• SQL SERVER – Povolit Identity Insert – Import Expert Wizard
• Řešení chyby Microsoft SQL Server 18456, Přihlášení selhalo pro uživatele

Zdroj: habr.com