Nel capitolo di apertura del libro di Craig Mullin, Database Administration, dice “In molti modi, il business oggi è dati”. All’interno della maggior parte delle organizzazioni la persona responsabile della protezione dei dati è l’amministratore del database you you.

Esatto; l’intera attività è nelle tue mani capaci, in esecuzione su quel server che non si blocca mai, con tutti quegli utenti finali che non commettono mai errori usando le applicazioni, costruiti da quegli sviluppatori che scrivono codice impeccabile la prima volta, ogni volta, con l’assistenza capace di quella nuova co-op che ha privilegi ‘sa’ grazie al tuo capo.

OK. Smettila di piangere. Ci sono cose che puoi fare per proteggere i dati di SQL Server sotto la tua cura e uno dei più importanti è l’esecuzione di backup regolari del database.

Backup

Microsoft, in SQL Server Books Online, definisce i backup come:

Una copia dei dati utilizzata per ripristinare e recuperare i dati dopo un errore di sistema

I backup SQL possono essere creati in diversi modi e possono incorporare tutti o alcuni dati, nonché parte del log delle transazioni. Mentre questo articolo è incentrato sulla sintassi del 2005, la maggior parte dei concetti sono applicabili al 2000. Questo è un argomento enorme. Nella migliore delle ipotesi, ho intenzione di graffiare la superficie e darvi abbastanza informazioni in modo da non iniziare a piangere di nuovo. Dopo aver letto questo, dovresti essere in grado di impostare un set ragionevole di backup per il tuo sistema.

Modelli di ripristino

Per iniziare a lavorare sui backup, l’azienda deve definire un modello di ripristino del database. In sostanza, un modello di ripristino definisce cosa si intende fare con i dati del registro delle transazioni.

Esistono tre modelli di recupero: Full, Simple e Bulk Logged. Questi sono abbastanza facili da definire:

• Semplice – in modalità di ripristino semplice, il registro delle transazioni non viene eseguito il backup in modo da poter ripristinare solo il backup completo o differenziale più recente.
• Full-in modalità di ripristino completo si esegue il backup del database e del log delle transazioni in modo da poter recuperare il database in qualsiasi momento.
• Bulk Logged – in modalità bulk logged, la maggior parte delle transazioni vengono memorizzate nel log delle transazioni, ma alcune operazioni di massa come i carichi di massa o la creazione di indici non vengono registrate.

Le due modalità più comunemente utilizzate sono Semplici e complete. Non necessariamente dare per scontato che, naturalmente, è sempre necessario utilizzare il recupero completo per proteggere i dati. Si tratta di una decisione di business. L’azienda sta per dirvi se avete bisogno di recuperare ad un punto nel tempo o se avete semplicemente bisogno l’ultimo backup completo. Definirà se i tuoi dati sono recuperabili con altri mezzi, come l’inserimento manuale, o se devi proteggere il più possibile mentre attraversa il filo. Si utilizza il ripristino semplice se si può permettere di perdere i dati memorizzati dall’ultimo backup completo o differenziale e/o semplicemente non è necessario il ripristino di un punto nel tempo. In modalità Semplice, è necessario ripristinare tutti i gruppi di file di lettura/scrittura secondari quando si ripristina il primario. Si utilizza Simple principalmente su database secondari che non sono una parte vitale assoluta dell’azienda o dei sistemi di reporting, con accesso in sola lettura, quindi non c’è comunque un log delle transazioni di cui preoccuparsi. Si utilizza Full se ogni bit dei dati è vitale, è necessario recuperare ad un punto nel tempo o, di solito nel caso di database molto grandi (VLDB), è necessario ripristinare singoli file e gruppi di file indipendentemente da altri file e gruppi di file.

Con modelli di ripristino sia semplici che completi, è ora possibile eseguire un backup di sola copia che consente di copiare il database in un file di backup, ma non influisce sul registro, sulle pianificazioni di backup differenziali o sul ripristino dell’impatto in un momento. Cercherò di approfondire il maggior numero possibile di questi argomenti attraverso l’articolo, ma non i file e i filegroup.

Lavorare con il ripristino semplice

Basta parlare. Passiamo ai backup in esecuzione. Supponiamo che siamo in semplice recupero su un database di piccole e medie dimensioni. Userò AdventureWorks per tutti gli script di esempio. Per l’impostazione di recupero:

semplice strategia di backup è quello di eseguire, a intervalli regolari, i seguenti backup di SQL Server di comando, che consentono di eseguire un backup completo del database:

