Backup e ripristino di database SQL Server

  • Articolo

Si applica a:SQL Server

In questo articolo vengono descritti i vantaggi dell'esecuzione del backup dei database di SQL Server e illustrati i termini di base del backup e del ripristino. Vengono anche presentate alcune strategie di backup e ripristino per SQL Server e alcune considerazioni relative alla sicurezza per il backup e il ripristino di SQL Server.

Questo articolo illustra i backup di SQL Server. Per i passaggi specifici necessari per eseguire il backup di database SQL Server, vedere Creazione di backup.

Il componente Backup e ripristino di SQL Server garantisce una misura di sicurezza essenziale per proteggere i dati critici archiviati nei database di SQL Server. Per ridurre al minimo il rischio di perdita irreparabile dei dati, è necessario eseguire il backup dei database a intervalli regolari per preservare le modifiche ai dati. Una strategia di backup e ripristino ben pianificata aiuta a proteggere i database dalla perdita di dati dovuta a vari errori. Testare la strategia ripristinando un set di backup e poi recuperando il database per prepararsi a rispondere in modo efficace a una situazione di emergenza.

Oltre alle risorse di archiviazione locale per l'archiviazione di backup, SQL Server supporta anche il backup e il ripristino dall’archiviazione BLOB di Azure. Per altre informazioni, vedere Backup e ripristino di SQL Server con l’archiviazione BLOB di Microsoft 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, vedere Backup di snapshot di 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 Backup di Azure per SQL Server in macchine virtuali di Azure.

Perché è importante eseguire un backup?

  • L'esecuzione di backup dei database si SQL Server, l'esecuzione di procedure di ripristino di test sui backup e l'archiviazione delle copie di backup in una posizione esterna sicura costituiscono modi validi per evitare una perdita di dati potenzialmente irreversibile. 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.
    • 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. Tramite il backup di SQL Server nell’archiviazione BLOB di Azure, è possibile creare un backup esterno in un'area diversa dalla posizione locale da usare in caso si venga colpiti da una calamità naturale.

  • 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

eseguire il backup
Il processo di creazione di un backup [sostantivo] copiando i record dei dati da un database di SQL Server o i record dei log dal relativo log delle transazioni.

backup
Copia dei dati di utilizzabile per il recupero e il ripristino dei dati in seguito a errori. I backup di un database possono essere utilizzati anche per ripristinare una copia del database in una nuova posizione.

dispositivo di backup
Dispositivo disco o nastro nel quale vengono scritti i backup di SQL Server e da cui è possibile eseguirne il ripristino. I backup di SQL Server possono anche essere scritti nell’archiviazione BLOB di Azure e viene usato il formato URL per specificare la destinazione e il nome del file di backup. Per altre informazioni, vedere Backup e ripristino di SQL Server con l’archiviazione BLOB di Microsoft Azure.

supporti di backup
Uno o più nastri o file del disco in cui sono stati scritti uno o più backup.

backup dei dati
Backup dei dati in un database completo (backup del database), database parziale (backup parziale) o set di file di dati o di filegroup (backup di file).

backup del database
Backup di un database. I backup completi del database rappresentano l'intero database al momento del completamento del backup. I backup differenziali del database contengono solo le modifiche apportate al database a partire dal backup del database più recente.

backup differenziale
Backup dei dati basato sull'ultimo backup completo di un database completo o parziale o di un set di file di dati o di filegroup (base differenziale) che contiene solo i dati modificati rispetto a quella base.

backup completo
Backup dei dati che include tutti i dati in un database specifico o in un set di filegroup o file, oltre a una parte di log sufficiente al recupero di tali dati.

backup di log
Backup dei log delle transazioni che include tutti i record di log di cui non è stato eseguito il backup in un backup di log precedente. (modello di recupero con registrazione completa)

recover
Riportare un database a uno stato stabile e coerente.

recovery
Fase di avvio del database o di ripristino con recupero che porta il database in uno stato coerente a livello di transazioni.

modello di recupero
Proprietà del database che controlla la manutenzione del log delle transazioni su un database. Sono tre i modelli di recupero disponibili: con registrazione minima, con registrazione completa e con registrazione minima delle operazioni bulk. Il modello di recupero del database ne determina i requisiti di backup e di ripristino.

restore
Processo multifase che copia tutti i dati e le pagine di log da un backup di a un database specificato ed esegue il rollforward di tutte le transazioni registrate nel backup applicando le modifiche registrate in modo da aggiornare i dati.

Strategie di backup e ripristino

Il backup e il ripristino dei dati devono essere personalizzati per uno specifico ambiente e devono funzionare con le risorse disponibili. Un utilizzo affidabile di backup e ripristino per il recupero richiede pertanto una strategia di backup e ripristino, che, se ben progettata, è in grado di bilanciare i requisiti aziendali per la massima disponibilità di dati e la minima perdita di dati, tenendo in considerazione il costo della gestione e dell'archiviazione dei backup.

