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:
- migrazione verso architetture di tipo cluster
- 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:
- 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.
- 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).
- Fare il backup completo dei dati del database (non è obbligatorio ma è utile!)
- Chiudere tutte le connessioni utente al database SQL che dobbiamo spostare.
- Scollegare il database mediante la procedura sp_detach_db
- Copiare i file MDF e LDF (solo dopo aver eseguito questa procedura è possibile!) appartenenti al database dalla directory dati del SQL Server di origine SQL Server di destinazione
- Collegare il database al nuovo SQL Server tramite la procedura sp_attach_db .
- Ricollegare (solo se necessario) il database al SQL Server di origine (quello sul vecchio Desktop PC per intenderci).
- Backup completo
- Backup differenziale
- Backup di log
- Recupero Semplice minor garanzia di recupero dei dati non è possibile ripristinare il database al momento del crash o in base ad uno specifico periodo temporale
- Recupero Completo maggior garanzia di recupero dei dati è possibile ripristinare il database fino all'istante prima del crash oppure in base ad uno specifico periodo temporale
- Recupero BULK_LOGGED buona garanzia di recupero dei dati massima velocità nel caso di ripristino minor spazio possibile
- Effettuare il backup completo del database pubs pubs.BAK
- Creare il "database copia" Pubs2
- Ripristinare il database Pubs2 pubs.BAK
- FROM DISK
- MOVE
- L'opzione RECOVERY
- L'opzione REPLACE
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:
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:
Anche per la sintassi di sp_detach_db non ci sono dubbi:
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:
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:
Per aiutarci in questa delicata operazione può essere utile uno schema del genere per avere la situazione prima e dopo:
|
Voci |
Vecchio PC |
Nuovo PC |
|
Directory DATI Server SQL |
D:SQL_DATAMSSQLData |
D:SQL_DATAMSSQLData |
|
Nome file dati primario |
pubs.mdf |
pubs.ldf |
|
Nome file dati secondario |
Nessuno |
Nessuno |
|
Nome file di Log |
pubs_log.ldf |
pubs_log.ldf |
|
Riconnettere Nuovamente |
No |
Si |
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.
|
Voci |
Vecchio PC |
Nuovo PC |
|
Directory DATI Server SQL |
D:SQL_DATAMSSQLData |
D:SQL_DATA_NUOVO |
|
Nome file dati primario |
pubs.mdf |
pubs.ldf |
|
Nome file dati secondario |
Nessuno |
Nessuno |
|
Nome file di Log |
pubs_log.ldf |
pubs_log_NUOVO.ldf |
|
Riconnettere Nuovamente |
No |
Si |
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).
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 sp_attach_db
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. 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: 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. 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: Viste le premesse, passiamo a duplicare il database pubs in uno del tutto identico dal nome Pubs2. Ecco i passi da compiere: Per prima cosa facciamo un backup completo del database pubs:Tipologie di BACKUP in SQL Server 2000
Modelli di recupero dei dati in SQL Server 2000
Usare i comandi Backup e Restore per duplicare un database
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
L'istruzione RESTORE DATABASE [NomeDelDatabase] effettua il ripristino del database ed è così articolata:
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:
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!
