Strategie per trasportare un database in SQL Server 2000

Un’esigenza comune agli sviluppatori sta nello spostare (o duplicare) i database creati nella propria Workstation sui server di test o di produzione (oppure sul nuovissimo portatile appena acquistato!). Questa operazione può essere fatta anche più volte durante la fase di sviluppo di un’applicazione, praticamente fino al rilascio di una versione stabile e definitiva della stessa.

Ci sono però anche altri scenari in cui diventa vitale spostare un database da un server ad un altro:

1. migrazione verso architetture di tipo cluster
2. l’upgrade hardware della macchina (dobbiamo aggiungere un disco più capiente e veloce).

Per svolgere questi compiti possiamo contare su differenti opzioni, ognuna delle quali fa uso della potenza del linguaggio T-SQL:

1. Usare le procedure di sistema sp_attach_db e sp_detach_db per “staccare” e “attaccare” un database da un Server SQL ad un altro.
2. Sfruttare i comandi BACKUP e RESTORE per salvare e successivamente ripristinare il database su un altro Server SQL (oppure sullo stesso Server SQL in caso di duplicazione).

Ora vedremo come procedere!

Attenzione: prima di iniziare facciamo sempre un Backup dei dati.

Per evitare grane, prima di effettuare operazioni simili, dobbiamo sempre assicurarci di avere un backup completo dei dati in un posto sicuro. Questa operazione ci mette al riparo se qualcosa non dovesse andare per il verso giusto in particolar modo quando si lavora su server in produzione, ricordiamoci che prevenire è meglio che curare.

Un’ultimo consiglio: se volete fare dei test usate sempre i databases Pubs e NorthWind!

Usare le procedure di sistema sp_attach_db e sp_detach_db

Le procedure sp_attach_db e sp_detach_db permettono di “sconnettere” e “riconnettere” in modo pulito, un database dal Server SQL al quale appartiene.

Dopo aver eseguito la procedura sp_detach_db su un database questo viene fisicamente sconnesso e quindi scompare dall’Enterprise Manager (EM) e dall’elenco dei databases disponibili.

Fatto ciò è possibile copiare o muovere dalla directory dati di SQL Server i files dati (primario e secondari) e di log del database senza incappare in blocchi del sistema .

Eseguendo correttamente sp_detach_db il database non viene compromesso in alcun modo e quindi possiamo tranquillamente connetterlo nuovamente al Server SQL (potrebbe essere quello originale ma anche un altro Server SQL) anche in un secondo tempo tramite la procedura sp_attach_db.

Una volta connesso (o riconnesso) il database appare nuovamente all’interno dell’EM e nell’elenco dei databases disponibili del Server SQL. Usando queste procedure di sistema sarà SQL Server stesso a garantire il corretto svolgimento delle operazioni di sconnessione e connessione.

Uno sguardo alla sintassi di sp_attach_db e sp_detach_db

La sintassi della procedura sp_attach_db è molto intuitiva:

sp_attach_db [ @dbname = ] ‘dbname’, [ @filename1 = ] ‘filename_n’ [,…16 ]

dove @dbname è il nome del database da connettere e @filename1 fino a @filename16 sono i nomi (incluso il percorso) dei files del database che devono essere collegati al Server SQL, ecco un esempio:

EXEC sp_attach_db @dbname = N’pubs’,
   @filename1 = N’d:SQL_DATAMSSQLDatapubs.mdf’,
   @filename2 = N’ d:SQL_DATAMSSQLDatapubs_log.ldf’

Anche per la sintassi di sp_detach_db non ci sono dubbi:

sp_detach_db [ @dbname = ] ‘dbname’  [, [ @skipchecks = ] ‘skipchecks’ ]

dove @dbname è il nome del database da sconnettere e @skipchecks un flag che ci dice se eseguire o meno in comando UPDATE STATISTICS, perciò per scollegare un database sarà sufficiente:

EXEC sp_detach_db ‘pubs’, ‘true’