Tale strategia prevede una parte relativa al backup e una parte relativa al ripristino. La parte della strategia relativa al backup definisce il tipo e la frequenza delle operazioni di backup, il tipo e la velocità dell'hardware necessario, le modalità di esecuzione di test dei backup, nonché i percorsi e le modalità di archiviazione dei relativi supporti, incluse le considerazioni relative alla sicurezza. La parte della strategia relativa al ripristino definisce il responsabile dell'esecuzione delle operazioni di ripristino, la modalità di esecuzione di tali operazioni in modo da realizzare gli obiettivi relativi alla disponibilità del database e ridurre al minimo il rischio di perdita dei dati e il modo in cui condurre i test sui ripristini.

La progettazione di una strategia di backup e ripristino efficace richiede operazioni accurate di pianificazione, implementazione e testing. L'esecuzione di test è obbligatoria: una strategia di backup può essere considerata efficace solo dopo il completamento del ripristino dei backup in tutte le combinazioni incluse nella strategia e l'esecuzione dei test sul database ripristinato per verificarne la coerenza fisica. È necessario considerare una vasta gamma di fattori, tra cui:

  • Obiettivi di produzione dell'organizzazione riguardo ai database di produzione, in particolar modo i requisiti relativi alla disponibilità e alla protezione dalla perdita o dal danneggiamento dei dati.

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

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

Indicazioni sulle procedure consigliate

Agli account che eseguono operazioni di backup o ripristino non devono essere concessi più privilegi del necessario. Controlla il backup e il ripristino per informazioni specifiche sulle autorizzazioni. È consigliabile crittografare i backup e, se possibile, comprimerli.

Per garantire la sicurezza, i file di backup devono avere estensioni che seguono le convenzioni appropriate:

  • I file di backup del database devono avere .BAKl'estensione
  • I file di backup del log devono avere .TRNl'estensione.

Usare un'archiviazione separata

Importante

Assicurarsi di inserire i backup del database in un percorso o dispositivo fisico separato dai file del database. Quando l'unità fisica in cui sono archiviati i database non funziona correttamente o si arresta in modo anomalo, la possibilità di un recupero dipende dalla capacità di accedere all'unità separata o al dispositivo remoto in cui sono stati archiviati i backup per eseguire un ripristino. Tenere presente che è possibile creare più volumi logici o partizioni da una stessa unità disco fisica. Esaminare attentamente i layout delle partizioni del disco e dei volumi logici prima di scegliere un percorso 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, ovvero una proprietà del database tramite la quale si controlla la modalità di gestione del log delle transazioni. Il modello di recupero di un database determina quindi i tipi di scenari di backup e ripristino supportati per il database e le dimensioni dei backup dei log delle transazioni. In genere, in un database viene usato il modello di recupero con registrazione minima o il modello di recupero con registrazione completa. Il modello di recupero con registrazione completa può essere ottimizzato passando al modello di recupero con registrazione minima delle operazioni bulk prima delle operazioni bulk. Per un'introduzione a questi modelli di recupero e alla loro influenza sulla gestione del log delle transazioni, vedere Log delle transazioni (SQL Server)

Il modello di recupero migliore per un 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 di dati, aumentando tuttavia il numero di operazioni amministrative, è possibile utilizzare il modello di recupero con registrazione completa. Per ridurre al minimo l'impatto sulle dimensioni del log durante le operazioni con registrazione minima delle operazioni bulk, consentendo allo stesso tempo la recuperabilità di tali operazioni, usare il modello di recupero con registrazione minima delle operazioni bulk. Per informazioni sull'effetto dei modelli di recupero sulle operazioni di backup e ripristino, vedere Panoramica del backup (SQL Server).

Progettare la strategia di backup

Dopo aver selezionato un modello di recupero che soddisfa le esigenze aziendali per un determinato database, è necessario pianificare e implementare una strategia di backup corrispondente. La strategia ottimale dipende da una serie di fattori. Di seguito vengono riportati i più significativi:

  • Numero di ore giornaliere per cui è necessario garantire l'accesso delle applicazioni al database.

    Se è possibile prevedere un periodo di minore attività, è consigliabile pianificare i backup completi del database durante tale periodo.

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

    Se le modifiche sono frequenti, considerare gli aspetti seguenti:

    • Nel modello di recupero con registrazione minima è consigliabile pianificare backup differenziali nei periodi intermedi tra i backup completi del database. Con un backup differenziale è possibile acquisire solo le modifiche successive all'ultimo backup completo del database.

    • Nel modello di recupero con registrazione completa è necessario pianificare backup frequenti del 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.

  • Ambito previsto per le modifiche, ovvero solo in parti ridotte del database o in gran parte del database.

    Per un database di dimensioni estese in cui le modifiche sono concentrate in una parte dei file o dei filegroup, i backup parziali e/o i backup completi del file possono risultare utili. Per altre informazioni, vedere Backup parziali (SQL Server) e Backup dei file completo (SQL Server).

  • Quantità di spazio su disco necessaria per un backup completo del database.

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

    Assicurarsi di aver definito una pianificazione accurata dei backup in base alle esigenze dell'applicazione e ai requisiti aziendali. Man mano che i backup diventano datati, il rischio di perdere i dati aumenta a meno che non esista un sistema per rigenerare tutti i dati fino al punto di errore. Prima di scegliere di eliminare i vecchi backup a causa di limitazioni delle risorse, considerare se la recuperabilità deve risalire così lontano nel passato

