🥇Gegevensoverdracht van de back-up van een nieuwere versie van MS SQL Server naar een oudere versie

Achtergrond

Een keer had ik een back-up van de productie database nodig om een fout te reproduceren.

Tot mijn verbazing stuitte ik op de volgende beperkingen:

De back-up van de database was gemaakt op versie SQL Server 2016 en was niet compatibel met mijn SQL Server 2014.
Op mijn werkomputer werd als besturingssysteem gebruikt Windows 7, daarom kon ik niet upgraden SQL Server naar versie 2016
Het ondersteunde product was onderdeel van een groter systeem met een sterk gekoppelde legacy-architectuur en verwees ook naar andere producten en databases, dus de implementatie op een andere workstation kon veel tijd kosten.

Gezien het bovenstaande, kwam ik tot de conclusie dat het tijd was voor tijdelijke alternatieve oplossingen.

Gegevensherstel uit de back-up

Ik besloot een virtuele machine te gebruiken Oracle VM VirtualBox met Windows 10 (je kunt een testafbeelding voor de Edge-browser nemen) hier). Op de virtuele machine was SQL Server 2016 geïnstalleerd en de applicatiedatabase was hersteld vanuit de back-up (handleiding).

Toegang tot SQL Server op de virtuele machine instellen

Vervolgens moesten er een aantal stappen worden ondernomen om toegang tot SQL Server van buitenaf mogelijk te maken:

Voeg een regel toe aan de firewall om verzoeken op poort 1433.
bij voorkeur moet de toegang tot de server niet via Windows-authenticatie, maar via SQL met gebruikersnaam en wachtwoord verlopen (gemakkelijker om toegang in te stellen). Maar in dit geval moet je niet vergeten de mogelijkheid tot SQL-authenticatie in de eigenschappen van SQL Server in te schakelen.
In de gebruikersinstellingen op SQL Server op het tabblad User Mapping moet je voor de herstelde database de rol van gebruiker opgeven db_securityadmin.

Gegevensoverdracht

De daadwerkelijke gegevensoverdracht bestaat uit twee fasen:

Overdracht van het gegevensschema (tabellen, views, opgeslagen procedures, enz.)
Overdracht van de gegevens zelf

Overdracht van het gegevensschema

Voer de volgende stappen uit:

We kiezen Taken -> Scripts genereren voor de over te dragen database.
Kies de gewenste objecten voor overdracht of laat de standaardinstelling staan (in dat geval worden scripts voor alle databaseobjecten aangemaakt).
Geef instellingen op voor het opslaan van het script. Het is het gemakkelijkst om het script in één bestand in Unicode-indeling op te slaan. Dan hoef je in geval van een storing niet alle stappen opnieuw te doorlopen.

Na het opslaan van het script kan het op de oorspronkelijke SQL Server (oude versie) worden uitgevoerd om de vereiste database aan te maken.

Let op: Na afloop van het script moet de overeenstemming van de database-instellingen uit de back-up met die van de database die door het script is gemaakt, worden gecontroleerd. In mijn geval ontbrak de COLLATE-instelling in het script, wat leidde tot een fout bij de gegevensoverdracht en het opnieuw creëren van de database met een aangevuld script.

Gegevensoverdracht

Voor het overdragen van gegevens moeten alle beperkingen op de database worden uitgeschakeld:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

De gegevensoverdracht gebeurt met behulp van de databankimportwizard. Taken -> Gegevens Importeren op SQL Server, waar de database die door het script is gemaakt, zich bevindt:

Geef de verbindingsinstellingen voor de bron op (SQL Server 2016 op een virtuele machine). Ik heb Data Source gebruikt SQL Server Native Client en de eerder genoemde SQL-authenticatie.
Geef de verbindingsinstellingen voor de bestemming op (SQL Server 2014 op de hostmachine).
Vervolgens stellen we de mapping in. Kies alle niet alleen-lezen objecten (bijvoorbeeld hoeven we geen weergaven te selecteren). Als aanvullende opties moet je selecteren "Toestaan dat er wordt ingevoegd in identity-kolommen", als deze worden gebruikt.
Let op: als je probeert meerdere tabellen te selecteren en deze een eigenschap toe te wijzen, "Toestaan dat er wordt ingevoegd in identity-kolommen" de eigenschap al eerder is ingesteld voor ten minste een van de geselecteerde tabellen, zal in de dialoog worden aangegeven dat de eigenschap al is ingesteld voor alle geselecteerde tabellen. Dit kan verwarrend zijn en leiden tot overdrachtsfouten.
Start de overdracht.

Herstel de controle op beperkingen:

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Als er fouten optreden, controleer dan de instellingen, verwijder de database die met fouten is gemaakt, maak deze opnieuw aan met het script, breng de correcties aan en herhaal de gegevensoverdracht.

Conclusie

Deze taak komt vrij zelden voor en ontstaat alleen door de hierboven genoemde beperkingen. Meestal bestaat de oplossing uit het bijwerken van SQL Server of het verbinden met een externe server, als de architectuur van de applicatie dat toestaat. Niemand is echter beschermd tegen legacy-code en ondeskundige ontwikkeling. Ik hoop dat je deze handleiding niet nodig hebt, maar als dat wel zo is, helpt het je hopelijk om veel tijd en zenuwen te besparen. Bedankt voor je aandacht!