Fai il backup e ripristina i database di SQL Server

Si applica a:SQL Server

Questo articolo descrive i vantaggi del backup dei database SQL Server, introduce i termini base di backup e ripristino, e tratta strategie di backup e ripristino e considerazioni di sicurezza per SQL Server.

La componente di backup e ripristino di SQL Server fornisce una salvaguardia essenziale per i dati critici memorizzati nei tuoi database SQL Server. Per minimizzare il rischio di perdita catastrofica dei dati, fai regolarmente backup dei tuoi database per conservare le modifiche ai dati. Una strategia di backup e ripristino ben pianificata aiuta a proteggere i database dalla perdita di dati causata da molti tipi di guasto. Prova la tua strategia ripristinando una serie di backup e poi recuperando il database, così sarai pronto a rispondere a un disastro.

Oltre allo storage locale, SQL Server supporta anche il backup e il ripristino da Archiviazione BLOB di Azure. Per altre informazioni, vedere Backup e ripristino di SQL Server con Archiviazione BLOB di Azure. Per i file di database archiviati tramite il l’archiviazione BLOB di Microsoft Azure, SQL Server 2016 (13.x) consente di usare gli snapshot di Azure per backup quasi istantanei e operazioni di ripristino più veloci. Per altre informazioni, vedi backup di snapshot dei file per i file di database in Azure. Azure offre anche una soluzione di backup di livello aziendale per SQL Server in esecuzione in macchine virtuali di Azure. Si tratta di una soluzione di backup completamente gestita che supporta gruppi di disponibilità Always On, conservazione a lungo termine, recupero temporizzato e gestione e monitoraggio centralizzati. Per altre informazioni, vedere Informazioni sul backup di SQL Server nelle macchine virtuali di Azure.

Perché è importante eseguire un backup?

• Fare il backup dei tuoi database SQL Server, eseguire procedure di ripristino di test sui tuoi backup e conservare copie dei backup in una posizione sicura e fuori sede ti protegge da potenziali perdite catastrofiche di dati. Il backup è l'unico modo per proteggere i dati.

  Con backup validi di un database, è possibile recuperare i dati a seguito di molti tipi di guasti ed errori, quali:

  • Errore del supporto di memorizzazione.

  • Errori degli utenti, ad esempio l'eliminazione accidentale di una tabella.

  • Errori hardware, ad esempio un'unità disco danneggiata o la perdita definitiva di un server.

  • Calamità naturali. Utilizzando SQL Server Backup to Archiviazione BLOB di Azure, puoi creare un backup off-site in una regione diversa dalla tua posizione on-site, da utilizzare se un disastro naturale dovesse influenzare la tua posizione on-premises.

• I backup di un database risultano inoltre utili per le attività di amministrazione di routine, ad esempio la copia di un database da un server a un altro, l'impostazione di gruppi di disponibilità Always On o del mirroring del database e l'archiviazione.

Glossario dei termini di backup

Strategie di backup e ripristino

Devi personalizzare le strategie di backup e ripristino per il tuo ambiente e le risorse disponibili. Un recupero affidabile richiede una strategia di backup e ripristino. Una strategia ben progettata bilancia i requisiti aziendali per la massima disponibilità e la minima perdita dati con i costi di mantenimento e memorizzazione dei backup.

Tale strategia prevede una parte relativa al backup e una parte relativa al ripristino. La parte di backup definisce il tipo e la frequenza dei backup, il tipo e la velocità dell'hardware necessario, come testare i backup e dove e come archiviare i supporti di backup (incluse considerazioni di sicurezza). La parte di ripristino definisce chi è responsabile di eseguire i ripristini, come effettuarli per raggiungere gli obiettivi di disponibilità del database e perdita minima di dati, e come testare i ripristini.

Una strategia efficace di backup e ripristino richiede una pianificazione attenta, implementazione e test. Il test è obbligatorio. Non hai una strategia di backup finché non ripristini con successo i backup in ogni combinazione inclusa nella strategia di ripristino e testi ogni database restaurato per la coerenza fisica. Considera diversi fattori, tra cui:

• Gli obiettivi dell'organizzazione relativi ai database di produzione, in particolare i requisiti per la disponibilità e la protezione dei dati da perdite o danni.

• Caratteristiche di ogni database, ovvero dimensioni, tipo di utilizzo, tipo di contenuto, requisiti relativi ai dati e così via.