Stimare le dimensioni di un backup completo del database

Prima di implementare una strategia di backup e ripristino, stimare quanto spazio su disco verrà utilizzato da un backup del database completo. Con l'operazione di backup i dati contenuti nel database vengono copiati nel file di backup. Poiché il backup include soltanto i dati presenti nel database, ma non lo spazio inutilizzato, le dimensioni del backup risultano di solito inferiori a quelle del database originale. È possibile stimare la dimensione di un backup del database completo tramite la stored procedure di sistema sp_spaceused . Per altre informazioni, vedere ssp_spaceused (Transact-SQL).

Pianificare le operazioni di backup

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

Per informazioni sulle restrizioni di concorrenza durante il backup, vedere Panoramica del backup (SQL Server).

Dopo aver stabilito i tipi di backup necessari e la frequenza di esecuzione per ogni tipo, è consigliabile pianificare backup regolari come parte di un piano di manutenzione per il 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.

Eseguire test dei backup

Una strategia di ripristino può essere considerata efficace solo dopo l'esecuzione di test dei backup. È essenziale testare accuratamente la strategia di backup per ogni database ripristinando una copia del database in un sistema di prova. È necessario testare il ripristino di tutti i tipi di backup che si desidera utilizzare. Si consiglia inoltre di eseguire, dopo il ripristino del backup, delle verifiche di coerenza via DBCC CHECKDB del database per assicurarsi che il supporto di backup non sia danneggiato.

Verificare la stabilità e la coerenza dei supporti

Usare le opzioni di verifica disponibili nelle utilità di backup (comando BACKUP di T-SQL, piani di manutenzione dei server SQL, software o soluzione di backup dell'utente e così via). Per un esempio, vedere [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Usare funzionalità avanzate come BACKUP CHECKSUM per rilevare i problemi del supporto di backup. Per altre informazioni, vedere Possibili errori relativi ai supporti durante il backup e il ripristino (SQL Server)

Strategia di backup/ripristino dei documenti

È consigliabile documentare le procedure di backup e ripristino e mantenerne una copia nella documentazione relativa alle procedure operative aziendali. È anche consigliabile mantenere un manuale operativo per ogni database, in cui indicare la posizione dei backup, i nomi dei dispositivi di backup (se presenti) e il tempo necessario per il ripristino dei backup di prova.

Monitorare l'avanzamento con xEvent

Le operazioni di backup e ripristino possono richiedere molto tempo a causa delle dimensioni di un database e della complessità delle operazioni coinvolte. Quando si verificano problemi con una delle operazioni, è possibile usare l'evento esteso backup_restore_progress_trace per monitorare l'avanzamento in tempo reale. Per altre informazioni sugli eventi estesi, vedere Eventi estesi.

Avviso

L'evento esteso backup_restore_progress_trace può causare un problema di prestazioni e usare una quantità di spazio su disco considerevole. È consigliabile usarlo per brevi periodi di tempo, prestare attenzione ed eseguire test accurati prima di implementarlo nell'ambiente di produzione.

-- Create the backup_restore_progress_trace extended event esssion
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 dell'evento esteso

Example of back up xevent outputExample of restore xevent output

Altre informazioni sulle operazioni di backup

Uso dei dispositivi di backup e dei supporti di backup

Creazione di backup

Nota

Per eseguire backup parziali o di sola copia è necessario usare l'istruzione BACKUP di Transact-SQL rispettivamente con l'opzione PARTIAL o COPY_ONLY.

Utilizzo di SSMS

Uso di T-SQL

Ripristinare backup di dati

Utilizzo di SSMS

Uso di T-SQL

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

Utilizzo di SSMS

Uso di T-SQL

Altre informazioni e risorse

Panoramica del backup (SQL Server)
Panoramica del ripristino e del recupero (SQL Server)
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
Backup e ripristino di database di Analysis Services
Backup e ripristino di indici e cataloghi full-text
Backup e ripristino di database replicati
Log delle transazioni (SQL Server)
Modelli di recupero (SQL Server)
Set di supporti, gruppi di supporti e set di backup (SQL Server)