Condividi tramite

Ripristinare un database di SQL Server a un punto nel tempo (Modello di Recupero Completo)

  • Articolo

Si applica a:SQL Server

Questo argomento illustra come ripristinare un database a un momento specifico in SQL Server usando SQL Server Management Studio o Transact-SQL. Le informazioni contenute in questo argomento sono rilevanti solo per i database di SQL Server che utilizzano i modelli di recupero completo o con registrazione minima.

Importante

Nel modello di recupero con registrazione minima delle operazioni bulk se un backup del log contiene modifiche con registrazione minima delle operazioni bulk, il recupero temporizzato non è possibile rispetto a un punto all'interno di tale backup. È necessario recuperare il database fino alla fine del backup del log delle transazioni.

Prima di iniziare

Consigli

  • Utilizzo dell'opzione STANDBY per la ricerca di un punto sconosciuto nel tempo

  • Specificare il punto nel tempo all'inizio di una sequenza di ripristino

Sicurezza

Autorizzazioni

Se il database da ripristinare non esiste, per eseguire un'operazione RESTORE l'utente deve disporre delle autorizzazioni CREATE DATABASE. Se il database esiste, le autorizzazioni per l'istruzione RESTORE vengono assegnate per impostazione predefinita ai membri dei ruoli predefiniti del server sysadmin e dbcreator e al proprietario (dbo) del database. Per l'opzione FROM DATABASE_SNAPSHOT, il database esiste sempre.

Le autorizzazioni RESTORE vengono assegnate ai ruoli in cui le informazioni sull'appartenenza sono sempre prontamente disponibili per il server. Poiché è possibile controllare l'appartenenza ai ruoli predefiniti del database solo quando il database è accessibile e non è danneggiato, condizioni che non risultano sempre vere quando si esegue un'operazione RESTORE, i membri del ruolo predefinito del database db_owner non dispongono delle autorizzazioni per l'istruzione RESTORE.

Utilizzo di SQL Server Management Studio

