Het probleem: Een database inclusief inhoud overzetten van een SQL Server 2008r2 instantie naar SQL Server 2008 instantie.

Het kan voorkomen dat je een database van een hogere versie naar een lagere versie wilt overzetten, inclusief de data. De procedure die ik hieronder beschrijf is één manier om dat te doen. Er zijn ongetwijfeld nog andere werkbare manieren, bijvoorbeeld met gebruikmaking van SSIS.

In dit voorbeeld zet ik een database over van SQLServer 2008 r2 naar SQLServer 2008. Het is niet mogelijk om dat te doen met behulp van een backup en restore, en ook niet met behulp van detach – attach database.

We gaan hier de database scripten en vervolgens de gegevens via bcp overzetten.

Bcp staat voor bulk copy program. Dit is een utility programma dat onafhankelijk van de SQL Server kan worden gebruikt. Het wordt opgestart vanaf de command line.

Scripten van de database: Selecteer in de Management Studio de database die je wilt overzetten, rechtsklik erop en selecteer Tasks en vervolgens Generate Scripts. Je komt in een wizzard terecht waarvan je het introductiescherm overslaat. In het volgende scherm kun je objecten selecteren die je in het script wilt openemen. Je kunt hier kiezen voor de gehele database of voor specifieke objecten. We kiezen voor de gehele database, maar het kan geen kwaad om hier even te kijken wat de mogelijkheden zijn wanneer je de andere keuze maakt.

Bovenstaande keuze maak je niet,  je kiest hier voor ‘script entire database and all database objects’. Je kom dan in het volgende scherm:

Je geeft hier een bestandsnaam op en laat in principe de standaardinstellingen intact. Het loont wel de moeite om even op de button ‘Advanced’ te klikken en alle opties daarachter te bekijken. Vooral de optie ‘Types of data to script’ is interessant. Je kunt er hier voor kiezen om behalve het schema ook de data in het script mee te nemen. Voor kleine databases en voor testdatabases is dit zeker het overwegen waard, maar heb je een database van enige omvang, dan is het sterk aan te raden om hier te kiezen voor schema only, hetgeen overigens de standaardinstelling is. Mocht je ook de data in het script meenemen, dan is de kans aanwezig dat je in een frustrerend proces terechtkomt.

 Klik vervolgens twee maal op next en als het goed is worden alle taken succesvol uitgevoerd. Vergeet vooral niet hoe het script heet en waar je het hebt opgeslagen.

De volgende stap is het aanmaken van de bcp instructies. Het bcp programma is een uiterst flexibel instrument waarin je tal van opties kunt instellen. Ik zal er hier enkele van bespreken. Voor meer informatie verwijs ik graag naar: http://msdn.microsoft.com/en-us/library/ms162802.aspx. Daar vind je een uitstekende uitleg van alle mogelijkheden van het programma.

Ik kies ervoor om voor elke tabel waarvan ik de data wil exporteren een aparte instructieregel aan te maken. Die regel ziet er als volgt uit:

bcp TestBox.dbo.Numbers out C:\Numbers.txt –w –S Srvrman –T

Een korte toelichting hierbij: TestBox is de naam van de database, dbo is de schemanaam en Numbers is de tabelnaam. ‘out’ geeft de richting van het datatransport aan, in dit geval exporteer je dus. Ga je straks importeren, dan komt hier ‘in’  te staan. –w geeft aan dat je karakters in unicode formaat exporteert, je kunt hier –c ingeven wanneer je alleen maar ASCII karakters hebt. –S geeft aan welke server je wilt benaderen, in dit geval is dat Srvrman. Tenslotte staat –T voor trusted connection, dat wil zeggen dat er gebruikt gemaakt wordt van de integrated security van Windows. Je kunt hier ook een gebruikersnaam en wachtwoord opgeven, mocht dat nodig zijn. Zie voor meer informatie daarover de boven gegeven link.

