Come ripristinare un backup del registro delle transazioni in SQL Server

Il ripristino di un backup del registro delle transazioni in SQL Server è un passaggio cruciale nel recupero del database. Ci consente di far avanzare le modifiche in un momento specifico. Sia che tu stia riprendendo da fallimento, migrazione di dati o scenari di check, il processo di ripristino del registro delle transazioni garantisce la coerenza dei dati applicando transazioni impegnate fino a un momento specifico nel tempo.

In questo articolo, impareremo i passaggi chiave, gli errori comuni e le migliori pratiche a Ripristina backup del registro delle transazioni in SQL Server con successo.

Innanzitutto, capiamo i diversi tipi di backup supportati in SQL Server.

Comprensione dei tipi di backup SQL Server

Server SQL Supporta cinque tipi di backup.

Backup completo

Il backup completo contiene l’intero database, inclusi gli oggetti di dati e database, in un momento specifico. Può essere utilizzato come base per tutti gli altri tipi di backup, il che significa che quando si desidera ripristinare il backup differenziale e il backup del registro delle transazioni, è richiesto un backup completo.

Backup differenziale

Il backup differenziale contiene i dati e le modifiche agli oggetti che si sono verificate dopo l’ultimo backup completo. I backup differenziali sono più piccoli e ci vuole meno tempo per completare.

Backup del registro delle transazioni

I backup del registro delle transazioni catturano tutte le modifiche che si sono verificate dopo l’ultimo backup completo o differenziale. I backup del registro delle transazioni sono di dimensioni molto piccole e di solito completano molto rapidamente. I backup del registro delle transazioni vengono presi solo quando il database è nel modello di ripristino completo.

I backup del registro delle transazioni svolgono un ruolo cruciale nel processo di recupero del database. Ecco alcune notice importanti sui backup del registro delle transazioni.

1. I backup del registro delle transazioni acquisiscono ogni modifica avvenuta nel database, che garantisce zero perdita di dati tra backup completi o differenziali.
2. I backup del registro delle transazioni ti aiutano a recuperare il database in un momento preciso nel tempo, il che significa che è possibile recuperare il database prima della corruzione dei dati o della cancellazione accidentale.
3. Quando il database si trova in un modello di ripristino completo, le transazioni di grandi dimensioni possono talvolta far diventare più grandi i file di registro delle transazioni, portando a potenziali problemi. Quando eseguiamo il backup di un file di registro delle transazioni, tronca tutte le modifiche registrate nel file di registro delle transazioni, che mantiene le dimensioni del file di registro delle transazioni alla dimensione originale.

Backup di solo copia

I backup solo per copia sono come un backup completo e contengono i dati e la struttura del database nel momento in cui viene preso. L’unica differenza è che un backup completo non rompe la catena di recupero, il che è molto cruciale quando si utilizza il modello di recupero completo. Se il database è nel modello di ripristino completo e quando si esegue un backup completo, ripristinerà la catena di recupero, che invalida i precedenti backup.

Backup del gruppo di file

I backup file/filegroup vengono utilizzati per eseguire il backup del file di dati specifico o del filegroup. Questi backup sono utili quando si desidera eseguire il backup dei gruppi di file di sola lettura.

Prerequisiti per ripristinare un backup del registro delle transazioni

Prima di iniziare a ripristinare il backup del registro delle transazionidobbiamo controllare il server di database per la sua versione, risorse di sistema e file di backup del database accessibile. I controlli prerequisiti sono cruciali perché se qualcuno di essi fallisce, interromperà il processo di recupero e lo ritarderà. Ecco l’elenco degli elementi che devono essere controllati prima di iniziare il processo di recupero.

Verifica l’edizione server e la versione

Verifica sempre la versione del server, soprattutto quando si ripristina il backup preso su una versione o un’edizione diversa di SQL Server. Non possiamo ripristinare il backup preso da una versione superiore a una versione inferiore. Advert esempio, non è possibile ripristinare il backup di SQL Server 2022 su un database SQL Server 2019.

Possiamo eseguire la seguente question per controllare la versione di SQL Server:

La question sopra ti fornirà i dettagli della versione e dell’edizione SQL Server.

Controlla le risorse di archiviazione e sistema