Per ripristinare un database a un momento specifico

  1. Stabilire una connessione all'istanza appropriata del motore di database di SQL Server, quindi espandere l'albero di server in Esplora oggetti.

  2. Espandere Database. A seconda del database, selezionare un database utente oppure espandere Database di sistemae selezionare un database di sistema.

  3. Fare clic con il pulsante destro del mouse sul database, scegliere Attività, selezionare Ripristina, quindi fare clic su Database.

  4. Per specificare l'origine e il percorso dei set di backup da ripristinare, nella pagina Generale , utilizzare la sezione Origine . Selezionare una delle seguenti opzioni:

    • Database

      Selezionare il database da ripristinare dall'elenco a discesa. Nell'elenco sono inclusi solo i database di cui è stato eseguito il backup in base alla cronologia dei backup di msdb .

    Nota

    Se il backup viene eseguito da un server diverso, il server di destinazione non disporrà delle informazioni della cronologia di backup per il database specificato. In questo caso, selezionare Dispositivo per specificare manualmente il file o il dispositivo da ripristinare.

    • Dispositivo

      Fare clic sul pulsante Sfoglia (...) per aprire la finestra di dialogo Seleziona dispositivi di backup . Nella casella Tipi di supporti di backup selezionare uno dei tipi di dispositivi elencati. Per selezionare uno o più dispositivi per la casella Supporti di backup , fare clic su Aggiungi.

      Dopo avere aggiunto i dispositivi desiderati nella casella di riepilogo Dispositivi di backup , fare clic su OK per tornare alla pagina Generale .

      Nella casella di riepilogo Origine: Dispositivo: Database selezionare il nome del database da ripristinare.

      Nota Questo elenco è disponibile solo se si seleziona Dispositivo . Saranno disponibili solo i database che dispongono di backup sul dispositivo selezionato.

  5. Nella sezione Destinazione , la casella Database viene popolata automaticamente con il nome del database da ripristinare. Per modificare il nome del database, immettere il nome nuovo nella casella Database .

  6. Fare clic su Sequenza temporale per accedere alla finestra di dialogo Sequenza temporale di backup.

  7. Nella sezione Ripristina fino a fare clic su Data e ora specifiche.

  8. Utilizzare le caselle Data e Ora o la barra del dispositivo di scorrimento per specificare una data e ora specifiche per l'arresto del ripristino. Seleziona OK.

    Nota

    Usare la casella Intervallo sequenza temporale per modificare la quantità di tempo visualizzata nella sequenza temporale.

  9. Dopo aver specificato una determinata temporizzazione, tramite Database Recovery Advisor viene assicurato che nella colonna Ripristina della griglia Set di backup da ripristinare vengano selezionati solo i backup necessari per il ripristino fino a quella temporizzazione. Questi backup selezionati costituiscono la strategia di ripristino consigliata per il ripristino a un punto specifico nel tempo. Per l'operazione di ripristino temporizzato è consigliabile utilizzare solo i backup selezionati.

    Per informazioni sulle colonne nella griglia Selezionare i set di backup da ripristinare, vedere Ripristina database (pagina Generale). Per informazioni su Database Recovery Advisor, vedere Panoramica del ripristino e del recupero (SQL Server).

  10. Nella pagina Opzioni del pannello Opzioni di ripristino è possibile selezionare una qualsiasi delle opzioni seguenti, in base alla situazione:

    • Sovrascrivi il database esistente (WITH REPLACE)

    • Mantieni le impostazioni di replica (WITH KEEP_REPLICATION)

    • Limita accesso al database ripristinato (WITH RESTRICTED_USER)

    Per altre informazioni su queste opzioni, vedere Ripristina database (pagina Opzioni).

  11. Selezionare un'opzione per la casella Stato di recupero . Questa casella determina lo stato del database al termine dell'operazione di ripristino.

    • RESTORE WITH RECOVERY è il comportamento predefinito che lascia il database pronto per l'utilizzo mediante il rollback delle transazioni di cui non è stato eseguito il commit. I log delle transazioni aggiuntivi non possono essere ripristinati. Selezionare questa opzione se si desidera ripristinare subito tutti i backup necessari.

    • RESTORE WITH NORECOVERY lascia il database non operativo e non esegue il rollback delle transazioni di cui non è stato eseguito il commit. I log delle transazioni aggiuntivi possono essere ripristinati. Non è possibile utilizzare il database fino al completamento del recupero.

    • RESTORE WITH STANDBY lascia il database in modalità di sola lettura. Annulla le transazioni di cui non è stato eseguito il commit, ma salva le azioni di rollback in un file standby in modo che gli effetti del recupero possano essere annullati.

    Per una descrizione delle opzioni, vedere Ripristina database (pagina Opzioni).

  12. Esegui il backup del log finale prima del ripristino verrà selezionato se necessario per il punto nel tempo che hai selezionato. Non è necessario modificare questa impostazione, ma è possibile scegliere di eseguire il backup della parte finale del log, anche se non è richiesto.

  13. Le operazioni di ripristino potrebbero non riuscire in presenza di connessioni attive al database. Selezionare l'opzione Chiudi connessioni esistenti per garantire che tutte le connessioni attive tra Management Studio e il database vengano chiuse. Questa casella di controllo imposta il database sulla modalità utente singolo prima di effettuare qualsiasi operazione di ripristino e imposta il database sulla modalità multiutente al termine.

  14. Selezionare Chiedi conferma prima del ripristino di ogni backup se si desidera ricevere una richiesta di conferma prima di ciascuna operazione di ripristino. L'operazione non è normalmente necessaria, a meno che le dimensioni del database siano elevate e si desideri monitorare lo stato dell'operazione di ripristino.

Utilizzo di Transact-SQL

Operazioni preliminari