• Vincoli sulle risorse, ad esempio hardware, personale, spazio per l'archiviazione dei supporti di backup, la sicurezza fisica dei supporti archiviati e così via.

Indicazioni sulle procedure consigliate

Non concedere agli account che effettuano operazioni di backup o ripristino più privilegi del necessario. Per maggiori informazioni, consulta backup e ripristino per dettagli specifici sui permessi. Cripta i backup del database e, se possibile, comprimili .

Usa estensioni di file coerenti per rendere più facile identificare e gestire i backup. SQL Server non richiede né impone queste estensioni, ma la coerenza aiuta con compiti operativi come la configurazione delle esclusioni antivirus per i file di backup. Per altre informazioni, vedere Configurare il software antivirus per l'uso con SQL Server.

• I file di backup del database dovrebbero avere l'estensione .BAK .
• I file di backup del log devono avere .TRNl'estensione.

Usa uno spazio di archiviazione separato

Metti i backup del database in una posizione fisica separata o su un dispositivo separato dai file del database. Quando il disco fisico che memorizza i tuoi database si guasta o va in crash, il recupero dipende dalla tua capacità di accedere al disco separato o al dispositivo remoto che ha memorizzato i backup. Puoi creare diversi volumi logici o partizioni dallo stesso disco fisico. Esamina attentamente la partizione del disco e la disposizione dei volumi logici prima di scegliere una posizione di archiviazione per i backup.

Scegliere il modello di recupero appropriato

Le operazioni di backup e di ripristino si verificano nel contesto di un modello di recupero, Un modello di recupero è una proprietà del database che controlla il modo in cui viene gestito il log delle transazioni. Pertanto, il modello di recupero di un database determina quali tipi di scenari di backup e ripristino supporta il database e la dimensione dei backup dei log delle transazioni. In genere, un database usa il modello di recupero semplice o il modello di recupero completo. È possibile integrare il modello di recupero con registrazione completa passando al modello di recupero con registrazione in blocco prima di eseguire operazioni in blocco. Per un'introduzione a questi modelli di recupero e sul modo in cui influiscono sulla gestione dei log delle transazioni, vedere il log delle transazioni.

La scelta migliore del modello di recupero database dipende dalle esigenze aziendali. Per evitare la gestione del log delle transazioni e semplificare le operazioni di backup e ripristino, è possibile utilizzare il modello di recupero con registrazione minima. Per ridurre al minimo il rischio di perdita del lavoro, a fronte di un maggiore carico amministrativo, utilizzare il modello di recupero completo. Per minimizzare l'effetto sulla dimensione del log durante le operazioni bulk-log, consentendo comunque il recupero di tali operazioni, si utilizza il modello di recupero bulk-log. Per informazioni sull'effetto dei modelli di recupero su backup e ripristino, vedi Panoramica del backup (SQL Server).

Progettare la strategia di backup

Dopo aver selezionato un modello di recupero che soddisfi le esigenze aziendali per un database specifico, pianifica e implementa una strategia di backup corrispondente. La migliore strategia di backup dipende da diversi fattori. I seguenti fattori sono particolarmente importanti:

• Quante ore al giorno le applicazioni hanno bisogno per accedere al database?

  Se c'è un periodo prevedibile di non picco, dovresti programmare backup completi del database per quel periodo.

• Frequenza prevista per l'esecuzione di modifiche e aggiornamenti.

  Se i cambiamenti sono frequenti, considera:

  • Con il modello di recupero semplice, puoi programmare backup differenziali tra backup completi del database. Con un backup differenziale è possibile acquisire solo le modifiche successive all'ultimo backup completo del database.

  • Con il modello completo di recupero, puoi programmare frequenti backup dei log. La pianificazione di backup differenziali nei periodi intermedi tra i backup completi consente di ridurre i tempi di ripristino limitando il numero di backup del log da ripristinare in seguito al ripristino dei dati.

• È probabile che si verifichino cambiamenti solo in una piccola parte del database, o in una parte ampia?

  Per un grande database in cui le modifiche sono concentrate in un sottoinsieme di file o gruppi di file, possono essere utili backup parziali o completi di file. Per altre informazioni, vedere Backup parziali (SQL Server) e Backup completi dei file (SQL Server).For more information, see Partial Backups (SQL Server) and Full File Backups (SQL Server).