Successivamente, dovremmo verificare che uno spazio adeguato sia disponibile dopo aver ripristinato backup completi e differenziali. A volte eseguiamo lo script di ripristino in una sequenza senza verificare l’archiviazione disponibile. In tali circostanze, il processo di ripristino verrà interrotto e porterà a tempi di inattività più lunghi. Inoltre, dobbiamo verificare che l’account SQL Server abbia l’autorizzazione richiesta per leggere i file di backup.

Controlla la sequenza di backup

Quando ripristiniamo qualsiasi database in SQL Server, dobbiamo mantenere la sequenza dei backup che verranno ripristinati. SQL Server utilizza sempre il numero di sequenza di registro per tracciare le modifiche nel registro delle transazioni. Se ripristina il backup in ordine non valido, romperà la catena LSN e ripristinerà i successivi backup.

Mantieni sempre la seguente sequenza:

1. Innanzitutto, ripristina il backup completo con il NORECOVERY opzione.
2. Secondo, ripristinare il backup differenziale, se presente, con il NORECOVERY opzione.
3. Infine, ripristina tutti i backup del registro delle transazioni che vengono presi dopo un backup completo o differenziale. Usare il RESTORE WITH RECOVERY opzione.

Per controllare i file di backup appropriati, è possibile utilizzare il RESTORE HEADERONLY comando. La sintassi è la seguente:

RESTORE HEADERONLY FROM DISK = 'C:BackupsYourBackup.bak' 

Il comando fornisce le seguenti informazioni chiave.

1. Tipo di backup: Mostra il tipo di backup (log completo, differenziale o di transazione).
2. Posizione: Mostra la posizione del set di backup all’interno del file di backup specificato.
3. Primo LSN e Final LSN: Mostra i numeri della sequenza di registro per il backup.

Puoi anche usare il RESTORE FILELISTONLY Comando per controllare l’elenco dei file di database. Ecco la sintassi del comando.

RESTORE FILELISTONLY FROM DISK = 'C:BackupsYourDatabase.bak' 

Il comando fornisce le seguenti informazioni.

1. Nome logico: Fornisce il nome logico del file.
2. Nome fisico: Fornisce il nome fisico del file.
3. Tipo: Mostra se il file è un file di registro o un file di dati.

Ora, facci capire come ripristinare un backup del registro delle transazioni.

Guida passo-passo per ripristinare un backup del registro delle transazioni

Ora, facci capire come ripristinare i backup del registro delle transazioni. Per la dimostrazione, ripristineremo il stackoverflow2010 database. Ripristineremo il backup del registro delle transazioni utilizzando una question T-SQL.

Supponiamo di voler ripristinare un database su un server separato che contiene tutti i dati del database di produzione. Il server ha tre lavori di backup. Un lavoro di backup completo fa un backup ogni notte. Un lavoro di backup differenziale funziona ogni 12 ore e un backup del registro delle transazioni funziona ogni ora.

Per ripristinare un clone sul server di sviluppo, dobbiamo ripristinare il database nella seguente sequenza.

1. Ripristina il backup completo con il NORECOVERY opzione.
2. Ripristina il backup differenziale con il NORECOVERY opzione.
3. Ripristina tutti i backup dei registri delle transazioni eseguiti dopo l’ultimo backup differenziale.

Ripristineremo il database utilizzando uno script T-SQL. Il nome del database clone sarà Stackoverflow2010_Clone. Innanzitutto, dobbiamo ripristinare il backup completo. Ecco il comando.

USE (grasp)

Go

RESTORE DATABASE (StackOverflow2010_Clone) FROM DISK = N'D:MS_SQLBackupStackOverflow2010_Full_Backup.bak'

WITH FILE = 1,

MOVE N'StackOverflow2010' TO N'D:MS_SQLDataStackOverflow2010_Clone.mdf',

MOVE N'StackOverflow2010_log' TO N'D:MS_SQLLogStackOverflow2010_Clone_log.ldf',

NORECOVERY, NOUNLOAD, STATS = 5

GO 

Come puoi vedere dallo screenshot sopra, abbiamo usato il MOVE Clausola nel database di ripristino. Il motivo è che la posizione del file di dati e la posizione del file di registro sul server clone sono various dalla posizione del server di produzione; Quindi, dobbiamo cambiare la posizione fisica usando il MOVE Parole chiave.

