Panoramica del ripristino e del recupero (SQL Server)
Si applica a:
SQL Server
Questo articolo offre informazioni generali sul processo di ripristino e recupero del database per SQL Server.
Panoramica
Per recuperare un database di SQL Server in seguito a un errore, il relativo amministratore deve ripristinare un set di backup di SQL Server in una sequenza di ripristino significativa e logicamente corretta. Il processo di ripristino e ripristino di SQL Server supporta il ripristino dei dati dai backup di un intero database, di un file di dati o di una pagina di dati, come indicato di seguito:
Database ( ripristino di database completo)
L'intero database viene ripristinato e recuperato e il database resta offline durante le operazioni di ripristino e di recupero.
File di dati ( ripristino del file)
Un file di dati o un set di file viene ripristinato e recuperato. Durante un ripristino del file, i filegroup che includono i file vengono impostati automaticamente come offline durante il ripristino. Qualsiasi tentativo di accedere a un filegroup offline provoca un errore.
Pagina di dati ( ripristino di pagina)
Nel modello di recupero con registrazione completa o con registrazione minima delle operazioni bulk è possibile ripristinare singole pagine. Le operazioni di ripristino della pagina possono essere effettuate in qualsiasi database, indipendentemente dal numero di filegroup.
Il backup e il ripristino di SQL Server possono essere usati in tutti i sistemi operativi supportati. Per informazioni sui sistemi operativi supportati, vedere Requisiti hardware e software. Per informazioni sul supporto per i backup delle versioni precedenti di SQL Server, vedere la sezione supporto per la compatibilità di RESTORE.
Scenari di ripristino
Uno scenario di ripristino in SQL Server è il processo di ripristino dei dati da uno o più backup, seguito dal recupero del database. Gli scenari di ripristino supportati dipendono dal modello di recupero del database e dall'edizione di SQL Server.
Nella tabella seguente vengono descritti i possibili scenari di ripristino supportati per modelli di recupero diversi.
| scenario di ripristino | Nel modello di recupero semplice | Nel modello di ripristino con registrazione completa o con registrazione bulk minimizzata |
|---|---|---|
| ripristino di database completo | Strategia di ripristino di base. Un ripristino di database completo può comportare semplicemente il ripristino e il recupero di un backup completo del database. In alternativa, tale tipo di ripristino può comportare il ripristino di un backup completo del database seguito dal ripristino e dal recupero di un backup differenziale. Per ulteriori informazioni, vedere Ripristini di database completi (Modello di recupero semplice). |
Strategia di ripristino di base. Un ripristino di database completo comporta il ripristino di un backup completo del database e, facoltativamente, di un backup differenziale, se disponibile, seguito dal ripristino di tutti i successivi backup del log, in sequenza. Il ripristino completo del database si completa recuperando l'ultimo backup del log e ripristinandolo (RESTORE WITH RECOVERY). Per altre informazioni, vedere Ripristini di database completi (modello di recupero completo) |
| Ripristino di file 1 | Consente di ripristinare uno o più file di sola lettura danneggiati senza ripristinare l'intero database. Il ripristino dei file è disponibile solo se il database ha almeno un filegroup di sola lettura. | Consente di ripristinare uno o più file, senza ripristinare l'intero database. Può essere eseguito mentre il database è offline oppure, per alcune edizioni di SQL Server, mentre il database rimane online. Durante un'operazione di ripristino del file, i filegroup che includono i file che vengono ripristinati sono sempre offline. |
| Ripristino di pagine | Non applicabile | Consente di ripristinare una o più pagine danneggiate. Può essere eseguito mentre il database è offline oppure, per alcune edizioni di SQL Server, mentre il database rimane online. Durante un'operazione di ripristino della pagina, le pagine che vengono ripristinate sono sempre offline. Perché la pagina sia aggiornata rispetto al file di log corrente, è necessario che sia disponibile una catena non interrotta di backup del log, fino al file di log corrente, e che i backup vengano tutti applicati. Per altre informazioni, vedere Ripristinare pagine (SQL Server). |
| Ripristino parziale 1 | Consente di ripristinare e recuperare il database in varie fasi a livello di filegroup, partendo dal filegroup primario e da tutti i filegroup secondari di lettura/scrittura. | Consente di ripristinare e recuperare il database in varie fasi a livello di filegroup, partendo dal filegroup primario. Per altre informazioni, vedere Ripristini parziali (SQL Server) |
1 Il ripristino online è supportato solo nell'edizione Enterprise.
Passaggi per ripristinare un database
Per ripristinare un file, il motore di database esegue due passaggi:
Crea eventuali file di database mancanti.
Copia i dati dai dispositivi di backup nei file di database.
Per ripristinare un database, il motore di database esegue tre passaggi:
Crea i file di database e i file del log delle transazioni se non esistono già.
Copia tutti i dati, il log e le pagine di indice dai supporti di backup di un database nei file del database.
Applica il log delle transazioni, in ciò che viene detto processo di recupero.
Indipendentemente dalla modalità di ripristino dei dati, prima di poter recuperare un database, il motore di database di SQL Server verifica che l'intero database sia logicamente consistente. Ad esempio, se si ripristina un file, non è possibile recuperarlo e portarlo online fino a quando non viene eseguito un rollforward sufficiente per garantire la coerenza con il database.
Vantaggi del ripristino di un file o di una pagina
Il ripristino e il recupero di file o pagine, anziché dell'intero database, offrono i vantaggi seguenti:
Il ripristino di una quantità minore di dati consente di ridurre il tempo necessario per la copia e il recupero.
Se in SQL Server si esegue un'operazione di ripristino del file o della pagina, è possibile mantenere online altri dati del database durante l'operazione di ripristino.
Ripristino e log delle transazioni
Per la maggior parte degli scenari di ripristino, è necessario applicare un backup del log delle transazioni e consentire al motore di database di SQL Server di eseguire il processo di recupero affinché il database sia disponibile online. Il recupero è il processo che SQL Server usa per avviare ogni database in uno stato coerente a livello di transazione o in uno stato originario.
Se si verifica un failover o un altro arresto del sistema non pulito, i database potrebbero rimanere in uno stato in cui alcune modifiche non sono mai state trasferite dalla cache del buffer ai file di dati, e nei file di dati potrebbero essere presenti alcune modifiche provenienti da transazioni incomplete. Quando viene avviata un'istanza di SQL Server, viene eseguito un recupero di ogni database che comprende tre fasi, sulla base dell'ultimo checkpoint del database:
La fase 1 è la fase di analisi durante la quali si analizza il log delle transazioni per determinare l'ultimo checkpoint e si crea la tabella delle pagine dirty e la tabella delle transazioni attive. La Tabella delle Pagine Sporche (DPT) contiene record di pagine sporche nel momento in cui il database è stato arrestato. La ATT contiene record di transazioni che erano attive al momento in cui il database non è stato arrestato correttamente.
La fase 2 è la fase di ripetizione durante la quale si esegue il rollforward di ogni modifica registrata nel log che potrebbe non essere stata scritta nei file di dati al momento dell'arresto del database. Il numero di sequenza minimo del file di log (minLSN) necessario per il recupero dell'intero database si trova nella DPT e contrassegna l'inizio delle operazioni di riapplicazione necessarie su tutte le pagine sporche. In questa fase il motore di database di SQL Server scrive su disco tutte le pagine dirty appartenenti alle transazioni di cui è stato eseguito il commit.
La Fase 3 è la Fase di annullamento durante la quale si esegue il rollback di tutte le transazioni incomplete rilevate nella tabella delle transazioni attive (ATT) per assicurare l'integrità del database. Dopo il rollback il database passa nello stato online, dopodiché non è possibile applicare alcun altro backup del log delle transazioni al database.
Le informazioni sullo stato di avanzamento di ogni fase di recupero del database vengono registrate nel log degli errori di SQL Server. È anche possibile rilevare lo stato di avanzamento del recupero del database usando gli eventi estesi. Per altre informazioni, vedere il post di blog New extended events for database recovery progress (Nuovi eventi estesi per lo stato di avanzamento del recupero del database).
Nota
In uno scenario di ripristino parziale, se un filegroup di sola lettura è stato tale fin da prima della creazione del backup del file, l'applicazione dei backup del log al filegroup non è necessaria e viene quindi saltata durante il ripristino del file.
Nota
Per ottimizzare la disponibilità dei database in un ambiente aziendale dopo l'avvio del servizio SQL Server, ad esempio dopo un failover di un'istanza del cluster di failover Always On o un riavvio in-place, SQL Server Enterprise Edition può portare online un database dopo la Fase di Redo, mentre la Fase di Undo è ancora in esecuzione. Questo processo è noto come recupero rapido. Tuttavia, il recupero rapido non è disponibile quando il database passa a uno stato online, ma il servizio SQL Server non è stato riavviato. Ad esempio, l'esecuzione di ALTER DATABASE AdventureWorks SET ONLINE; non consentirà al database di essere in stato di lettura/scrittura fino al completamento di tutte e tre le fasi di recupero.
Modelli di recupero e operazioni di ripristino supportate
Le operazioni di ripristino disponibili per un database variano in base al relativo modello di recupero. Nella tabella seguente vengono riepilogati i casi e la misura in cui ognuno dei modelli di recupero supporta uno scenario di ripristino specifico.
| Operazione di ripristino | Modello di recupero completo | Modello di recupero con registrazione minima delle operazioni bulk | Modello di recupero semplice |
|---|---|---|---|
| Recupero dati | Recupero completo (se il log è disponibile). | Rischio parziale di perdita di dati. | Tutti i dati successivi all'ultimo backup completo o differenziale vanno perduti. |
| Ripristino temporizzato | Qualsiasi periodo di tempo coperto dai backup del log. | Non consentito se il backup del log contiene modifiche con registrazione minima bulk. | Non supportato. |
| Ripristino di file 1 | Supporto completo. | In casi specifici. 2 | Disponibile solo per i file secondari di sola lettura. |
| Ripristino della pagina 1 | Supporto completo. | In casi specifici. 2 | Nessuno. |
| Ripristino parziale (a livello di filegroup) 1 | Supporto completo. | In casi specifici. 2 | Disponibile solo per i file di tipo secondario di sola lettura. |
1 Disponibile solo nell'edizione Enterprise di SQL Server
2 Per le condizioni necessarie, vedere Restrizioni relative al ripristino nel Modello di recupero semplice più avanti in questo articolo.
Importante
Indipendentemente dal modello di recupero di un database, un backup di SQL Server non può essere ripristinato da una versione del motore di database di SQL Server precedente a quella usata per creare il backup.
Scenari di ripristino nel modello di recupero semplice
Il modello di recupero semplice impone le restrizioni seguenti sulle operazioni di ripristino:
Il ripristino del file e il ripristino a fasi sono disponibili solo per gruppi di file secondari in modalità di sola lettura. Per informazioni su questi scenari di ripristino, vedere Ripristino dei file (Modello di recupero semplice) e Ripristini a fasi (SQL Server).
Il ripristino della pagina non è consentito.
Il ripristino temporizzato non è consentito.
Se qualcuna di queste restrizioni risulta inappropriata per le necessità di recupero, consigliamo di valutare l'uso del modello di recupero completo. Per ulteriori informazioni, vedere Panoramica del backup (SQL Server).
Importante
Indipendentemente dal modello di recupero di un database, un backup di SQL Server non può essere ripristinato da una versione di SQL Server precedente a quella tramite cui è stato creato il backup.
Ripristino nel modello di recupero con registrazione minima delle operazioni bulk
In questa sezione vengono discusse considerazioni sul ripristino specifiche del modello di recupero con registrazione minima delle operazioni bulk, che è destinato esclusivamente come supplemento al modello di recupero con registrazione completa.
Nota
Per un'introduzione al modello di recupero con registrazione minima, vedere Il log delle transazioni.
Generalmente, il modello di recupero con registrazione parziale è simile al modello di recupero con registrazione completa, e le informazioni fornite per quest'ultimo si applicano a entrambi. Tuttavia, il recupero temporizzato e il ripristino in linea sono influenzati dal modello di recupero con registrazione minima delle operazioni bulk.
Restrizioni relative al recupero temporizzato
Se un backup del log eseguito utilizzando il modello di recupero con registrazione in blocco contiene modifiche di tale tipo, il recupero temporizzato non è consentito. Se si tenta di eseguire un ripristino a un momento specifico su un backup del log che contiene modifiche in blocco, l'operazione di ripristino fallisce.
Restrizioni relative al ripristino online
Una sequenza di ripristino in linea funziona solo se vengono soddisfatte le condizioni seguenti:
Tutti i backup del log necessari devono essere eseguiti prima dell'avvio della sequenza di ripristino.
È necessario eseguire un backup delle modifiche bulk prima di avviare la sequenza del ripristino online.
Se nel database sono presenti modifiche in blocco, tutti i file devono essere online o inattivi, ovvero non più inclusi nel database.
Se queste condizioni non vengono soddisfatte, la sequenza del ripristino in linea non riesce.
Nota
È consigliabile passare al modello di recupero completo prima di avviare un ripristino online. Per altre informazioni, vedere Modelli di ripristino (SQL Server).
Per informazioni su come eseguire un ripristino online, vedere Ripristino in linea (SQL Server).
Database Recovery Advisor (SQL Server Management Studio)
Tramite Database Recovery Advisor viene semplificata la costruzione di piani di ripristino mediante i quali vengono implementate ottime sequenze di ripristino corrette. Molti problemi noti di ripristino del database sono stati risolti e sono stati apportati molti miglioramenti richiesti dai clienti. I principali miglioramenti introdotti da Database Recovery Advisor includono:
Algoritmo del piano di ripristino: l'algoritmo utilizzato per costruire i piani di ripristino è stato migliorato in modo significativo, in particolare per gli scenari di ripristino complessi. Molti casi limite, inclusi gli scenari di fork nei ripristini temporizzati, vengono gestititi in modo più efficiente rispetto alle versioni precedenti di SQL Server.
Ripristini temporizzati: tramite Database Recovery Advisor viene notevolmente semplificato il ripristino di un database in un determinato momento. Tramite una cronologia di backup visiva viene migliorato in modo significativo il supporto per i ripristini temporizzati. Tramite questa cronologia visiva è possibile identificare un momento appropriato come punto di recupero di destinazione per il ripristino di un database. Tramite la cronologia viene semplificato l'attraversamento di un percorso di recupero con fork, cioè un percorso che si estende su più fork di recupero. In un piano di ripristino temporizzato specificato sono inclusi automaticamente i backup rilevanti per il ripristino al punto nel tempo previsto (data e ora). Per ulteriori informazioni, vedere Ripristinare un database di SQL Server a un punto specifico nel tempo (modello di recupero completo).
Per ulteriori informazioni su Database Recovery Advisor, vedere i seguenti blog relativi alla facilità di gestione di SQL Server:
Ripristino accelerato del database
Ripristino accelerato del database (ADR) è disponibile a partire da SQL Server 2019 (15.x). ADR è disponibile anche nel database SQL di Azure, nell'Istanza Gestita di Azure SQL, in Azure Synapse Analytics (solo pool SQL dedicato) e nei database SQL in Microsoft Fabric. Il ripristino accelerato del database consente di migliorare considerevolmente la disponibilità del database, in particolare in presenza di transazioni a esecuzione prolungata, riprogettando il processo di ripristino del motore di database di SQL Server. Un database con ADR abilitato completa notevolmente il processo di ripristino in modo significativo dopo un failover o un altro arresto imprevisto. Quando ADR è abilitato, il rollback delle transazioni di lunga durata annullate viene completato istantaneamente.
È possibile abilitare ADR per database in SQL Server 2019 (15.x) e versioni successive usando la sintassi seguente:
ALTER DATABASE [<db_name>] SET ACCELERATED_DATABASE_RECOVERY = ON;
Nota
L'ADR è sempre abilitato nel database SQL di Azure, nell'Istanza SQL gestita di Azure, in Azure Synapse Analytics (solo pool SQL dedicato) e nel database SQL in Microsoft Fabric.