L’unica cosa da ricordare prima di scollegare un database è quella di chiudere tutte le connessioni degli utenti a quel database prima di procedere, altrimenti l’operazione non andrà a buon fine.

Un semplice esempio con il database Pubs

Vediamo ora come trasportare il database Pubs dal SQL Server del nostro vecchio Desktop PC al PC Portatile che abbiamo appena acquistato, i passi da seguire sono:

1. Fare il backup completo dei dati del database (non è obbligatorio ma è utile!)
2. Chiudere tutte le connessioni utente al database SQL che dobbiamo spostare.
3. Scollegare il database mediante la procedura sp_detach_db.
4. Copiare i file MDF e LDF (solo dopo aver eseguito questa procedura è possibile!) appartenenti al database dalla directory dati del SQL Server di origine (Desktop PC) alla directory dati del SQL Server di destinazione (PC Portatile).
5. Collegare il database al nuovo SQL Server tramite la procedura sp_attach_db, specificando eventualmente un nuovo percorso per i files di dati.
6. Ricollegare (solo se necessario) il database al SQL Server di origine (quello sul vecchio Desktop PC per intenderci).

Per aiutarci in questa delicata operazione può essere utile uno schema del genere per avere la situazione prima e dopo:

In questo caso non ci sono differenze né tra i percorsi delle directory dati né tra i nomi dei files da muovere, ma potremmo essere nella situazione opposta cioè dover connettere un database su un percorso differente da quello originale oppure con un nome differente per il file di log o per il file dati.

Come spesso accade in SQL Server possiamo decidere di procedere in modi differenti, scrivendo il codice in Query Analyzer (QA) oppure aiutandoci con EM, vediamoli entrambi.

Usiamo EM per spostare il database Pubs

Tramite EM possiamo eseguire la procedura sp_detach_db con un semplice click sulla voce di menu “Detach Database”.  Possiamo trovarla cliccando con il tasto destro del mouse su ogni database presente sul Server SQL.

Apparirà una maschera che informa sulle connessioni che stanno attualmente utilizzando il database (se esistono dobbiamo chiuderle con il tasto Clear) e da cui è possibile effettuare la sconnessione del database.

Una volta premuto OK, un messaggio ci informa se l’operazione è giunta a buon fine.

Una volta scollegato il database possiamo spostarci con “Gestione Risorse” nella directory dati del Server SQL del Desktop PC e copiare tutti i files del database su un CD allo scopo di poterli portare sul PC Portatile.

Una volta copiati i file sul PC Portatile passiamo alla fase di connessione del database al nuovo Server SQL.

Aprendo l’EM e cliccando con il tasto destro del mouse sul nodo Databases apparirà un menu con la voce “Attach Database”.

