Recover deleted data in SQL Server

recover deleted data in sql serverIf you ever have the misfortune of having data deleted from the production database, don’t get upset as there is a way you can recover most of the missing data with the aid of Transaction Logs and LSNs (Log Sequence Numbers).
 Here is a step-by-step solution on how to achieve this.

Even if you use SqlBak to backup everything unauthorized data deletion from your database is one of the worst nightmares in terms of database administration. If you know when your data was deleted then the challenge to recover the deleted data is not as difficult. But, if you do not know the date and time when the data was deleted, then we will have to find a different way of identifying the data.

How to recover deleted data in SQL Server

Here are the steps we are going to use in recovering our data.

    • For testing and example purposes we will create a database and a table schema.
    • USE [master];
      GO
      CREATE DATABASE RecoverTestDatabase
      GO
      
      USE [RecoverTestDatabase];
      GO
      CREATE TABLE [BlogArticles](
          [Article_ID] INTEGER IDENTITY,
          [Artilce_Date] DATETIME DEFAULT GETDATE(),
          [Article_Creator] NVARCHAR(50) DEFAULT 'Admin');
    • Populate the table with data using this script.
    • INSERT INTO BlogArticles DEFAULT VALUES;
      GO 30
    • Continue by deleting some records from the table in order to recover them through the help of LSNs in the SQL Server Transaction Log.
    • DELETE FROM BlogArticles
      WHERE Article_ID < 15;
      GO
      
      SELECT * FROM BlogArticles;

You can see the results returned by the query above that the records are actually missing from the table.

    • Now, let’s go and review the Transaction Log of SQL Server to get the deleted records. Run the next query in order to get basic information about the deleted records.
    • SELECT
          [Current LSN],
          [Transaction ID],
          [Operation],
          [Context],
          [AllocUnitName]
      FROM fn_dbLog(NULL, NULL)
      WHERE [Operation] = 'LOP_DELETE_ROWS'
    • If you’ve gone along using all of the queries in this example, then after running the query above you should have the following result set returned.

recover deleted data in sql server

    • The Transaction ID column will show us what DELETE statement was run, but in case you didn’t notice the AllocUnitName column stores the table from which the transactions have been deleted. You can additionally filter the results to display only those of this table with the query below.
    • SELECT
          [Current LSN],
          [Transaction ID],
          [Operation],
          [Context],
          [AllocUnitName]
      FROM fn_dblog(NULL, NULL)
      WHERE [Operation] = 'LOP_DELETE_ROWS'
          AND [AllocUnitName] = 'dbo.BlogArticles'
    • Other columns give us information about the table type, in this case, it’s a HEAP type of table and the transaction that the DELETE statement was performed through transaction 0000:0000022c, and as we can see there are multiple records with the same transaction ID.
    • This type of information is quite important as it shows us that all of the records from the BlogArticles table were deleted with one transaction, which doesn’t necessarily mean one query.
    • In order to prove this run the next batch of queries
    • BEGIN TRANSACTION
      
      DELETE FROM BlogArticles
      WHERE Article_ID % 2 = 0
      
      DELETE FROM BlogArticles
      WHERE Article_ID % 3 = 0
      
      COMMIT
    • If now if we are to go and run the query that displays data from fn_dblog, we will see that both queries have run in the same transaction, thus proving that this is not sufficient evidence to identify the number of queries.

recover deleted data in sql server

    • Now, we are going to take the transaction ID from the queries above. We will continue with the transaction ID returned by our second query, the one in the image above highlighted with blue, 0000:00000248.

Please be aware that from this point on you will have to use the LSN you have in your result set. We will continue this example with the LSN we were provided with by the query.

    • Using this transaction ID we will use it in the next statement to retrieve the LSN (Log Sequence Number) of the LOP_BEGIN_XACT operation.
    • This means we will need the exact LSN when the DELETE query began its transaction.
    • SELECT
          [Current LSN],
          [Operation],
          [Transaction ID],
          [Begin Time],
          [Transaction Name],
          [Transaction SID]
      FROM fn_dblog(NULL, NULL)
      WHERE [Transaction ID] = '0000:00000248'
          AND [Operation] = 'LOP_BEGIN_XACT'
    • The above query should give you similar output to the one in the image below.

recover deleted data in sql server

    • Now we know the time the DELETE statement was executed, what is its LSN and what transaction it was executed under.
    • The next step involves changing the hexadecimal value of the LSN number into a decimal so we will be able to retrieve our data.
    • As you might see, our LSN number has 3 sections, each separated by a colon. We will use a query on each section of the LSN in order to get its decimal value.
    • Run the below query once for each part of the LSN and specify that part as a parameter to get the decimal number. In our LSN’s case the queries that we need to run are:
    • SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00000014', 1))
      SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000000ee', 1))
      SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1))