Che cos’è tutta la digitazione che chiedi? Non abbiamo strumenti GUI per gestire il lavoro per noi? Sì, i backup più semplici possono essere eseguiti utilizzando SQL Server Management Studio. Tuttavia, se vuoi imparare e capire cosa Management Studio sta facendo per te, o se vuoi un controllo a grana fine su ciò che viene eseguito il backup, come e dove, allora dovrai rompere la tastiera e mettere via il mouse.

Il comando precedente farà precipitare un backup di base su disco. La maggior parte dei DBA che conosco esegue il backup su file e quindi raschiare i file su un nastro o su altri supporti. Questo perché i file su disco sono semplici e veloci da recuperare, mentre i media a volte possono essere un po ‘ dolorosi. Per esempio, abbiamo generalmente due o tre giorni vale la pena di backup sui nostri file system per il ripristino immediato. Andiamo ai sistemi a nastro solo se abbiamo bisogno di eseguire ripristini per i backup più vecchi.

Che cosa ha fatto quel comando? Ha fatto una copia di tutti i dati impegnati nel database. Ha anche copiato le voci di registro non committed. Questi vengono utilizzati durante il ripristino per eseguire il commit o il rollback delle modifiche apportate ai dati durante il processo di backup.

Backup di sola copia

Normalmente, il backup di un database influisce su altri processi di backup e ripristino. Ad esempio, dopo aver eseguito il comando precedente, qualsiasi backup differenziale (un backup che copia solo i dati modificati dall’ultimo backup) utilizzerebbe questo come punto di partenza per le modifiche ai dati, non il backup eseguito la scorsa notte. Come osservato in precedenza, SQL 2005 introduce un nuovo concetto di backup, COPY_ONLY backup, che ci permettono di mantenere interrompere il ciclo:

Abbiamo già trovato uno di quei momenti più granulari in cui lo Studio di gestione non ti avrebbe aiutato. Se si desidera una copia di backup solo, è necessario utilizzare la riga di comando.

Backup differenziali

Supponiamo per un momento, che siamo ancora in semplice recupero, ma abbiamo a che fare con un database più grande, diciamo qualcosa di sopra di 100 GB di dimensione. I backup completi possono effettivamente iniziare a rallentare un po ‘ il processo. Invece, dopo aver consultato l’azienda, abbiamo deciso di eseguire un backup completo settimanale e backup differenziali giornalieri. Backup differenziali eseguire il backup solo delle pagine di dati modificate dall’ultimo backup completo. Di seguito è riportato il comando SQL backup per eseguire un backup differenziale:

Ora, se dovessimo ripristinare questo database, andremo prima all’ultimo backup completo, ripristinarlo e quindi ripristinare i backup differenziali in ordine (ne parleremo più avanti).

Ci sono una serie di altre opzioni di backup che non sarò dettagliare qui. Leggi i libri online per vedere i dettagli su BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NOME, STATISTICHE e così via.

È anche possibile eseguire un’istruzione che verificherà l’integrità di un backup del database. Non controlla l’integrità dei dati all’interno di un backup, ma verifica che il backup sia formattato correttamente e accessibile.

Ripristino completo e backup dei log

Abbiamo lavorato principalmente su un database che era in modalità di ripristino semplice (questo era chiamato Truncate Log on Checkpoint). In questa modalità, non eseguiamo il backup dei log delle transazioni per il ripristino successivo. Ogni backup sotto questo meccanismo è un backup del database. I backup dei log non sono semplicemente possibili.

Tuttavia, hai protetto i dati solo a partire dall’ultimo backup valido, completo o differenziale. Cambiamo le nostre ipotesi. Ora abbiamo a che fare con una grande applicazione e database mission critical. Vogliamo essere in grado di recuperare questo database fino all’ultimo minuto. Questo è un punto molto importante. In teoria, poiché le voci di registro vengono memorizzate e sottoposte a backup, siamo protetti fino al punto di qualsiasi errore. Tuttavia, alcuni errori possono causare il danneggiamento del registro, rendendo impossibile il ripristino fino a un certo punto nel tempo. Quindi, dobbiamo determinare quale sarà il tempo minimo ragionevole tra i backup del registro. In questo caso possiamo vivere con non più di 15 minuti vale la pena di dati persi.

Quindi, iniziamo mettendo il nostro database in modalità di ripristino COMPLETO:

Quindi, in base a una pianificazione, in questo caso, ogni 15 minuti, ti eseguire il backup di SQL comando per il log delle transazioni:

Questo script eseguirà il backup delle transazioni impegnate dal log delle transazioni. Ha marcatori nel file che mostrano l’ora di avvio e di arresto. Troncerà il registro quando viene completato correttamente, eliminando dal registro delle transazioni le transazioni impegnate che sono state scritte nel file di backup. Se necessario, è possibile utilizzare l’istruzione WITH NO_TRUNCATE per acquisire i dati dal log delle transazioni indipendentemente dallo stato del database, supponendo che sia online e non in stato di EMERGENZA. Questo è solo per le emergenze.

Si noti che non stiamo utilizzando l’istruzione INIT in questo caso, ma è possibile farlo se si sceglie. Quando si eseguono i backup dei registri, sono disponibili opzioni:

1. Esegui tutti i backup su un singolo file, dove si impileranno e tutto ciò che devi fare, su restore (coperto in seguito), è scorrere attraverso di essi.
2. Assegna un nome univoco ai backup, probabilmente usando data e ora nella stringa.

In quest’ultimo caso, safety dice, usa INIT perché stai esercitando il massimo controllo su ciò che viene eseguito il backup dove, e sarai in grado di sapere esattamente cos’è un backup, quando è stato preso e da dove in base al nome. Questo è un altro posto in cui i backup operativi dalla riga di comando ti danno più controllo della GUI. Abbiamo utilizzato entrambi gli approcci nei nostri sistemi per diversi motivi. Puoi decidere cosa è meglio per le tue esigenze tecnologiche e aziendali.

La maggior parte delle opzioni disponibili per il backup del database sono incluse in Log backup, incluso COPY_ONLY. Ciò consente di acquisire una serie di dati di transazione senza influire sul registro o sul successivo backup del registro pianificato. Questo sarebbe utile per portare i dati di produzione su un altro sistema per la risoluzione dei problemi, ecc.

Se il database è impostato su Ripristino COMPLETO, è necessario eseguire i backup dei registri. A volte, le persone dimenticano e il registro delle transazioni cresce al punto che riempie l’unità disco. In questo caso, puoi eseguire:

il Collegamento NO_LOG per il backup del log in, e il fatto di non specificare una posizione per il file di registro, cause la parte inattiva del registro per essere rimosso e questo senza una voce di registro, quindi sconfiggere l’intero disco rigido. Questo è assolutamente sconsigliato perché interrompe la catena di log, la serie di backup di log da cui si ripristinerebbe il database in un punto nel tempo. Microsoft consiglia di eseguire un backup completo immediatamente dopo l’utilizzo di questa istruzione. Inoltre, stanno avvertendo che questa affermazione potrebbe essere deprecata in una versione futura.

Ripristino dei database

Per quanto importanti siano i backup di SQL Server e siano vitali, sono inutili senza la possibilità di ripristinare il database.

Ripristino di un backup completo del database

Il ripristino di un backup completo del database è semplice come lo era creare:

E ‘ davvero così semplice-a meno che, come noi stiamo il backup di tutto in un file come se si trattasse di un dispositivo di backup. In tal caso, dovrai specificare quale file all’interno del “dispositivo” a cui stai accedendo. Se non sai quale file, dovrai generare un elenco:

Questo ti darà la stessa lista come ho mostrato sopra da Management Studio. Quindi ora, se volessimo ripristinare il secondo file nel gruppo, il backup COPY_ONLY, dovresti emettere il seguente comando:

Sfortunatamente, se stai seguendo, potresti scoprire che hai appena generato questo errore:

Ciò significa che il tuo database è in modalità di ripristino completo, ma non hai eseguito il backup della “coda del registro”, ovvero delle transazioni immesse dall’ultima volta che hai eseguito un backup. È possibile ignorare questo requisito se si modifica la sintassi precedente in:

Questa è la prima volta che abbiamo accatastato le clausole WITH (CON FILE=2 e CON REPLACE è rappresentato come CON FILE=2, REPLACE), ma non sarà l’ultimo. Leggi i libri online. La maggior parte delle istruzioni CON clausola può essere utilizzata in combinazione con le altre.

Cosa succede se vogliamo ripristinare un database diverso da quello originale? Ad esempio, vogliamo fare una copia del nostro database da un backup separato. Forse vogliamo spostarlo su un server di supporto alla produzione in cui lavoreremo su di esso, separato dalla copia di produzione del database. Se prendiamo l’approccio semplice, beh, prova questo:

In questo caso, dovresti vedere tutta una serie di errori relativi ai file che non vengono sovrascritti. È davvero possibile creare nuovi database dai backup, ma se lo si sta facendo su un server con il database esistente, è necessario modificare la posizione dei file fisici utilizzando i nomi logici. Per conoscere i nomi logici dei file per un determinato database, eseguire questa operazione prima di tentare di spostare i file:

Questo può quindi essere utilizzato per identificare i nomi logici per generare questo script:

Ripristino di un backup differenziale

L’ultimo metodo consiste nell’applicare il backup differenziale. Ciò richiede due passaggi. Per prima cosa, ripristineremo il database, ma con una torsione e poi applicheremo il backup differenziale:

La maggior parte di questo è probabilmente auto-esplicativo in base a ciò che abbiamo già trattato. L’unica ruga è l’inclusione della parola chiave NORECOVERY. Molto semplicemente, durante un ripristino, le transazioni potrebbero essere iniziate durante il processo di backup. Al termine di un ripristino, le transazioni completate vengono inoltrate nel database e le transazioni incomplete vengono ripristinate. L’impostazione di NORECOVERY mantiene aperte le transazioni. Ciò consente di prelevare la prossima serie di transazioni dal backup successivo in ordine.

In questo articolo ci occupiamo principalmente di backup e ripristini semplici, ma un ripristino più avanzato nel 2005 consente di ripristinare gruppi di file secondari mentre il database è online. Il suo gruppo di file primario deve essere online durante l’operazione. Questo sarà più utile per sistemi di database molto grandi.

Ripristino dei database di SQL Server in un punto nel tempo

Il ripristino dei log non è molto più difficile del ripristino del database differenziale appena completato. C’è solo un po ‘ più coinvolto nel ripristinare un momento nel tempo. Supponendo che tu stia eseguendo il backup dei registri su un singolo file o dispositivo:

Altrimenti, vai semplicemente a prendere i nomi dei file di cui hai bisogno. Per prima cosa esegui il ripristino del database, avendo cura di lasciarlo in uno stato non recuperato. Seguire questo con una serie di log ripristina ad un punto nel tempo.

Ora quello che abbiamo è un database che corrisponde all’esatta, ultima transazione commessa alle 14:30:29 del 23 ottobre. Ricorda, durante i ripristini multi-step come questo, devi lasciare il database in uno stato di ripristino. Ciò significa aggiungere NORECOVERY a ciascuna istruzione fino a quando non hai completato il processo di ripristino. Se per qualche motivo hai aggiunto NORECOVERY per tutte le tue affermazioni, o semplicemente fermarsi in mezzo, e vorrei riportare il database online, è possibile utilizzare questa istruzione per completare il processo:

Snapshot del database

SQL Server 2005 ha introdotto il concetto di snapshot, ovvero una vista statica di sola lettura di un database. Le istantanee vengono create principalmente per fornire una versione di sola lettura di un database a scopo di reporting. Tuttavia, funzionano in modo simile ai backup. L’unica differenza principale è che tutte le transazioni non committed vengono ripristinate. Non esiste alcuna opzione per il rotolamento in avanti,l’acquisizione di registri, ecc., che i backup forniscono, né sono molto molte risorse di SQL Server usate affatto. Piuttosto, la tecnologia del disco viene utilizzata per creare una copia dei dati. Per questo motivo sono molto più veloci dei backup sia per creare che per ripristinare.

NOTA:
Per maggiori dettagli su SQL 2005 Snapshot, fare riferimento a http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

Un buon uso delle istantanee, oltre alla segnalazione, potrebbe essere quello di crearne una prima della manutenzione dopo aver già rimosso tutti gli utenti attivi (e le loro transazioni) dal sistema. Mentre le istantanee non supportano la volatilità dei backup live, la loro velocità e facilità di recupero sono un ottimo strumento per il recupero rapido da un rollout fallito. Le istantanee sono memorizzate sul server, quindi è necessario assicurarsi di avere spazio di archiviazione adeguato.

La sintassi è diversa, perché non avete il backup di un database; la creazione di un nuovo:

Ora sarà accessibile per l’accesso in sola lettura. Poiché ci occupiamo principalmente dell’utilizzo di questo come meccanismo di backup, includiamo il metodo per ripristinare un database in un’istantanea del database.

