Come recuperare i record di tabella eliminati in SQL Server

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 Dipendenti

Se 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.