Il backup con SQL Server

Quali sono gli elementi critici da considerare quando si implementa un piano di backup utilizzando gli strumenti messi a disposizione da Microsoft SQL Server? Cerchiamo di individuarli.

Se abbiamo intenzione di implementare una efficace strategia di disaster recovery, dovremmo conoscere quali sono le opzioni che il backup di SQL Server ci mette a disposizione, quali sono i database oggetto del backup e, andando ancora più nel dettaglio, come è composto un database. Elemento ancora più importante, comune a tutte le strategie di disaster recovery, è capire quanti dati siamo disposti a perdere in caso di disastro (RPO) ed in quanto tempo devo ritornate operativo (RTO).

SQL Server mette a disposizione queste modalità di backup:

• Backup del database
• Backup del transaction log
• Backup dei file dei database

Dalla nostra esperienza, l’ultima opzione è usata molto raramente ed in occasioni particolari, motivo per cui non verrà presa in considerazione qui.

Spesso si ignora il fatto che il tipo di backup che possiamo scegliere le opzioni di restore ad esso associate sono influenzate da una impostazione del database, il recovery model. Tratteremo a parte questo argomento ma per il momento ci serve solo sapere che i recovery model disponibili sono tre: FULL, SIMPLE, BULK_LOGGED e che non possiamo fare il backup del transaction log di un database che ha il recovery model impostato a SIMPLE.

Con questa modalità viene effettuata, all'interno di un file di backup, una copia dei dati e degli oggetti presenti all'interno dei file (primari e secondari) che compongono il database.

Esistono due tipologie di backup del database:

• Full, effettua il backup di tutti i dati e gli oggetti presenti all'interno del database.
• Differenziale, effettua il backup dei dati e gli oggetti presenti all'interno del database che sono cambiati dall'ultimo backup di tipo Full.

A differenza di quanto potrebbe far pensare l’aggettivo Full, questo backup si occupa di copiare solo i dati del file del database (.mdf) e non effettua una copia completa dei dati presenti all'interno del transaction log (.ldf). Motivo per cui, se un database ha un recovery model diverso da SIMPLE, è sempre necessario effettuare anche un backup del transaction log.

Spesso, quando effettuiamo un health check presso nuovi clienti, troviamo piani di backup solo parzialmente validi proprio per questo motivo: backup FULL effettuato regolarmente ma transaction log completamente ignorato ed in crescita costante. Questo porta a situazione del tipo database file da 10Gb e Transaction Log da 200Gb.

I backup di tipo FULL costituiscono comunque la base per ogni successivo ragionamento: si parte sempre da qui.

Non sempre è pensabile basare la propria strategia di backup solo sui backup di questo tipo per una serie di motivi:

1. In caso di disastro rischiamo di perdere tutti i dati dal momento dell’ultimo backup effettuato. Pensate ad esempio ad un backup full ogni 24 ore.
2. Possiamo aumentare la frequenza dei backup FULL ma avremo bisogno di spazio disponibile per i file di backup e di tempo e risorse (CPU), soprattutto se i database hanno una certa dimensione.

Un esempio potrebbe essere un backup Full una volta al giorno, a mezzanotte:

Il backup di tipo Differenziale è molto simile al backup Full ma si differenzia per il fatto che contiene solo i dati e gli oggetti modificati dall'ultimo backup Full. Attenzione: dall'ultimo backup Full e non dall'ultimo backup Differenziale.

Questo significa, come già precedentemente evidenziato, che serve prima fare un backup Full, il quale rappresenterà la nostra base di partenza.

Questo tipo di backup non è molto utilizzato per database con dimensioni ragionevoli perché, in tal caso è più comodo affidarsi ad un mix di backup Full e backup del transaction log, mentre per database di dimensioni ragguardevoli, spesso è l’unica possibilità.

Un esempio di strategia di backup che coinvolge la modalità differenziale potrebbe essere questa: backup FULL a mezzanotte e differenziali ogni due ore:

I backup differenziali precedenti l’ultimo backup full teoricamente non servono più a nulla ma è buona abitudine tenerli per almeno due o tre giorni e dopo, in ogni caso, avere la certezza che i backup full siano andati a buon fine.

Il backup del transaction log si occupa di copiare, all'interno di un file di backup, tutti i log delle transazioni inseriti all'interno del file LDF (transaction log file) dall'ultimo backup di questo tipo.

In sostanza, il file del transaction log contiene una traccia storica e dettagliata di tutte le modifiche che sono occorse all'interno del database se questo ha il recovery model impostato a FULL o BULK LOGGED. Queste informazioni rimarranno all'interno del file e non verranno sovrascritte finché non sarà effettuata una operazione di backup. Il vantaggio di avere il backup del transaction log risiede nel fatto che possiamo effettuare un restore molto più fine, ad un determinato momento nel tempo (point in time restore) e molto vicino a quando il “disastro” è accaduto.

Uno dei vantaggi del backup dei transaction log è che non impattano sulle performance del database come fanno i backup FULL e Differenziali. Questo ci consente di effettuare questo tipo di backup molto più frequentemente. Un esempio: possiamo integrare la strategia di backup differenziale per cominciare a parlare di perdita di dati nell'ordine di qualche minuto e non di qualche ora. Guardate la differenza con le immagini precedenti:

Ogni backup del transaction log contiene solo le informazioni generate dal momento dell’ultimo backup del transaction log stesso (differentemente da quanto accade con il backup Differenziale). Si crea quindi una sorta di catena di backup (backup log chain) che è molto importante non interrompere. Ogni volta che effettuiamo un backup full, iniziamo la catena nuovamente.

Se, per qualsiasi motivo, abbiamo la necessità di effettuare un backup Full e non vogliamo interrompere la catena di backup, possiamo utilizzare l’opzione COPY_ONLY.

Abbiamo appena sfiorato l’argomento soprattutto dal punto di vista tecnico. Si potrebbe scrivere un volume di qualche centinaio di pagine su questo tema e più di qualcuno ovviamente lo ha già fatto. Alla fine, riporto qualche nostro suggerimento in merito.

Speriamo sia comunque emerso il fatto che SQL Server mette a disposizione gli strumenti necessari per implementare una corretta e completa politica di backup. Nei prossimi articoli vedremo altri aspetti molto importanti come ad esempio il restore, operazione spesso sottovalutata, e gli strumenti di terze parti, che ci consentono di semplificare una serie di operazioni.

• SQL Server Backup and Restore - Gratuito se scaricato in formato PDF
  
• Beginning Backup and Restore for SQL Server
  
• Database Backup and Restore process in SQL Server