Nessun risultato. Prova con un altro termine.
Guide
Notizie
Software
Tutorial

Strategie per trasportare un database in SQL Server 2000

Effettuiamo un passaggio indolore da un server all'altro
Effettuiamo un passaggio indolore da un server all'altro
Link copiato negli appunti

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).
  3. 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:

    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 SQL Server di destinazione
    5. Collegare il database al nuovo SQL Server tramite la procedura sp_attach_db .
    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:

    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.

    Screenshot

    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.

    Screenshot

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

    Screenshot

    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".

    Screenshot

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

    Screenshot

    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.

    Screenshot

    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.

    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
    2. Backup differenziale
    3. Backup di log

    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 minor garanzia di recupero dei dati non è possibile ripristinare il database al momento del crash o in base ad uno specifico periodo temporale
    2. 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
    3. Recupero BULK_LOGGED buona garanzia di recupero dei dati massima velocità nel caso di ripristino minor spazio possibile

    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 pubs.BAK
    2. Creare il "database copia" Pubs2
    3. Ripristinare il database Pubs2 pubs.BAK

    Per prima cosa facciamo un backup completo del database pubs:


    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:

    • FROM DISK
    • MOVE
    • L'opzione RECOVERY
    • L'opzione REPLACE

    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!

Ti consigliamo anche