• Quanto spazio su disco richiede un backup completo di un database?

• Per quanto tempo devono essere conservati i backup dell'azienda?

  Assicurati di avere un programma di backup adeguato che si adatti alle esigenze dell'applicazione e alle esigenze aziendali. Man mano che i backup invecchiano, il rischio di perdita dei dati aumenta, a meno che non si disponga di un metodo per ricostruire tutti i dati fino al momento del guasto. Prima di eliminare i vecchi backup per limiti di archiviazione, valuta se hai bisogno di poter effettuare un ripristino che risalga così indietro nel tempo.

Stimare le dimensioni di un backup completo del database

Prima di implementare una strategia di backup e ripristino, stima quanto spazio su disco consuma un backup completo del database. Con l'operazione di backup i dati contenuti nel database vengono copiati nel file di backup. Il backup contiene solo i dati effettivi nel database, non spazi inutilizzati. le dimensioni del backup risultano di solito inferiori a quelle del database originale. Per stimare la dimensione di un backup completo del database, si utilizza la sp_spaceused procedura di archiviazione del sistema. Per altre informazioni, consultare ssp_spaceused.

Pianificare le operazioni di backup

Un'operazione di backup ha un effetto minimo sulle transazioni in esecuzione, quindi puoi eseguire backup durante le operazioni normali. È possibile eseguire un backup di SQL Server con un effetto minimo sui carichi di lavoro di produzione.

Dopo aver deciso quali tipi di backup ti servono e con quale frequenza eseguire ciascuno tipo, programma backup regolari come parte di un piano di manutenzione del database. Per informazioni sui piani di manutenzione e su come crearli per i backup di database e di log, vedere Use the Maintenance Plan Wizard.

Testa i backup

Non è disponibile una strategia di ripristino fino a quando non si testano i backup. Testa accuratamente la tua strategia di backup per ogni database ripristinando una copia del database su un sistema di test. È necessario testare il ripristino di tutti i tipi di backup che si desidera utilizzare. Dopo aver ripristinato il backup, verifica DBCC CHECKDB il database per verificare che il supporto di backup non sia danneggiato.

Verificare la stabilità e la coerenza dei supporti

Usa le opzioni di verifica fornite dalle utility di backup (BACKUPcomando T-SQL, SQL Server Maintenance Plans, il tuo software o soluzione di backup, e così via). Per un esempio, vedi RESTORE Enunciati - VERIFYONLY.

Usa funzionalità avanzate come BACKUP CHECKSUM per rilevare problemi nel supporto di backup stesso. Per altre informazioni, vedere Possible Media Errors During Backup and Restore (SQL Server).

Strategia di backup/ripristino dei documenti

Documenta le procedure di backup e di ripristino e conserva una copia della documentazione nel runbook.

Dovresti anche mantenere un manuale operativo per ogni database. Questo manuale operativo dovrebbe documentare la posizione dei backup, i nomi dei dispositivi di backup (se presenti) e il tempo necessario per ripristinare i backup di test.

Rischio di sicurezza per il ripristino dei backup da origini non attendibili

Questa sezione descrive il rischio di sicurezza associato al ripristino di backup da origini non attendibili a qualsiasi ambiente SQL Server, tra cui locale, Istanza gestita di SQL di Azure, SQL Server in macchine virtuali di Azure e qualsiasi altro ambiente.

Perché è importante

Il ripristino dei file di backup SQL (.bak) comporta un potenziale rischio se il backup proviene da un'origine non attendibile. Il rischio di sicurezza è ulteriormente esacerbato quando un ambiente SQL Server ha più istanze, in quanto amplifica l'area di minaccia. Mentre i backup che rimangono entro un limite attendibile non rappresentano problemi di sicurezza, il ripristino di un backup dannoso può compromettere la sicurezza dell'intero ambiente.

Un file dannoso .bak può:

• Acquisire la proprietà dell'intera istanza di SQL Server.
• Escalare i privilegi e ottenere l'accesso non autorizzato all'host o alla macchina virtuale di base.

Questo attacco si verifica prima che qualsiasi script di convalida o controlli di sicurezza possa essere eseguito, il che rende estremamente pericoloso. Il ripristino di un backup non attendibile equivale all'esecuzione di applicazioni non attendibili in un server o in una macchina virtuale critica e l'introduzione di un'esecuzione arbitraria del codice nell'ambiente.