Innanzitutto, identificare l’istantanea che si desidera utilizzare. Se ce n’è più di uno su qualsiasi database che si sta per ripristinare, è necessario eliminare tutti tranne quello che si sta utilizzando:

Quindi è possibile ripristinare il database tramite l’esecuzione di un’istruzione di RIPRISTINO (misto metafore, non va bene):

Che è. Sul mio sistema, l’esecuzione delle istantanee del database di Adventureworks ha richiesto 136 ms. Il backup completo ha richiesto 5.670 ms.Il ripristino dello snapshot ha richiesto 905 ms e il ripristino del database ha richiesto 13.382 ms. Incorporando questo in un processo di rollout di produzione potrebbe comportare vantaggi significativi

Ancora una volta, vale la pena notare che ci sono alcuni avvertimenti per l’utilizzo dello snapshot. È necessario disporre di spazio su disco sufficiente per una seconda copia del database. È necessario fare attenzione a gestire le istantanee poiché la maggior parte della sintassi è simile a quella utilizzata dai database stessi. Infine, mentre ci sono istantanee collegate a un database non è possibile eseguire un ripristino da un backup del database di quel database.

Best practice

Il modo in cui si eseguono i backup del database non deve essere una decisione tecnica. Dovrebbe essere dettato dal business. Piccoli sistemi con bassi tassi di transazione e / o sistemi di reporting che vengono caricati regolarmente avranno sempre e solo bisogno di un backup completo del database. Sistemi di medie dimensioni e sistemi di grandi dimensioni diventano dipendenti dal tipo di dati gestiti per determinare quali tipi di backup sono necessari.

Per un sistema di medie dimensioni, un backup giornaliero con backup di log durante il giorno probabilmente risponderebbe alla maggior parte dei requisiti di dati in modo tempestivo.

Per un database di grandi dimensioni l’approccio migliore è quello di combinare i backup per garantire la massima recuperabilità nel tempo minimo. Ad esempio, eseguire un backup completo settimanale. Due volte al giorno durante la settimana, eseguire un backup differenziale. Ogni 10 minuti durante il giorno, eseguire un backup del registro. Questo ti dà un gran numero di meccanismi di recupero.

Per database molto grandi, è necessario eseguire i backup di filegroup e file perché eseguire un backup completo o anche un backup differenziale dell’intero database potrebbe non essere possibile. Un certo numero di funzioni aggiuntive sono disponibili per aiutare in questo settore, ma non andrò in loro qui.

Si dovrebbe prendere il tempo di sviluppare alcuni script per l’esecuzione di backup e ripristini. Una convenzione di denominazione in modo da sapere quale database, da quale server, da quale data, in quale backup e formato specifico sarà molto favorevole alla tua sanità mentale. È necessario definire una posizione comune per i backup, il registro, completo o incrementale. Tutti i responsabili dovrebbero essere addestrati sia in backup e ripristino e risoluzione dei problemi lo stesso. Ci sono molti modi per farlo, ma puoi trovare alcuni suggerimenti nei backup Pop e nei ripristini Pop.

Il vero test consiste nell’eseguire i meccanismi di backup e quindi eseguire un ripristino. Quindi provare un diverso tipo di ripristino, e un altro, e un altro. Essere sicuri che, non solo hai fatto due diligence nel definire come eseguire il backup del sistema, ma che hai fatto il passo in più di garantire che è possibile recuperare tali backup. Se non hai praticato questo e documentato la pratica e poi testato il documento, in effetti, non sei pronto per un disastro.

Riepilogo

I backup all’interno della tua azienda dovrebbero essere come votare a Chicago, presto e spesso. L’impostazione dei backup di base è abbastanza semplice. Aggiunta di backup di registro e differenziali è facile pure. Esplora le opzioni per vedere come aggiungere backup e ripristini di file e gruppi di file per aumentare la velocità dei backup e dei ripristini, entrambi i quali aumenteranno la disponibilità del sistema e il tempo di attivazione. Mantenere uno standard di denominazione comune. Fare attenzione quando si utilizzano istantanee, ma certamente li impiegano. Memorizzare i file in una posizione standard tra i server. Pratica i tuoi recuperi. Infine, per rendere davvero i backup cantare, scaricare una prova gratuita di SQL Backupâ¢di Red Gate. Offre compressione ad alte prestazioni e resilienza di rete per rendere il processo di scrittura o copia di backup su reti traballanti fault-tolerant.