Tutti sappiamo quanto siano importanti i backup dei nostri database, in quanto rappresentano lo strumento principale per permettere un ripristino dei dati a fronte del verificarsi di un evento disastroso. Di conseguenza, è assolutamente fondamentale per un tecnico IT avere una ottima conoscenza delle tipologie di backup disponibili in SQL Server e delle buone pratiche per mettere in atto un piano di backup efficace e coerente.
In rete si trova dell’ottima documentazione che spiega in modo dettagliato le tipologie e le modalità di esecuzione dei backup in SQL Server.
La volontà di questo articolo è di andare oltre alla classica documentazione tecnica disponibile, cercando di rispondere a quesiti e/o falsi miti che spesso mi sono stati sottoposti, o che mi sono posto io stesso nei primi anni di attività in ambito SQL Server.
Sono sicuro che alcuni punti potrebbero stupire anche qualche DBA non più di primo pelo 😊.
Iniziamo!
Le operazioni di backup possono causare dei lock su alcuni oggetti, interferendo con le query in esecuzione.
No, le operazioni di backup non generano lock sugli oggetti. Le operazioni di backup generano un importante carico di lettura/scrittura su disco e quindi possono rallentare le altre operazioni, generando a volte la sensazione che vi siano dei lock, ma non è così. Ovviamente, proprio per il carico di lavoro aggiuntivo che generano nel sottosistema di I/O, è opportuno schedulare tali attività in orario con poco carico di lavoro.
Cambiare recovery model da FULL a BULK_LOGGED per poi tornare a FULL va a rompere la catena dei log backup.
No, tale switch non genera la rottura della catena dei log backup. Solamente il passaggio a SIMPLE recovery model porta alla rottura della catena dei log backup.
Per ripristinare la catena dei log backup è necessario eseguire un full backup.
Tale affermazione è vera solo in parte. In realtà è possibile far ripartire la catena dei log backup a partire da un full backup o da un backup di tipo differenziale.
Un backup full senza l’opzione Copy Only rompe la catena dei log backup.
Un backup full o differenziale svuota il file di log.
No, un backup full o differenziale non svuota il transaction log. Quando si è in FULL recovery Model o in BULK_LOGGED recovery model, solo un log backup può svuotare il transaction log.
I backup, per essere eseguiti, leggono i dati da disco e li portano nel buffer pool di SQL Server.
No, il processo di backup apre un canale specifico verso i datafiles per evitare il sovraccarico di performance che comporterebbe leggere i dati da disco, portarli nel buffer pool di SQL Server ed infine riportarli verso filesystem (il file di backup di destinazione). Se così fosse, immaginate l’impatto che avrebbe un’operazione di backup nel buffer pool di SQL Server: molte pagine di dati presenti in quel momento nel buffer pool verrebbero rimosse con conseguente impatto lato memoria ed esecuzione delle query.
Se il backup ha funzionato, sicuramente funziona anche il restore.
Assolutamente no, sono due operazioni distinte di cui la prima non garantisce la seconda ed è per questo motivo che, nelle buone pratiche di manutenzione, sarebbe opportuno prevedere sempre un test periodico di restore dei backup fatti al fine da garantire la certezza di ripristino in caso di emergenza. Purtroppo, questa attività di test dei restore viene fatta poche volte, se non mai, in moltissime realtà.
Un log backup avrà la dimensione del transaction log al momento del backup.
Non per forza. Il transaction log deve contenere tutte le informazioni necessarie per poter gestire anche il rollback di transazioni attualmente attive, di conseguenza, normalmente il log backup è più piccolo rispetto alla dimensione del transaction log. Il log backup contiene tutti i log generati a partire dall’ultimo log backup eseguito.
Un log backup sicuramente svuota il transaction log.
Un log backup tenterà sempre di svuotare il transaction log. In realtà il log backup può colpire solo la parte inattiva del transaction log. Se ci sono transazioni attive o altri processi che mantengono dei lock su delle porzioni di transaction log, tali dati non vengono svuotati dal log backup (ma molto probabilmente verranno svuotati dai successivi log backup che verranno fatti).
I backup differenziali sono incrementali.
No, i backup differenziali rappresentano il backup dei cambiamenti avvenuti rispetto all’ultimo full backup, quindi sono cumulativi, non certamente incrementali. I log backup invece sono incrementali (backup di tutti i log dall’ultimo log backup).
Una volta completato un backup full posso tranquillamente cancellare i vecchi backup full.
Assolutamente no. Come già detto un backup non garantisce che il suo restore possa avvenire con successo. Inoltre, cosa succederebbe se ci accorgiamo di aver fatto un backup di un database corrotto? Avere altri backup del passato ci può garantire di trovare una versione non corrotta da cui ripristinare alcuni oggetti se non l’intero database. A questo proposito, ci tengo a precisare l’importanza di fare un regolare e schedulato controllo di integrità dei database, proprio per garantire di non trovarsi con un backup di database corrotto.
Una buona prassi è mantenere lo storico dei backup tale da coprire almeno la fascia temporale che va da un controllo di integrità avvenuto con successo ed il successivo. Solo in questo modo possiamo avere la garanzia di avere almeno un backup integro e non corrotto.
È possibile fare il backup (o il restore) di una sola tabella.
È consigliato fare uno shrink del database prima di un backup per ridurre le dimensioni del file di backup.
No, lo shrink cambia solo l’ordine delle pagine dentro il datafile e quindi non farebbe alcuna differenza. E lo spazio vuoto dentro un datafile non viene backuppato. Inoltre, sappiamo che lo shrink in genere può peggiorare la frammentazione degli indici.
In aggiunta uno shrink fatto dopo un backup di tipo full, avrebbe la conseguenza di rendere il successivo backup di tipo differenziale (nel caso fossero previsti i differenziali) di dimensioni molto grandi pur non essendoci stato un effettivo cambiamento a livello di dati. In conclusione, quindi, si consiglia di non fare mai degli shrink se non per delle motivazioni più che valide.
Non è necessario fare il backup dei database di sistema.
Errato. È assolutamente buona norma eseguire il backup dei database di sistema per garantire un ripristino effettivo di un’intera istanza se il server venisse perso. Il database master contiene informazioni relative alla security, il database msdb contiene informazioni su agent jobs, SSIS packages, backup history mentre il database model contiene i modelli e le configurazioni per i nuovi database. Sono database in genere piccoli e quindi non pesano sulle dimensioni totali dei backup.
Backup nativi SQL Server e backup di terze parti non possono convivere assieme.
Falso, entrambi i sistemi possono convivere ma è necessario porre alcune attenzioni.
I backup nativi SQL Server diciamo che rappresentano i backup per antonomasia e nel limite del possibile consiglio sempre che vi siano dei backup nativi.
Esistono in commercio molti software che vengono utilizzati per fare le snapshot delle macchine virtuali e che permettono anche di gestire i backup di SQL Server tramite il servizio di Volume Shadow Copy e SQL writer. Questi software hanno il vantaggio di essere molto veloci nell’eseguire tali operazioni e facilmente gestibili anche da sistemisti che non hanno competenze specifiche in ambito database. Se però vi è in essere un piano di backup nativo SQL Server è necessario porre due attenzioni nel configurare questi strumenti:
- I backup di tipo full devono essere fatti con l’opzione copy only (nel caso in cui il piano di backup nativo SQL Server preveda i backup di tipo differenziale).
- I log backup se vengono fatti nativamente lato SQL Server è opportuno escluderli dal software di terze parti, onde evitare che venga rotta la catena dei backup.