Un momento specifico viene sempre ripristinato da un backup del log. In ogni istruzione RESTORE LOG della sequenza di ripristino, è necessario specificare l'ora o la transazione di destinazione in una clausola STOPAT identica. Come prerequisito per un ripristino temporizzato, è necessario innanzitutto ripristinare un backup completo del database il cui endpoint sia precedente rispetto al momento di ripristino di destinazione. Il backup completo del database può essere più vecchio dell'ultimo backup completo del database purché vengano ripristinati tutti i backup del log successivi, fino a includere il backup del log che contiene il tuo punto temporale di destinazione.

Per consentire l'identificazione del backup del database da ripristinare, è possibile specificare la clausola WITH STOPAT nell'istruzione RESTORE DATABASE per generare un errore se un backup dei dati è troppo recente rispetto alla data e ora specifica di destinazione. Il backup completo dei dati viene sempre ripristinato, anche se contiene la data e ora specifica di destinazione.

Sintassi Transact-SQL di base

RESTORE LOG nome_database FROM <dispositivo_backup> WITH STOPAT =ora, RECOVERY...

Il punto di recupero è l'ultimo commit delle transazioni eseguito in corrispondenza o prima del valore datetime specificato da time.

Per ripristinare solo le modifiche apportate prima di determinato momento, specificare WITH STOPAT =ora per ogni backup da ripristinare. Questo garantisce che non superi il tempo target.

Per ripristinare un database a un certo momento

Nota

Per un esempio di questa procedura, vedere Esempio (Transact-SQL)più avanti in questa sezione.

  1. Connettersi all'istanza del server in cui si desidera ripristinare il database.

  2. Eseguire l'istruzione RESTORE DATABASE utilizzando l'opzione NORECOVERY.

    Nota

    Se una sequenza di ripristino parziale esclude qualsiasi filegroup FILESTREAM , il ripristino temporizzato non è supportato. È possibile forzare la continuazione della sequenza di ripristino. Tuttavia i filegroup FILESTREAM omessi dall'istruzione RESTORE non potranno mai più essere ripristinati. Per forzare un ripristino a un punto specifico nel tempo, specificare l'opzione CONTINUE_AFTER_ERROR insieme all'opzione STOPAT, STOPATMARK o STOPBEFOREMARK, che è necessario includere anche nelle istruzioni successive di RESTORE LOG. Se si specifica CONTINUE_AFTER_ERROR, la sequenza di ripristino parziale ha esito positivo e il filegroup FILESTREAM non può più essere recuperato.

  3. Ripristinare l'ultimo backup del database differenziale, se presente, senza recuperare il database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  4. Applicare ogni backup del log delle transazioni nella stessa sequenza di creazione, specificando l'ora in cui si intende arrestare il ripristino del log (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT =time, RECOVERY).

    Nota

    Le opzioni RECOVERY e STOPAT. Se il backup del log delle transazioni non contiene i dati corrispondenti all'ora richiesta, ad esempio se l'ora specificata è successiva al periodo di tempo gestito dal log delle transazioni, viene generato un messaggio di avviso e il database non viene recuperato.

Esempio (Transact-SQL)

Nell'esempio seguente viene ripristinato un database allo stato di 12:00 AM del April 15, 2020 e viene illustrata un'operazione di ripristino che coinvolge più backup del log. Nel dispositivo di backup, AdventureWorksBackups, il backup completo del database da ripristinare è il terzo set di backup (FILE = 3), il primo backup del log è il quarto set di backup (FILE = 4) e il secondo backup del log è il quinto set di backup (FILE = 5).

Importante

Il database AdventureWorks2022 utilizza il modello di recupero semplice. Per consentire i backup del log, prima di eseguire un backup completo del database, il database è stato impostato per l'utilizzo del modello di recupero completo tramite ALTER DATABASE AdventureWorks SET RECOVERY FULL.

RESTORE DATABASE AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=3, NORECOVERY;  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
RESTORE DATABASE AdventureWorks WITH RECOVERY;   
GO

Attività correlate

Vedi anche

backupset (Transact-SQL)
RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)