Avere un buon piano di backup e restore è una parte importante di una strategia di disaster recovery. Questo articolo descrive 3 differenti approcci/soluzioni per creare un backup pianificato in SQL Server.
Come parte di una strategia di backup diversi tipi possono essere usati insieme.
Tipi di backup
- Backup completo. Comprende tutti gli oggetti del database, tabelle di sistema, dati e transazioni che si verificano durante il backup. Un backup completo permette di eseguire un restore totale allo stato precedente il backup stesso.
- Backup differenziale. Contiene i dati che sono cambiati dall’ultimo backup completo e le transazioni eseguite durante il processo di backup. Un backup differenziale è utilizzato congiuntamente all’ultimo backup completo. Dopo aver effettuato un backup differenziale basato sull’ultimo backup completo, tutti i precedenti backup differenziali diventano obsoleti.
- Backup del transaction log. Registra tutte le transazioni che si sono verificate su un database dall’ultimo backup del transaction log e poi tronca il transaction log on line. Il backup del transaction log garantisce il ripristino del database ad uno specifico istante, per esempio al momento prima di una perdita di dati.
- Il backup di file e filegroups è particolarmente indicato per il backup di database molto grandi. Il backup di un file conterrà tutti i dati in uno o più file o filegroups. Il backup del transaction log deve anche essere eseguito per estendere tutti i backup dei file dall’inizio alla fine quando si usa un file backup per fare il restore di un database.
- Backup di sola copia; sono maggiormente utilizzati quando è necessario fare la copia di un database senza interferire sui processi di backup e restore per uno specifico database. La funzionalità di sola copia è la stessa di un backup completo con la differenza che il backup del transaction log farà la copia di tutte le transazioni eseguite dopo l’ultimo backup completo ignorando l’esistenza del backup di sola copia; perciò questo tipo di backup non può essere usato come base per i backup differenziali o dei transaction log.
Strategie di backup raccomandate
Ubicazione del backup
È consigliabile che i backup non siano salvati nella stessa sede (disco fisico) dove sono archiviati i file del database. Nel caso di errore su un disco fisico, si può utilizzare un altro supporto o una struttura di rete per eseguire il restore. Se l’ubicazione del file non è specificata quando si crea un database, SQL Server archivierà i file di database nella destinazione di default.
Notare che modificare l’ubicazione di default non avrà effetto di spostare i dati attuali ed i file di log nella nuova destinazione. La modifica si applicherà soltanto ai database creati dopo la aver fatto la variazione.
Backup pianificati ed automatizzati
Per avere dei backup sicuri ed affidabili è necessario impostare (automatizzare) il processo di backup attraverso strumenti di pianificazione. Creare una pianificazione dei backup è importante, poiché più passa il tempo più il backup diventa obsoleto e datato.
Proteggiti e stai certo di avere sempre a portata di mano un mezzo per ripristinare i tuoi dati fino al punto in cui il database si è danneggiato. Un backup pianificato fornisce anche un’accurata storia dei dati.
La frequenza di un backup dipende dalle esigenze dell’azienda, etc. ed è definita da un Recovery Point Objective (RPO). Per esempio, se i livelli di servizio di un’organizzazione (SLA – Service Leveel Agreement) prevedono che non si possa perdere più di un’ora di dati, il RPO è di un’ora.
Testare i backup
Una strategia di backup e recovery non può essere completa fino a quando i backup non sono ripristinati correttamente su un server di test e si è verificato che il backup può essere ripreso per soddisfare a tutti i requisiti e condizioni previste, comprese tutte le combinazioni che la strategia di recovery prevede. Ci sono una varietà di fattori da considerare per esempio: i requisiti dell’organizzazione a proposito di uso dei dati, protezione, ecc.
Verifica dei backup
Verificare un backup ci garantisce che è stato creato correttamente, intatto fisicamente, che tutti i file del backup siano leggibili e che possano essere ripristinati nel caso in cui un utente ne abbia bisogno e che tutte le transazioni siano consistenti. È importante capire che verificare un backup non verifica la struttura dei dati nel backup. Tuttavia, se il backup è stato creato usando WITH CHECKSUMS, questa verifica può fornire una buona indicazione dell’affidabilità dei dati del backup.
Utilizzando T-SQL:
Includere l’istruzione CHECKSUM garantisce la consistenza sui dati sul backup di destinazione. Per includere CHECKSUM usa la seguente query:
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AW12.bak' WITH CHECKSUM;
SQL Server Management Studio fornisce anche le opzioni per includere il controllo CHECKSUM nella verifica del backup quando si crea un backup come task:
Le opzioni “Verify backup when finished” e “Perform checksum before writing to media” sono utilizzate come assicurazione che sia il backup che i dati siano consistenti.
Ti mostreremo anche come includere le verifiche quando si schedulano i backup
In questo articolo creeremo un backup pianificato di SQL Server utilizzando un job di SQL Server Agent e SQL Server Maintenance Plans
Creare un backup pianificato usando un job di SQL Server Agent
Per automatizzare e pianificare un backup con SQL Server Agent:
-
Nel pannello Object Explorer, sotto il nodo SQL Server Agent, clicca con il desto su Jobs seleziona nuovo job dal menu contestuale:
- Nella nuova finestra di dialogo inserisci il nome del job
- Nel tab Steps clicca su New e crea uno step di backup inserendo un’istruzione T-SQL. In questo caso la clausola CHECKSUM deve essere inclusa nel codice T-SQL
USE AdventureWorks2012 GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AW12.bak' WITH CHECKSUM;
Per creare un backup differenziale usa il seguente script T-SQL:
USE AdventureWorks2012 GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AW12.bak' WITH CHECKSUM; BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AWD12.bak' WITH DIFFERENTIAL; WITH CHECKSUM; GO
TPer fare il backup del transaction log usa il seguente script:
BACKUP LOG [AdventureWorks2012] TO DISK = N'F:\Logs\AWD12.log'; GO
Nota: Per creare un backup differenziale o del transaction log un backup completo deve esistere. Se per il database desiderato non è mai stato fatto un backup, prima di farne uno differenziale, deve essere creato un backup completo. Backup differenziali e del transaction log possono essere utilizzati a fianco di uno completo. Per esempio, un backup completo può essere schedulato ogni 24 ore, un backup differenziale può essere eseguito ogni 5 ore ed un backup del transaction log ogni 15 minuti.
-
Clicca OK per aggiungere lo step e poi clicca OK per creare il job:
- Per schedulare un job, nella finestra di dialogo, nel tab Schedule clicca New
-
Nello Schedule del job scegli una frequenza di esecuzione e una data di partenza, quindi premi OK:
Per controllare il job nel pannello Object Explorer sotto il nodo SQL Server Agent > Jobs click con il destro sul job appena creato e seleziona l’opzione “Start job at Step”
Se si vuole usare SQL Server Agent per il fare backup di tutti i database della stessa istanza, ci sono due approcci, che richiedono entrambi del lavoro manuale. Un approccio è di creare un pacchetto SSIS usando l’opzione Backup Database Task dalla barra di menu e di creare un job con SQL Server Agent per schedularlo.
L’altro approccio è di scrivere uno script T-SQL per fare il backup di tutti i database in uno step di un job in SQL Server Agent.
Creare un backup pianificato di SQL Server utilizzando i SQL Server Maintenance Plans
Ci sono due opzioni per creare un backup pianificato utilizzando i SQL Server Maintenance Plans: a mano creando un nuovo piano oppure usando il Maintenance Plan Wizard.
Per creare a mano un backup pianificato:
-
Nel pannello Object Explorer sotto il nodo Management, clicca Maintenance Plans e seleziona l’opzione New Maintenance Plan
-
Dal casella degli strumenti Maintenance Plan Tasks scegli Back Up Database Task:
Il Maintenance Plan Wizard fornisce anche il Check Database Integrity Task che può essere incluso nel Maintenance Plan:
-
Doppio click sul piano aggiunto e imposta le opzioni di backup:
- Per pianificare un job di SQL Server Agent usa l’opzione “Sub plan schedulino”
- Quando un piano è creato, clicca salva e questa azione creerà un corrispondente job nella cartella Jobs di SQL Server Agent
Il metodo per creare un nuovo piano di manutenzione tramite il Maintenance Plan Wizard ti guida attraverso il processo, ma questa scelta prevede poche opzioni di dettaglio.
Per automatizzare e pianificare un backup usando il SQL Server Maintenance Plan Wizard:
- Nel pannello Object Explorer sotto il nodo Management click con il destro su Maintenance Plans e seleziona l’opzione Maintenance Plan Wizard:
-
Nella finestra Select Plan Properties specifica un nome per il piano. Per pianificare un job di SQL Server Agent clicca il bottone Change:
-
Nella finestra Select Maintenance Plan Task seleziona l’opzione “Back Up Database” e la casella “Check data integrity”. Il check data integrity esegue dei controlli interni di consistenza sui dati e sulle pagine di indice interne al database:
-
Nella finestra seguente configura il task di manutenzione specificando il database per il backup e le opzioni di backup. Nella finestra Define Back Up Database Task imposta anche l’opzione “Verify backup integrity”:
-
Dopo aver verificato le scelte e le azioni clicca Finish
I Maintenance Plans sono più indicati per DBA con poca esperienza perché forniscono una interfaccia grafica facile da usare e non richiedono la scrittura di scripts di manutenzione scritti a mano. Il lato negativo dei Maintenance Plans è che mettono a disposizione pochi compiti molto essenziali e non lasciano spazio per la personalizzazione.
Un Maintenance Plan è anche atomico e perciò non è in grado di eseguire più attività contemporaneamente. Ciascun tipo di attività di manutenzione all’interno di un singolo Maintenance Plan può solamente essere configurato per girare una volta sola all’interno del piano. Per esempio, se un’attività è fatta per eliminare i file di backup più vecchi, cancellerà soltanto un tipo di file alla volta. A causa di questo, molteplici Maintenance Plans devono essere creati proprio per svolgere una singola attività in alcuni casi, ed ogni Maintenance Plan deve avere il suo corrispondente job in SQL Server Agent da creare.
Risorse utili:
Backup Overview (SQL Server)
Maintenance Plans
Schedule a Job
July 25, 2017