Apparirà una maschera da cui è possibile recuperare sul file system del PC Portatile il file dati principale del database (con il suffisso MDF

Appena trovato il file dati MDF SQL Server lo carica e controlla l’esistenza dei files di log e dati (i secondari se esistono) ad esso abbinati.

In questa fase dobbiamo associare un owner, tipicamente sa, ed un nome per il database da collegare. Ora non dobbiamo fare altro che premere OK e collegare il database.

Usare T-SQL per spostare il database Pubs

Sarà sicuramente comodo usare EM, ma trovo il linguaggio T-SQL più espressivo e conciso e poi anche EM alla fine esegue codice T-SQL o no? Quindi apriamo il QA e cominciamo!

Sconnettiamo il database dal Server SQL del Desktop PC (ricordiamoci che non ci siano utenti connessi, mettiamo offline il database ad esempio).

Use master
GO
sp_detach_db N’pubs’, N’true’

Poi copiamo i file sul CD e quindi sul PC Portatile, fatto ciò possiamo collegare i files del database sul Server SQL del PC Portatile:

Use master
GO
sp_attach_db N’pubs’, N’D:SQL_DATAMSSQLDatapubs.mdf’, N’D:SQL_DATAMSSQLDatapubs_log.ldf’

Ecco fatto, è bastato solamente un minuto!

Questa tecnica è veramente sicura e molto rapida, ideale per trasportare database da ambienti di sviluppo ad ambienti di produzione oppure per upgrade di macchina o parti di macchina (es. componenti hardware) a patto che tutte le connessioni al database in oggetto siano chiuse.

Abbiamo visto come le procedure sp_detach_db e sp_attach_db servano per spostare un database da un Server SQL ad un altro. Questa tecnica è molto efficace, ma possiede un limite: non ci devono essere connessioni aperte al database.

Se invece volessimo  duplicare (o spostare) un database mantenendo attive le sue connessioni? In questo caso utilizzeremo i comandi BACKUP e RESTORE.

Prima di passare all’azione è utile fare una panoramica delle tipologie di backup dei dati e dei modelli di recupero dei dati in SQL Server 2000.

Tipologie di BACKUP in SQL Server 2000

Per evitare qualsiasi spiacevole inconveniente è sempre importante fare backup regolari dei nostri dati. In effetti può capitare, per cause accidentali o per errori di diversa natura, che i files dati dei databases vengano danneggiati. In questi casi è possibile, grazie all’operazione di backup, recuperare i files danneggiati ripristinando il database tramite i backup.

La quantità di dati che è possibile recuperare dipende dal modello di backup scelto per il database, SQL Server 2000 ne supporta tre:

1. Backup completo: vengono copiate tutte le pagine di un database su una periferica di backup (può essere una unità nastro, un file di rete o locale o una named pipe).
2. Backup differenziale: copia solo gli extent modificati dall’ultima volta in cui è stato effettuato un backup completo.
3. Backup di log: copia tutti i record scritti nel log delle transazioni dall’ultimo backup di log o completo. Il funzionamento del comando BACKUP LOG dipende anche dal modello di recupero supportato da SQL Server.

Un backup completo di un database può essere fatto anche mentre il database in uso; questo ci permette di effettuare spostamenti o duplicazioni di un database in uso, così come vedremo in seguito.

Modelli di recupero dei dati in SQL Server 2000

In SQL Server 2000 è possibile scegliere tra tre differenti modelli (modalità) di recupero dei dati, in base a questa scelta possiamo determinare la velocità e la dimensione del file di log delle transazioni e soprattutto il grado di protezione dei nostri dati:

1. Recupero Semplice: questo modello fornisce la minor garanzia di recupero dei dati nel caso di files dati danneggiati.  Con questa impostazione non è possibile ripristinare il database al momento del crash o in base ad uno specifico periodo temporale, in quanto le transazioni vengono eliminate dal log delle transazioni ad ogni checkpoint. Per avere un maggior grado di protezione sui dati occorre selezionare uno degli altri modelli disponibili.
2. Recupero Completo: questo modello fornisce la maggior garanzia di recupero dei dati nel caso di perdite o danneggiamenti. Quando un database è in questa modalità tutte le modifiche apportate dalle operazioni come INSERT, UPDATE e DELETE vengono tracciate nei log delle transazioni. Vengono altresì registrate le operazioni di massa come bcp, SELECT INTO, BULK INSERT ed anche le operazioni CREATE INDEX. Con questa impostazione è possibile ripristinare il database fino all’istante  prima del crash oppure in base ad uno specifico periodo temporale. Le transazioni ancora in corso durante il crash, ma non ancora concluse, verranno annullate. L’elevato grado di protezione assicurato da questo modello comporta un notevole dispendio di spazio per il mantenimento del file di log delle transazioni ma anche un maggior tempo per la creazione dei backup dello stesso.
3. Recupero BULK_LOGGED: questo modello fornisce una buona garanzia di recupero dei dati, la massima velocità nel caso di ripristino e il minor spazio possibile occupato dal file di log delle transazioni. Questo è possibile grazie alla ridotta registrazione delle operazioni di massa ( BULK INSERT, CREATE INDEX, SELECT INTO, ecc…) nel file di log delle transazioni.

Usare i comandi Backup e Restore per duplicare un database

Viste le premesse, passiamo a duplicare il database pubs in uno del tutto  identico dal nome Pubs2. Ecco i passi da compiere:

1. Effettuare il backup completo del database pubs su un file denominato pubs.BAK.
2. Creare il “database copia” Pubs2 (questo passaggio è opzionale vedremo il perché successivamente)
3. Ripristinare il database Pubs2 partendo dal file di backup pubs.BAK.

Per prima cosa facciamo un backup completo del database pubs:

Use master
GO
BACKUP DATABASE [Pubs]
    TO  DISK = ‘D:SQL_DATAMSSQLBACKUPPubs.BAK’
    WITH  INIT, NOUNLOAD
   , name = ‘Pubs backup’
   , Description = ‘Backup Completo del database Pubs’
   , NOFORMAT

Poi, usando l’istruzione CREATE DATABASE, andiamo a creare il database Pubs2 (la “sintassi minima” specificata qui sotto provvede a creare il database secondo le opzioni di default):

Use master
GO
CREATE DATABASE [Pubs2]

In seguito andiamo ad effettuare il ripristino del database Pubs2, partendo dal file di backup di pubs.BAK. Così facendo avremo una copia del database pubs sullo stesso SQL Server.

Use master
GO
ALTER DATABASE Pubs2 SET SINGLE_USER WITH ROLLBACK Immediate
RESTORE DATABASE Pubs2
    FROM DISK = ‘D:SQL_DATAMSSQLBACKUPPubs.BAK’
    WITH RECOVERY, REPLACE,
            MOVE ‘pubs’
    TO ‘D:SQL_DATAMSSQLDataPubs2.mdf’,
    MOVE ‘pubs_log’
    TO ‘D:SQL_DATAMSSQLDataPubs2_log.ldf’

ALTER DATABASE Pubs2 SET MULTI_USER

Il comando ALTER DATABASE con l’opzione SET SINGLE_USER informa SQL Server di mettere il database Pubs2 in modalità singolo utente, specificando poi nell’opzione “ROLLBACK Immediate” di chiudere tutte le connessioni non qualificate al database (l’unica connessione qualificata è quella che esegue l’istruzione ALTER).

L’istruzione RESTORE DATABASE [NomeDelDatabase] effettua il ripristino del database ed è così articolata:

• FROM DISK indica il percorso del file di backup da cui prendere i files per ripristinare il database
• MOVE [NomeLogicoDelFile] TO [PercorsoDestinazioneDelFile] indica come chiamare e dove mettere i files dati e di log
• L’opzione RECOVERY  informa SQL Server di rendere immediatamente operativo il database dopo il ripristino.
• L’opzione REPLACE informa SQL Server che può rimpiazzare il contenuto dei files dati e di log del database Pubs2 con quelli del database pubs contenuti nel file di backup

Il comando ALTER DATABASE con l’opzione SET MULTI_USER informa SQL Server di riportate il database Pubs2 in modalità multi-utente. Bene, a questo punto abbiamo duplicato il database pubs!

È interessante notare che se il database Pubs2 non fosse esistito, SQL Server lo avrebbe creato automaticamente durante l’operazione di ripristino, ecco il codice:

–Il database Pubs2 non esiste
RESTORE DATABASE Pubs2
    FROM DISK = ‘D:SQL_DATAMSSQLBACKUPPubs.BAK’
    WITH RECOVERY,
    MOVE ‘pubs’
    TO ‘D:SQL_DATAMSSQLDataPubs2.mdf’,
    MOVE ‘pubs_log’
    TO ‘D:SQL_DATAMSSQLDataPubs2_log.ldf’
–Ora il database Pubs2 è stato creato correttamente

In questo caso l’opzione REPLACE è inutile, in quanto il database non esiste.

Conclusione

Come abbiamo visto le possibilità per salvare, copiare o spostare e ripristinare un database SQL sono tantissime; e bene però tenere a mente una cosa: prima di compiere una qualsiasi operazione di questo tipo ricordiamoci di fare un copia di backup dei dati. La prudenza non è mai troppa!