Flytta en SQL Server databas mellan olika maskiner
Förord
Att flytta en databas mellan olika SQL Server maskiner kan göras på flera sätt. Ett sätt kan vara att använda DTS för att kopiera databasen mellan servrarna, men då krävs det att man har en direkt kontakt mellan maskinerna. Annars kan man göra en backup på server A och sedan en restore på server B. En annan variant är att scripta hela databasen samt exportera datan (med bcp) till textfiler och sedan återskapa databasen samt importera datan på mottagarservern. Det sistnämnda alternativet kan dock vara ett mödosamt arbete med många filer och script, så därför brukar man inte använda det annat än vid installationer av en databas (till skillnad från att flytta en befintlig med all existerande data). I denna artikel tänker jag dock beskriva ytterligare ett sätt att flytta en databas mellan två SQL Server maskiner, nämligen att använda sig av system procedurerna sp_detach_db och sp_attach_db. Jag kommer att visa hur man använder dem, vad man bör tänka på när man gör det, samt varför jag i de flesta fall anser detta vara det bästa sättet att flytta en databas mellan två servrar
Översikt
Systemprocedurerna sp_detach_db och sp_attach_db används för att flytta en databas mellan två SQL Server maskiner. Databasen 'kopplas ur' på ursprungsservern (hädanefter kallad server A), flyttas/kopieras till destinationsservern (server B) och kopplas slutligen in där. Det innebär alltså att databasen inte finns kvar på server A efter att man kopplat ur den, men till skillnad från om man gör en DROP på databasen så finns databasfilerna fortfarande kvar. Det är dessa man sedan flyttar (alternativt kopierar om man vill koppla in databasen på server A igen) till server B, till skillnad från att kopiera databasen med en BACKUP-operation som skapar backupfiler vilka flyttas och används när man gör RESTORE på server B. En annan väsentlig skillnad mellan att använda detach/attach och backup/restore är att en backup inte motsvarar en exakt bild av databasen i det ögonblick backupen togs. Det beror på att backupjobbet kan ta en stund att utföra, och ändringar som görs från det att man påbörjat den till dess att den är slut kan komma med. sp_detach_db däremot kan inte köras om någon är inloggad mot databasen som ska kopplas ur, så därför kan inga förändringar ske i databasen efter det ögonblick då man kör proceduren.
Koppla ur databasen: sp_detach_db
Proceduren sp_detach_db gör följande:- Stänger ner den aktuella databasen på ett 'snyggt sätt', dvs den ser till att det inte finns några transaktioner som inte är slutförda samt skriver ner alla s k dirty pages till hårddisken (utförda transaktioner skrivs från minnet till databasfilerna på disk)
- Tar bort den rad i master.dbo.sysdatabases som beskriver databasen
- Stänger de filer som tillhör databasen
Filerna betraktas nu av operativsystemet som vilka filer som helst, vilka går att kopiera, flytta eller ta bort (tidigare har de varit öppnade av SQL Server och därför kan man inte bara ta bort dem ur filsystemet när SQL Server kör). Om man skulle kopiera databasfilerna utan att köra sp_detach_db eller stänga SQL Server så går det inte att koppla in databasen med dessa filer eftersom de inte är korrekt stängda, exempelvis kan de innehålla dirty pages eller avbrutna transaktioner (vilket är betydligt värre). Även en databas som kopierats efter att SQL Server stängts ner kan innehålla dirty pages, men har man bara loggfilen intakt så är det inget problem eftersom SQL Server kör en fullständig recovery när databasen kopplas in igen. Vid en full recovery rullas alla utförda transaktioner fram (dvs utförs igen om de inte är utförda i databasen) och avbrutna transaktioner rullas tillbaka, så därför kommer dirty pages inte att vara ett problem.
Man måste vara medlem i sysadmin-rollen för att exekvera sp_detach_db, och syntax för att köra den ser ut så här (från Books Online):
sp_detach_db [ @dbname = ] 'dbname' [ , [ @skipchecks = ] 'skipchecks' ]
Den första parametern är naturligtvis namnet på den databas man vill flytta, men den andra kan behöva en liten förklaring. Den kan ha värdena 'true'|'false' (som en nvarchar(10) sträng). Om man anger 'false' så kör SQL Server UPDATE STATISTICS på samtliga tabeller i databasen innan den kopplar ur databasen, och om man anger 'true' (eller inte anger något alls) så hoppar den över det. Om man tänker lägga datafilerna på ett icke-skrivbart medium (t ex en CD-ROM) kan det vara bra att köra UPDATE STATISTICS, annars finns det inget behov eftersom statistiken hålls kontinuerligt uppdaterad på vanliga databaser.
Koppla in databasen: sp_attach_db
En databas som kopplats ur med sp_detach_db (eller genom att stänga SQL Server, enligt ovanstående) kan kopplas in igen på samma server, eller någon annan server efter att filerna flyttats, genom att man kör proceduren sp_attach_db. Man måste vara medlem i sysadmin- eller dbcreator-rollen för att exekvera sp_attach_db, och syntax för att köra den ser ut så här (från Books Online):
sp_attach_db [ @dbname = ] 'dbname' , [ @filename1 = ] 'filename_n' [ ,...16 ]
Med den första parametern anger man vilken databas man vill koppla in. Observera att det inte behöver vara samma namn som databasen hette tidigare, man kan kalla den vad man vill, så länge det är unikt i den aktuella servern. Därefter anger man den fullständiga sökvägen till databasfilerna som utgör databasen. Hur många filer man måste ange beror på situationen:
- Om filerna finns kvar på samma plats som de låg på när man körde sp_detach_db (om man exempelvis bara skulle köra den för att kunna kopiera databasen och man sedan vill koppla in den igen) så räcker det att man anger den första filen (den primära datafilen, *.mdf) eftersom denna innehåller information om vilka övriga filer som databasen består av.
- Om man flyttat en eller flera filer från sin ursprungsplats så måste man även ange dessa. Observera dock att det räcker att man anger de filer man flyttat, resterande kommer att hittas automatiskt av SQL Server genom att den kan läsa denna information i den primära datafilen. Detta kan vara användbart om man t ex flyttat några filer till en ny diskpartition, medan övriga ligger kvar på ursprungsplatsen.
- Om samtliga filer flyttats, t ex till en ny server med en annorlunda sökväg till datamappen (eller var man vill placera filerna) så måste samtliga filers sökvägar anges.
- Observera att transaktionsloggen inte behöver finnas med när man kör sp_attach_db (om man inte kopierat filerna efter att ha stängt SQL Server istället för att använda sp_detach_db enligt ovan). SQL Server kommer då automatiskt att skapa en ny loggfil för databasen, med den storlek som angivits som minimistorlek för loggen. Detta kan användas som ett snabbt sätt att krympa en loggfil som blivit ohanterligt stor.
Exempel
Vi förutsätter att Pubs databasen finns på server A och består av tre datafiler, med sökvägen 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'. Följande SQL sats kopplar ur Pubs från SQL Server:
EXEC sp_detach_db @dbname=N'Pubs', @skipchecks=N'true'
Efter att filerna kopierats till server B och placerats på lämplig plats (här används sökvägen 'D:\sqldatafiles\') så kopplas den in med följande SQL sats:
EXEC sp_attach_db @dbname=N'Pubs_FromServerA'
, @filename1=N'D:\sqldatafiles\pubs.mdf'
, @filename2=N'D:\sqldatafiles\pubs1.ndf'
, @filename3=N'D:\sqldatafiles\pubs2.ndf'
För att sedan koppla in den igen på server A (vi ville bara kopiera databasen) körs följande SQL sats:
EXEC sp_attach_db @dbname=N'Pubs'
, @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Tänk på...
Nu har vi sett hur enkelt det är att flytta en databas mellan olika SQL Server maskiner. Men, som vanligt när man ska göra en avancerad operation så finns det en del saker att tänka på. Först och främst är det förutsättningarna för att man ska kunna koppla in databasen från server A på server B. Detta beror dels på vilken version av SQL Server som körs på server A resp B, och möjligen även hur dessa är konfigurerade. Det går bra att flytta en databas från SQL Server 7 till SQL Server 2000, men inte tvärtom. Självklart går det även att flytta mellan två SQL Server 7 eller två SQL Server 2000 maskiner. För att det ska gå att koppla in en databas på en server som kör SQL Server 7 så måste servern den flyttas från ha samma inställningar för Sort Order och Code Page som den mottagande servern. Om de inte överensstämmer måste server B byggas om (med verktyget Rebuild Master Utility (Rebuildm.exe) som installeras med SQL Server) med korrekta inställningar, alternativt installeras om. Dessa ämnen beskrivs inte vidare här men finns väldokumenterade i Books Online. Eftersom SQL Server 2000 kan ha varierande collations (motsvarigheten till sort order/code pages) för olika databaser, t o m ända ner på kolumnnivå, så gör det ingen skillnad vilken collation server B installerats med om man kör SQL Server 2000. Den kan då koppla in databasen oavsett vilken sort order/code page alternativt collation den skapats med, och den behåller dessutom de inställningar den skapades med även efter det att den kopplats in på server B.
En annan sak man ska vara medveten om när man flyttar en databas är hanteringen av användare och serverlogin. Om man skapat en eller flera användare (users) i databasen, vilka är kopplade mot serverlogin, måste man manuellt återskapa dessa serverlogins. Det beror på att de finns lagrade i master-databasen istället för i den databas man flyttar. Användarna i den flyttade databasen kommer att flyttas med och finnas kvar, men de kommer inte längre att vara kopplade mot något serverlogin.
Det finns ytterligare ett möjligt problem man kan komma att ställas inför. Även om det inte är så vanligt så kan det hända att databasen man ska flytta är uppdelad på väldigt många datafiler. Som ni kanske la märke till i syntaxbeskrivningen ovan så kan man inte ange fler än 16 datafiler som inparametrar till sp_attach_db. Om man nu står där med en databas som ändå är uppdelad på fler datafiler än så, vad gör man då? Jo, till att börja med så kopplar man ur databasen som normalt, med sp_detach_db. Sedan flyttar man filerna till den plats man vill ha dem på, och slutligen kör man en SQL sats enligt nedan:
CREATE DATABASE MyMultiFileDatabase
ON PRIMARY (FILENAME = 'c:\program files\microsoft sql
server\mssql\data\MyMultiFileDatabase.mdf')
FOR ATTACH
GO
Detta talar om för SQL Server att den ska skapa en ny databas från redan existerande filer. Samma regler gäller här som för sp_attach_db, dels vad gäller vilka filer som måste anges, dels vad gäller sort order/code page. Det är rekommenderat att använda sp_attach_db om man inte har fler än 16 datafiler och måste göra på detta sätt.
0 Kommentarer