recover deleted data in sql server

    • Now that we have the decimal values of the sections of the LSN we will be using the STOPBEFOREMARK operation in order to recover our data.
    • We will use the decimal returned by the first query as it is, in this case, it’s 20.
    • But the next decimals have to be preceded by leading zeros. The second decimal has to be preceded by zeros until it has a length of 10 characters, while the third should also be preceded by zeros until it has a length of 5.
    • Thus, the outputs that you are going to need in use are returned by the following queries
-- Outputs the decimal for the second section

SELECT RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x000000ee', 1)) AS NVARCHAR), 10)

-- Outputs the decimal for the third section

SELECT RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5)

recover deleted data in sql server

    • Now that we have the three decimal parts we can put the numbers together in order to get the LSN number. In our case, the LSN is 20000000023800001.
    • The next step is to run a Transaction Log Backup on the database from where data was deleted if there was no previous transaction log backup done since the data was removed.
    • Now, we will do a restore of this database at a different location or on the same server with a different name up until the point of the LSN number we calculated above.
    • After that, we will import the missing data from the newly restored database to the main database.
-- Starting first with restoring the FULL BACKUP with NORECOVERY
RESTORE DATABSE RecoverTestDatabase_COPY
    FROM DISK = 'C:\RecoverTestDatabase_FULL.bak'
WITH
    MOVE 'RecoverTestDatabase' TO 'C:\RecoverTestDatabase.mdf'
   ,MOVE 'RecoverTestDatabase_log' TO 'C:\RecoverTestDatabase_log.mdf'
   ,REPLACE
   ,NORECOVERY;
GO

-- Restore LOG backup with STOBEFOREMARK option to recover exact LSN
RESTORE LOG RecoverTestDatabase_COPY
    FROM DISK = 'C:\RecoverTestDatabase_log.trn'
WITH
    STOPBEFOREMARK = 'lsn:20000000023800001'
    • At this point your restored database is ready and you can check if the deleted data is recovered or not. Run the below SELECT to see the first rows which were deleted.
    • USE RecoverTestDatabase_COPY
      
      SELECT * FROM BlogArticles

recover deleted data in sql server

    • You can see in the above screenshot that all rows were recovered. Now, this data can be used. You can move the data from this database restore to your main database through SSIS‘s Import and Export feature.

25 thoughts on “Recover deleted data in SQL Server”

  1. Hey as per your instruction i followed all of your instructions at the last i am getting following error:
    Processed 24528 pages for database ‘SalesDBOriginal_new’, file ‘SalesDBData’ on file 1.
    Processed 3 pages for database ‘SalesDBOriginal_new’, file ‘SalesDBLog’ on file 1.
    RESTORE DATABASE successfully processed 24531 pages in 9.358 seconds (20.479 MB/sec).
    Msg 4335, Level 16, State 2, Line 3
    The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
    Msg 3013, Level 16, State 1, Line 3
    RESTORE LOG is terminating abnormally.

    plz tell me what should i do now

    Reply
    • Hi there,

      You are constrained by using the STOPAT command, which generally is used only when restoring logs, but it is not necessarily used only for log backups/restores.

      If you are restoring a FULL or DIFFERENTIAL backup, then it is possible that these backups contain a part of the log which also has the changes you want to revert to.

      Thus, when you do a restore of these backups, the records that were captured in the log can be applied and in this way you can bring back the database to a consistent state and possibly have the records you want.

      In case there are no log records captured and the transactions were occuring while the FULL backup was initially being done, then the database would be inconsistent.

      An example situation would be backing up a FULL database, process which would take 2 hours to complete (from 3 AM to 5 AM). You can in this situation restore the backup file and also use STOPAT 4 AM so you would capture just the completed transactions that occurred up until half point.

      I hope my answer is clear and it also offered a more detailed view about the process.

      If you have any other questions please post and I will try to answer as soon as possible.

      Reply
  2. I was not able to execute the below query –
    Use master
    RESTORE DATABASE pund330_copy
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.EVAL1\MSSQL\Backup\pund330.bak’
    MOVE ‘pund330’ TO ‘C:\pund330.mdf’,
    MOVE ‘pund330_log’ TO ‘C:\pund330_log.ldf’,
    REPLACE, NORECOVERY;
    GO

    It gave me error – Logical file ‘pund330’ is not part of database ‘pund330_copy’. Use RESTORE FILELISTONLY to list the logical file names.

    Reply
  3. Hi,

    I followed everything as you said.I was able to restore my previous database to new one.But after executing restore query,database is being in restoring status.And it is taking long time to complete restore operation even though i have only 5 records in my previous table.

    Reply
    • Thank you for posting this alternative. As I can see your solution helps you identify the user who removed the records from the database, while this post you commented on will help you recover the missing records and restore the database to a state which you want.

      Thank you once again for posting a comment and also please feel free to browse through other articles on this blog.

      Reply
  4. Hi,

    Please mention, when should I take a full backup and when should I take a log backup to get the perfect results like you from the above task.

    Thanks in advance.

    Reply

Leave a Comment