Procedure consigliate

Seguire queste procedure consigliate per la sicurezza dei backup per ridurre la minaccia agli ambienti di SQL Server:

• Considerare il ripristino dei backup come operazione ad alto rischio.
• Ridurre la superficie di minaccia utilizzando istanze isolate.
• Consenti solo backup attendibili: non ripristinare mai i backup da origini sconosciute o esterne.
• Consentire solo i backup rimasti entro un limite attendibile: assicurarsi che i backup provengano dall'interno del limite attendibile.
• Non ignorare i controlli di sicurezza per praticità.
• Abilitare il controllo a livello di server per acquisire eventi di backup e ripristino e attenuare l'evasione dei controlli.

Monitorare lo stato di avanzamento con XEvent

Le operazioni di backup e ripristino possono richiedere molto tempo a causa delle dimensioni del database e della complessità delle operazioni coinvolte. Quando sorgono problemi con una delle due operazioni, usa l'evento backup_restore_progress_trace esteso per monitorare i progressi in diretta. Per altre informazioni sugli eventi estesi, vedere Panoramica degli eventi estesi.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Esempio di output da Extended Event

Altre informazioni sulle operazioni di backup

• Creare un piano di manutenzione
• Creare un processo di SQL Server Agent in SQL Server Management Studio
• Configura la programmazione per l'attività di SQL Server Agent

Usare dispositivi di backup e supporti di backup

• Definire un dispositivo di backup logico per un file su disco (SQL Server)
• Definire un dispositivo di backup logico per un'unità nastro (SQL Server)
• Specificare una destinazione di backup su disco o nastro (SQL Server)
• Eliminare un dispositivo di backup (SQL Server)
• Impostare la data di scadenza in un backup (SQL Server)
• Visualizzare il contenuto di un nastro o di un file di backup (SQL Server)
• Visualizzare i file di dati e di log in un set di backup (SQL Server)
• Visualizzare le proprietà e il contenuto di un dispositivo di backup logico (SQL Server)
• Ripristinare un backup da un dispositivo (SQL Server)

Creare backup

Per i backup parziali o di sola copia, usa l'istruzione Transact-SQL BACKUP con l'opzione PARTIAL o COPY_ONLY, rispettivamente.

Usa SSMS

• Creare un backup completo del database
• Eseguire il backup di un log delle transazioni
• File di backup e gruppi file
• Creare un backup differenziale del database (SQL Server)

Usare T-SQL

• Usa Resource Governor per limitare l'uso della CPU tramite la compressione di backup
• Eseguire il backup del log delle transazioni quando il database è danneggiato (SQL Server)
• Abilitare o disabilitare i checksum di backup durante il backup o il ripristino (SQL Server)
• Specificare il backup o il ripristino per continuare o arrestare dopo l'errore

Ripristinare backup di dati

Usa SSMS

• Ripristinare un backup del database con SSMS
• Ripristinare un database in una nuova posizione (SQL Server)
• Ripristinare un backup differenziale del database (SQL Server)
• Ripristinare file e gruppi di file (SQL Server)

Usare T-SQL

• Ripristina un backup del database con il modello di recupero semplice
• Ripristino del database fino al punto del guasto - ripristino completo
• Ripristina file e gruppi di file su file esistenti (SQL Server)
• Ripristina i file in una nuova posizione (SQL Server)
• Ripristina il database master

Ripristinare i log delle transazioni (modello di recupero con registrazione completa)

Usa SSMS

• Ripristinare un database in una transazione contrassegnata (SQL Server Management Studio)
• Ripristinare un backup del log delle transazioni (SQL Server)
• Ripristina un database SQL Server a un certo punto temporale (modello completo di recupero)

Usare T-SQL

• Ripristina un database SQL Server a un certo punto temporale (modello completo di recupero)
• Riavviare un'operazione di ripristino interrotta
• Recupera un database senza ripristinare dati

Contenuti correlati

• Panoramica del backup (SQL Server)
• Panoramica del ripristino e del recupero (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Istruzioni (Transact-SQL)
• Backup e ripristino dei database di Analysis Services
• Fai il backup e ripristina i cataloghi e gli indici a testo completo
• Eseguire il backup e ripristinare i database replicati
• Log delle transazioni
• Modelli di recupero (SQL Server)
• Set di supporti, gruppi di supporti e set di backup (SQL Server)