Condividi tramite

Backup e ripristino di database DI SQL Server

Si applica a:SQL Server

Questo articolo descrive i vantaggi del backup dei database di SQL Server, descrive i termini di backup e ripristino di base e introduce strategie di backup e ripristino per SQL Server e considerazioni sulla 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 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 Informazioni sul backup di SQL Server nelle 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
Processo di creazione di un backup [sostantivo] copiando i record di dati da un database di SQL Server o record di log dal log delle transazioni.

backup
Copia dei dati che è possibile usare per ripristinare e ripristinare i dati dopo un errore. 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 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 del 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. Esistono tre modelli di recupero: semplici, completi e registrati in blocco. Il modello di recupero di un database determina i requisiti di backup e ripristino.

restore
Processo multifase che copia tutti i dati e le pagine di log da un backup di SQL Server specificato in un database specificato e quindi esegue il rollforward di tutte le transazioni registrate nel backup applicando le modifiche registrate per inoltrare i dati in tempo.

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. Pertanto, un uso affidabile del backup e del ripristino per il ripristino richiede una strategia di backup e ripristino. Una strategia di backup e ripristino ben progettata bilancia i requisiti aziendali per la massima disponibilità dei dati e la perdita minima di dati, considerando al contempo il costo di gestione e archiviazione dei backup.

Tale strategia prevede una parte relativa al backup e una parte relativa al ripristino. La parte di backup della strategia definisce il tipo e la frequenza dei backup, la natura e la velocità dell'hardware necessari, il modo in cui i backup devono essere testati e dove e come archiviare i supporti di backup (incluse le considerazioni sulla sicurezza). La parte di ripristino della strategia definisce chi è responsabile dell'esecuzione dei ripristini, come devono essere eseguiti i ripristini per soddisfare gli obiettivi di disponibilità del database e ridurre al minimo la perdita di dati e come vengono testati i ripristini.

La progettazione di una strategia di backup e ripristino efficace richiede operazioni accurate di pianificazione, implementazione e testing. Il test è obbligatorio: non è disponibile una strategia di backup fino a quando non sono stati ripristinati correttamente i backup in tutte le combinazioni incluse nella strategia di ripristino e non è stato testato il database ripristinato per la coerenza fisica. È necessario considerare una vasta gamma di 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

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. Di conseguenza, il modello di recupero di un database determina i tipi di backup e gli scenari di ripristino supportati per il database e le dimensioni dei backup del 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. È possibile aumentare il modello di recupero con registrazione completa passando al modello di recupero con registrazione minima delle operazioni bulk prima delle operazioni bulk. 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.

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 l'esposizione alla perdita di lavoro al costo del sovraccarico amministrativo, usare 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 ripristino sul backup e sul ripristino, vedere Panoramica del backup.

Progettare la strategia di backup

Dopo aver selezionato un modello di ripristino che soddisfi i requisiti aziendali per un database specifico, è 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 è previsto un periodo di minore attività prevedibile, è consigliabile pianificare i backup completi del database per 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 completi dei file (SQL Server).For more information, see Partial backups (SQL Server) and Full file backups (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 backup precedenti a causa delle limitazioni delle risorse di archiviazione, prendere in considerazione se la recuperabilità è necessaria in 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 le dimensioni di un backup completo del database usando la sp_spaceused stored procedure di sistema. 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; pertanto, è possibile eseguire operazioni di backup durante le normali operazioni. È 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.

Testare i backup

Non è disponibile una strategia di ripristino fino a quando non si testano i backup. È molto importante testare accuratamente la strategia di backup per ognuno dei database ripristinando una copia del database in un sistema di test. È necessario testare il ripristino di tutti i tipi di backup che si desidera utilizzare. È inoltre consigliabile eseguire controlli di coerenza del database tramite DBCC CHECKDB del database per verificare che il supporto di backup non sia stato danneggiato.

Verificare la stabilità e la coerenza dei supporti

Usare le opzioni di verifica fornite dalle utilità di backup (BACKUP comando T-SQL, piani di manutenzione di SQL Server, software di backup o soluzione e così via). Per un esempio, vedere Istruzioni RESTORE - VERIFYONLY.

Usare funzionalità avanzate come BACKUP CHECKSUM per rilevare problemi con il supporto di backup stesso. 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 lo stato di 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 entrambe le operazioni, è possibile usare l'evento backup_restore_progress_trace esteso per monitorare lo stato di avanzamento live. Per altre informazioni sugli eventi estesi, vedere Panoramica degli eventi estesi.

Avviso

L'uso dell'evento backup_restore_progress_trace esteso può causare un problema di prestazioni e utilizzare una quantità significativa di spazio su disco. È 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 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 dell'evento esteso

Screenshot di un esempio di output xevent di backup. Screenshot di un esempio di output xevent di backup continuo.

Altre informazioni sulle operazioni di backup

Usare dispositivi di backup e supporti di backup

Creare backup

Nota

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

Usare SSMS

Usare T-SQL

Ripristinare backup di dati

Usare SSMS

Usare T-SQL

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

Usare SSMS

Uso di T-SQL

Contenuti correlati

  • Last updated on