Puoi leggere di più sul RESTORE DATABASE comando qui. Il backup completo viene ripristinato con il NORECOVERY Opzione in modo da poter applicare backup differenziali e di registro in seguito. Una volta ripristinato il backup, il database sarà nel RESTORING stato. È possibile eseguire una question SQL per visualizzare lo stato del database.

choose

title (Database Identify),

create_date (Database create Date),

state_desc (Database State),

user_access_desc (Person entry standing)

from sys.databases the place title="StackOverflow2010_Clone" 

Output di question:

Successivamente, ripristineremo il backup differenziale. Per fare ciò, eseguire il seguente comando.

RESTORE DATABASE (StackOverflow2010_Clone) FROM

DISK = N'D:MS_SQLBackupStackOverflow2010_Diff_Backup.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

Una volta ripristinato il backup differenziale, ripristineremo i backup del registro delle transazioni. Per farlo, useremo il RESTORE LOG comando. Ecco la domanda.

RESTORE LOG (StackOverflow2010_Clone) FROM DISK = N'D:MS_SQLBackupStackOverflow2010_log_Backup_1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG (StackOverflow2010_Clone) FROM DISK = N'D:MS_SQLBackupStackOverflow2010_log_Backup_2.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG (StackOverflow2010_Clone) FROM DISK = N'D:MS_SQLBackupStackOverflow2010_log_Backup_3.trn' WITH FILE = 1, NOUNLOAD, RECOVERY, STATS = 5 

Si noti che abbiamo ripristinato l’ultimo backup del registro con il RECOVERY opzione. Ciò renderà on-line un database e nessun ulteriore backup può essere ripristinato nel database. Una volta ripristinato il database, è possibile eseguire la seguente question per verificare lo stato del database.

choose

title (Database Identify),

create_date (Database create Date),

state_desc (Database State),

user_access_desc (Person entry standing)

from sys.databases the place title="StackOverflow2010_Clone"

Output di question:

Errori comuni e suggerimenti per la risoluzione dei problemi

Errore: “Il registro in questo set di backup inizia a LSN …”

Di solito, questo errore si verifica quando la catena di log della transazione (sequenza LSN) è rotta. Ciò potrebbe accadere a causa di un backup pieno o differenziale mancante o perché i backup sono fuori sequenza.

Per evitare che si verifichino tali problemi, prima di ripristinare il backup del registro delle transazioni, verificare sempre la sequenza di backup. È possibile utilizzare la seguente question per controllare la cronologia di backup.

SELECT * FROM msdb.dbo.backupset

WHERE database_name="YourDB"

ORDER BY backup_start_date DESC; 

Innanzitutto, ripristina il backup completo più recente, quindi ripristina tutti i successivi backup di registro differenziale e delle transazioni.

Errore: “Il database è in uso.”

Questo errore si verifica quando i processi utente sono connessi al database che viene ripristinato.

Per correggere l’errore, seguire i passaggi seguenti:

1. Imposta il database in modalità utente singolo

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Carry out backup/restore

ALTER DATABASE YourDB SET MULTI_USER; 

2. Uccidi le sessioni attive

-- Generate kill instructions for all lively connections

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';'

FROM sys.dm_exec_sessions

WHERE database_id = DB_ID('YourDB');

3. Ripristina il registro delle transazioni utilizzando l’opzione limited_user

RESTORE DATABASE YourDB FROM DISK = 'path' WITH RESTRICTED_USER; 

Conclusione

In questo articolo, comprendiamo diversi tipi di backup e come ripristinare un backup del registro delle transazioni per clonare un database. Apprendiamo anche i problemi comuni che incontriamo quando ripristiniamo il registro delle transazioni e come risolverli.

Il processo per riparare un database corrotto ripristinando backup SQL nativi e backup del registro delle transazioni è un processo semplice e semplice. Ma se hai una situazione in cui una catena LSN è rotta e non hai un backup completo compatibile disponibile, la perdita di dati è imminente. In tali circostanze, dovremmo iniziare a considerare strumenti di terze parti per riparare il database. Qui vorrei raccomandare uno strumento di recupero dei dati, riparazione stellare per MS SQL, che offre molte opzioni per recuperare il database corrotto.