Summary: Questo blog spiega come recuperare i record cancellati di una tabella in SQL Server. Illustra un esempio di recupero dei record eliminati da una tabella SQL utilizzando un Log Sequence Number (LSN). Esplora inoltre il software Stellar Repair for MS SQL per recuperare rapidamente i record eliminati senza alcuna perdita di dati.
Se si è eseguito accidentalmente un comando DROP o DELETE su una tabella con una clausola WHERE errata e si sono persi dei record importanti, è possibile recuperarli utilizzando uno dei seguenti metodi.
Nota: Le operazioni di troncamento non possono essere recuperate. È possibile recuperare solo le operazioni di cancellazione.
Metodi per recuperare i record di tabella eliminati in SQL Server
Metodo 1 – Utilizzo del numero di sequenza del registro (LSN)
Nota: Questo metodo non funziona se il backup del registro transazioni è danneggiato o mancante. In tal caso, passare al metodo successivo.
Ogni record del registro delle transazioni di SQL Server è identificato in modo univoco da un numero di sequenza del registro (LSN). È possibile costruire sequenze di ripristino utilizzando l’LSN di un record di registro in cui si è verificato un evento significativo.
Facciamo un esempio per capire come utilizzare il metodo del numero di sequenza del registro per recuperare i record della tabella eliminati. In questo esempio creeremo un database e una tabella, inseriremo righe nella tabella, cancelleremo alcune righe, otterremo informazioni sui dati cancellati e recupereremo i dati.
Passi per recuperare i record eliminati da una tabella SQL con LSN
In questa sezione, creeremo un database di prova e una tabella su cui eseguiremo un’operazione di cancellazione. Quindi, troveremo i dati cancellati e li recupereremo utilizzando LSN.
Passo 1: creare un database
Eseguire la seguente query per creare un database denominato ‘RecoverDeletedRecords’ e una tabella denominata ‘Employee’:
UTILIZZARE [master];
VAI
CREARE DATABASE RecoverDeletedRecords;
VAI
UTILIZZARE RecoverDeletedRecords;
VAI
CREARE TABELLA [Dipendente] (
[Sr.No] IDENTITÀ INT,
[Data] DATETIME DEFAULT GETDATE (),
[Città] CHAR (25) DEFAULT 'Città1')
Passo 2: Inserire i dati nella tabella
Abbiamo creato una tabella denominata ‘RecoverDeletedRecords’ e una tabella ‘Employee’ con tre colonne. Ora inseriamo le righe nella tabella eseguendo la seguente query:
UTILIZZARE RecoverDeletedRecords;
VAI
INSERT INTO Employee DEFAULT VALUES;
GO 100
Passo 3: Eliminare le righe dalla tabella
Ora cancelliamo alcune righe eseguendo quanto segue:
UTILIZZARE RecoverDeletedRecords
Vai
CANCELLARE i dipendenti
DOVE [Sr.No] < 10
VAI
Selezionare * da Dipendenti
Come mostrato nell’immagine precedente, tutte le righe con Sr.No inferiore a 10 sono state eliminate dalla tabella ‘Employee’.
Passo 4: Ottenere informazioni sulle righe eliminate
Successivamente, si otterranno informazioni sulle righe eliminate cercando nel registro delle transazioni:
UTILIZZARE RecoverDeletedRecords
VAI
SELEZIONARE
[LSN corrente],
[ID transazione],
Operazione,
Contesto,
Nome dell'unità di allocazione
DA
fn_dblog(NULL, NULL)
DOVE
Operazione = 'LOP_DELETE_ROWS'
Dopo aver ottenuto gli ID delle transazioni delle righe cancellate, dobbiamo trovare l’ora in cui le righe sono state cancellate.
Passo 5: Ottenere il numero di sequenza del registro del record di registro LOP_BEGIN_XACT
Per trovare l’ora esatta in cui le righe sono state eliminate, è necessario utilizzare l’ID della transazione per ottenere l’LSN del record di log LOP_BEGIN_XACT di una transazione:
UTILIZZARE RecoverDeletedRecords
VAI
SELEZIONARE
[LSN corrente],
Operazione,
[ID transazione],
[Ora di inizio],
[Nome della transazione],
[Transaction SID]
DA
fn_dblog(NULL, NULL)
DOVE
[ID transazione] = '0000:0000020e'
E
[Operazione] = 'LOP_BEGIN_XACT'
Nella schermata precedente, è possibile vedere l’LSN corrente della transazione insieme a dettagli come l’ora ‘2021/03/15 19:36:59:337’ in cui è stata eseguita un’istruzione DELETE con LSN ‘00000014:0000001a:0001’ sotto l’ID transazione ‘0000:0000020e’. Per recuperare i dati della tabella del database SQL cancellata, procedere con il passo successivo.
Passo 6: Recupero dei record eliminati in SQL Server
Per recuperare i record della tabella SQL cancellati, è necessario convertire i valori LSN da esadecimali a decimali. Per farlo, aggiungere ‘0x’ prima del numero di sequenza del registro, come mostrato nel codice seguente:
-Ripristino del backup completo con assenza di ripristino.
RESTORE DATABASE RecoverDeletedRecords_COPY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak'
CON
SPOSTARE 'RecoverDeletedRecords' in 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.mdf',
SPOSTARE 'RecoverDeletedRecords_log' in 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.ldf',
SOSTITUIRE, NON RECUPERARE;
VAI
-Ripristinare il backup del registro con l'opzione STOPBEFOREMARK per ripristinare l'esatto LSN.
RESTORE LOG RecoverDeletedRecords_COPY
DA
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords_tlogbackup.trn'
CON
STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'
Ora che i dati sono stati ripristinati, verificare se i record eliminati sono stati recuperati. A tale scopo, eseguire la seguente query per visualizzare le prime dieci righe della tabella che sono state eliminate (al punto 3).
UTILIZZARE RecoverDeletedRecords_COPY
VAI
SELEZIONARE * da DipendentiSe i record delle tabelle eliminate non sono stati recuperati, provare a utilizzare uno strumento di recupero SQL come Stellar Repair for MS SQL per recuperare i dati eliminati.
Nota: nel codice sopra riportato, sostituire il percorso ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’ con il percorso in cui si trova il file di backup. Inoltre, sostituire il percorso dei file .mdf e .ldf con il percorso in cui sono stati salvati i file del database.
Metodo 2 – Utilizzo di un software di recupero SQL
Il software di recupero SQL di Stellar® può aiutare a recuperare tutti i record eliminati in un database SQL Server in pochi semplici passaggi. Oltre ai dati eliminati, il software è in grado di recuperare anche tutti gli altri oggetti come viste, trigger, stored procedure e così via, mantenendo l’integrità dei dati.
Prima di procedere
Prima di utilizzare il software per ripristinare i record, accertarsi che il sistema soddisfi i seguenti prerequisiti:
- Innanzitutto, è necessario installare SQL Server. È possibile utilizzare qualsiasi SQL Server installato in Windows.
- In secondo luogo, assicuratevi di avere installato SQL Server Management Studio (SSMS).
- Software Stellar Repair for MS SQL
Passi per recuperare i record di tabella eliminati con Stellar Repair for MS SQL
Scaricate la versione demo del software di recupero SQL per recuperare le righe eliminate o i database danneggiati:
- Avviare il software Stellar Repair for MS SQL. L’interfaccia principale del software si apre con l’istruzione di interrompere il database di SQL Server e creare una copia in una posizione diversa. Premere OK.
- Sfogliare il file MDF del database SQL che si desidera riparare. Se non si conosce la posizione del file MDF, fare clic su Cerca per trovare e selezionare il file:
Nota: se non sapete dove si trova il vostro database, potete trovarlo in SSMS nelle proprietà del database, nella pagina del file di dati (fate riferimento all’immagine sottostante).
- Dopo aver selezionato il file di database, selezionare l’opzione Includi record eliminati e quindi fare clic su Ripara.
- Il software richiede di selezionare una modalità di scansione appropriata per riparare il file di database. Selezionare “Scansione standard” o “Scansione avanzata”, quindi fare clic su OK.
- Fare clic su OK quando si apre la finestra di messaggio “Riparazione completata”.
- Il software mostra un’anteprima dei record delle tabelle recuperabili. Ripristina anche altri oggetti del database come viste, sinonimi, stored procedure, funzioni, regole, impostazioni predefinite, tipi di dati, trigger, sequenze e assiemi. Il software visualizza anche un rapporto di registro contenente informazioni come la dimensione del database, la collazione, il percorso del file mdf e l’elenco delle tabelle e degli oggetti.
- Quindi, fare clic su Salva nel menu File per salvare i dati recuperati.
- Nella finestra di dialogo ‘Save Database’ (Salva database), è possibile scegliere di salvare i dati recuperati nei formati MDF, CSV, HTML o XLS (Excel). In questo esempio, sceglieremo MSSQL:
- È possibile creare un nuovo database o lavorare con il database online. È possibile ripristinare in un’altra istanza di SQL Server e specificare il tipo di autenticazione, ossia Autenticazione Windows o Autenticazione SQL Server. Inoltre, è possibile specificare la posizione dei file del database:
- Fare clic su Salva.
I record della tabella recuperati e altri dati selezionati verranno salvati nel file MDF riparato.
Guardate il processo completo di funzionamento del software SQL Recovery:
Conclusione
In questo articolo abbiamo appreso come recuperare i record cancellati in SQL Server. Queste soluzioni possono funzionare con qualsiasi versione di SQL Server in Windows. Se avete a disposizione il backup completo del database, potete provare a recuperare i record eliminati utilizzando il registro delle transazioni con il Log Sequence Number (LSN). Se il backup è danneggiato, utilizzare Stellar Repair for MS SQL per recuperare i dati eliminati in SQL Server o in altri formati come file CSV (Comma Separated Value), HTML o XLS.
Il software di riparazione del database SQL non solo aiuta a recuperare le righe cancellate, ma ripristina anche il database SQL danneggiato da attacchi di virus, guasti hardware o altri motivi.
