da parte di: Atif Shehzad | Aggiornato: 2009-06-15 | Commenti (4) | Correlati: Altro > Backup

Problema

Ho implementato un piano di backup per i miei database SQL Server. Faccio un backup giornaliero completo supportato da backup differenziali ogni ora. Un piano di ripristino è documentato sulla base di questo piano di backup insieme alla posizione e al tempo dei backup menzionati nel piano. Spesso mi viene richiesto di aggiornare il server di test o di sviluppo con una copia aggiornata del database di produzione. A questo scopo faccio un backup completo del database richiesto. Il problema è che questi backup ad hoc interrompono la mia sequenza di recupero pianificata in caso di un recupero necessario. C’è un modo per far sì che i miei backup ad hoc non interrompano la sequenza del mio piano di backup?

Soluzione

Per fortuna in SQL Server 2005 e successivi abbiamo un’opzione di backup per questa situazione. Questa opzione di backup è conosciuta come backup di sola copia. Questa opzione è specifica per creare un backup ad hoc che non disturba la sequenza di ripristino pianificata per quel database.

I backup di sola copia possono essere usati per creare un backup completo o un logbackup delle transazioni. Questa opzione non è implementata per i backup differenziali. In scenari pratici avrete raramente bisogno di creare un backup di log di sola copia, tuttavia l’opzione di sola copia può essere usata frequentemente con i backup completi.

Anche se l’opzione di sola copia è disponibile per SQL Server 2005 non è possibile crearli usando SSMS in SQL 2005. Se avete SSMS 2008 potete usare la GUI o dovete usare una dichiarazione T-SQL per creare backup Copy Only.

Prima vediamo come questo può essere fatto usando SSMS 2008 per creare backup di sola copia

• Vai alla finestra del database di backup come faresti per un normale backup

• Appena sotto il menu “Tipo di backup”, troverai una casella di controllo per “Copy OnlyBackup”
• Clicca questa casella di controllo
• Compila tutte le altre informazioni relative come faresti per un backup normale e clicca su OK

Nota: La casella di controllo per “Copia solo backup” sarà attiva anche per i backup differenziali, ma non avrà alcun effetto diverso dalla creazione di un normale backup differenziale.

In secondo luogo, per creare un backup di sola copia con T-SQL potete lanciare il seguente comando:

-- Create full backup with Copy Only optionBACKUP DATABASE AdventureWorks TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'WITH COPY_ONLYGO

Provare il concetto

Prima di andare avanti sarà bene riassumere il concetto che un backup completo con l’opzione di sola copia è indipendente dalla sequenza dei vostri altri backup normali.Quindi, dopo aver creato un backup con l’opzione di sola copia, sarete in grado di lavorare con il piano di ripristino basato sui vostri backup programmati senza che questo abbia un impatto sul vostro processo di ripristino.

Utilizzeremo le informazioni LSN (log sequence number) per tracciare il backup completo che è la base per i backup differenziali. Per prima cosa noteremo l’LSN per la base differenziale del database AdventureWorks.

--Script # 1: Note current differential base LSNSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Note differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO

Il differential_base_lsn influenza la sequenza in cui la combinazione di backupsono da ripristinare in un ripristino. Potete anche ottenere informazioni dettagliate sull’LSN di qualsiasi database dalla tabella msdb..backupset.

Ora dobbiamo confermare che un backup completo senza l’opzione Copy Only aggiornerà il differential_base_lsn. A questo scopo emetteremo un comando di backup completo senza l’opzione Copy Only e noteremo il cambiamento nell’LSN per provare che l’LSN aggiornato della base differenziale è l’LSN del nostro ultimo backup completo.

--Script # 2: Create full backup and compare LSN information-- Create full backup -- Run script after changing Backup pathBACKUP DATABASE AdventureWorks TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'GO-- Get differential_base_lsn after full backupSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Updated differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO-- Get LSN of recent full backup for match purposeSELECT database_name, backup_start_date, is_copy_only,first_lsn as 'LSN of full bakup'FROM msdb..backupset WHERE database_name = 'AdventureWorks'ORDER BY backup_start_date DESCGO

Nel seguente set di risultati, possiamo verificare che l’LSN è stato cambiato per la base di ripristino del backup differenziale e corrisponde al nostro LSN del backup completo. Entrambi gli LSN contrassegnati di seguito sono gli stessi, il che conferma che l’ultimo backup è la nostra base differenziale.

Ora creeremo un backup completo con l’opzione Copy Only e sarà dimostrato che il backup completo con l’opzione Copy Only non influenzerà l’LSN della base differenziale del nostro database. In altre parole, il backup completo con l’opzione Copy Only non influenzerà il backup completo di base per i backup differenziali.

--Script # 3: Create full backup with copy only option and compare LSN information-- Create full backup with copy only option-- Run script after changing Backup pathBACKUP DATABASE AdventureWorks TO DISK = 'D:\CopyOnly_AdventureWorks.bak'WITH COPY_ONLYGO-- Get differential_base_lsn after full backup with copy only optionSELECT DB_NAME(database_id) AS ,differential_base_lsn AS 'Un changed differential base LSN'FROM sys.master_filesWHERE database_id = DB_ID('AdventureWorks')AND type_desc = 'ROWS'GO-- Get LSN of recent full backup with copy only option for match purposeSELECT database_name, backup_start_date, is_copy_only,first_lsn as 'LSN of last full bakup'FROM msdb..backupset WHERE database_name = 'AdventureWorks'ORDER BY backup_start_date DESCGO

Nell’immagine seguente possiamo verificare che dopo un backup completo con l’opzione Copy Only l’LSN differenziale di base è invariato e corrisponde all’LSN del backup completo precedente (entrambi sono segnati in rosso). Notate anche che l’ultimo backup completo con l’opzione Copy Only è anch’esso presente (segnato in verde).

Le seguenti considerazioni saranno utili quando si utilizzano i backup con l’opzione Copy Only.

• L’opzione Copy Only funzionerà anche per i database con livello di compatibilità 80 in un’istanza di SQL Server 2005
• I backup dei log delle transazioni con l’opzione Copy Only conservano il punto di archivio del log esistente, quindi non troncheranno i log delle transazioni di quel database.
• Non è richiesta alcuna considerazione avanzata durante il ripristino di un backup creato con l’opzione Copy Only.
• Un backup completo con l’opzione Solo copia non può essere usato come base per il ripristino di backup differenziali
• Un backup dei log con l’opzione Solo copia può essere creato per i database con recoverymodel full o bulk log only.
• Un backup completo con l’opzione Copy Only può essere creato per i database con qualsiasi modello di recupero.

Passi successivi

Mentre prendete qualsiasi backup dal vostro piano di backup considerate l’uso dell’opzione Copy Only per evitare qualsiasi confusione e disturbo del vostro piano di recupero. Tenendo presente che i backup di sola copia sono indipendenti dai backup normali.

• Quindi la prossima volta che avete bisogno di aggiornare i vostri ambienti di test o di sviluppo, considerate di usare l’opzione di sola copia per non interrompere il vostro processo di recupero
• Per informazioni sui modelli di recupero dei database cliccate qui o qui
• Qui c’è un suggerimento relativo sui backup di SOLA COPIA

Ultimo aggiornamento: 2009-06-15

Informazioni sull’autore

Atif Shehzad è un appassionato SQL Server DBA, revisore tecnico e autore di articoli.
Vedi tutti i miei consigli

Risorse correlate

• Altri consigli di SQL Server DBA…