Om vervolgens alle tabellen uit een database voor export klaar te zetten maken we gebruik van één van de INFORMATION_SCHEMA views. Je kunt hier ook sysobjects gebruiken, of system_objects of sys.objects. Ik geef de voorkeur aan de INFORMATION_SCHEMA views omdat ze alle informatie bevatten die ik nodig heb en omdat verschillende van de andere genoemde objecten op de nominatie staan uit SQLServer verwijderd te worden.

use TestBox

go

select ‘bcp ‘ + TABLE_CATALOG + ‘.’ + TABLE_SCHEMA + ‘.’ + TABLE_NAME +’ out C:\SQLData\’ + TABLE_NAME + ‘.dat –w –S Srvrman -T’  from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE’

Nu moet je nog in de toolbar van Management Studio aangeven dat je de resultaten van de query naar een bestand wil sturen door op het icoontje ‘Results to File’ te klikken. Voer je vervolgens deze query uit dan word je gevraagd een bestandsnaam en lokatie in te voeren. Maak hier een .bat bestand van dat je later gemakkelijk kunt uitvoeren.

De inhoud van dit bestand ziet er dan ongeveer als volgt uit:

—————————————————————————————————————————————————————————————————————————————————————-

bcp Testbox.dbo.Testorderdetails out C:\SQLData\Testorderdetails.dat -w –S Srvrman -T

bcp Testbox.dbo.Numbers out C:\SQLData\Numbers.dat -w –S Srvrman -T

bcp Testbox.dbo.TstCalendar out C:\SQLData\TstCalendar.dat -w –S Srvrman -T

bcp Testbox.dbo.Outputs out C:\SQLData\Outputs.dat -w –S Srvrman -T

(4 row(s) affected)

Je moet nog wat overbodige regels uit dit bestand verwijderen om er een net .bat bestand van te maken. Ik moet er wel op wijzen dat mijn ervaring leert dat je dit bestand met een lege regel moet beginnen, omdat anders het eerste bestand niet wordt geexporteerd. Ik weet niet hoe dat komt en ik zou het heel fijn vinden als iemand mij het kon uitleggen.

Nu moeten we de database gaan aanmaken op de andere server. Open het gegenereerde sql script in de Management Studio van de doel database server. Waarschijnlijk staan de paden naar de datafiles en logfiles niet goed, – die verwijzen immers nog naar de lokaties van de bronserver -, maar als je deze aanpast zodat ze op de nieuwe server te vinden zijn, dan zal dit script zonder problemen lopen.

Nu moet je de export van de data vanaf de bronserver nog draaien. Dit doe je door het .bat bestand op te starten. Let er wel op dat het goed zit met de rechten op de folders waarin je de data wegschrijft. Je krijgt een hele verzameling .dat bestanden (of .txt, het is maar net wat je als extensie opgegeven hebt). Die kopieer je naar de doelserver. Ook het .bat bestand kopieer je. Dat .bat bestand moet je echter nog bewerken voor import. Dat doe je door met zoek en vervang in je tekst editor ‘out’  te vervangen door ‘in’ en de naam van je bronserver door de naam van je doelserver.

Een dump maken van de database naar tekstbestanden.

Wil je alleen maar data wegschrijven naar tekstbestanden, dan kun je ermee volstaan een bcp instructie uit te voeren. Je hoeft dus niet gebruik te maken van de Management Studio. Je moet in dit geval echter wel de databasenaam opgeven, dat is in dit geval TOOLSET. Het is een min of meer geneste bcp instructie:

bcp “select ‘bcp TOOLSET.’ + TABLE_SCHEMA + ‘.’ + TABLE_NAME +’ out C:\SQLData\’ + TABLE_NAME + ‘.dat –w –S Srvrman -T’  from TOOLSET.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE’” queryout test1.bat  –w –S Srvrman -T

Dit .bat bestand zou je gewoon moeten kunnen uitvoeren, maar ik kom een vreemde foutmelding tegen. Pas wanneer ik aan het begin van het bestand een <enter> geef en vervolgens de hele inhoud van het bestand in een nieuw, leeg bestand kopieer, dan kan ik dat nieuwe bestand uitvoeren. Weet iemand hier een oplossing voor te vinden?