How To Quickly Restore SQL Data That Has Been Deleted

It has happened to most DBA’s at one point or another – a time when they have had to quickly restore data that had been wrongly deleted.

It usually begins with an embarrassed user shuffling nervously over to you with a look of fear in their eyes.

“I had a little accident” They stammer awkwardly whilst looking down at their feet

“Its ok” You calmly reassure them.. “I am sure we will be able to sort it out – what happened?”

They begin to unravel the series of unfortunate events that caused the database to react with a mind of its own, and delete the data in question.

Remember as a DBA it is not up to you apportion blame, if anyone is to blame it is you for letting those that can do harm to your precious databases, have the rights to do damage!

Anyway – its time for the restore. There are a number of options available to you depending on the situation.

How to restore the missing rows from a previous backup

Option 1

This is the simplest way, if you have a good idea of the logic that caused the deletion and know of a time prior to the last log or full backup where the data existed is just to restore another copy of the db, identify what was deleted and insert from the backup, to the live copy.

Option 2

If you need to know the EXACT time when the problem happened you need to get technical and dig into the transaction log, to identify the issue.

Firstly lets create a sample table and do a demonstration.

----------------------------------------------
USE DBAFire_DB

CREATE TABLE _Test_Delete (id INT)

INSERT INTO _Test_Delete
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
-----------------------------------------------

Now along comes our unlucky user, not realizing the power that someone has give them over this valuable data.

They run the below command, however instead of deleting rows > 3 they delete the rows < 3.

If they were more careful they would have done the delete within a transaction and checked the results before committing. However we have to work with what is in front of us.

 ----------------------------------------------
USE DBAFire_DB

DELETE FROM _Test_Delete WHERE id < 3

GO

--Note the now missing rows

SELECT * FROM _Test_Delete
 ----------------------------------------------

Now the table is missing the rows 1 and 2, which were actually required.

As long as the DB is in FULL recovery mode we can go in and see what happened. Make sure you put in the dbo. in front of the table name, or it won’t pick it up.

-------------------------------------------

SELECT
    [AllocUnitName],*
FROM fn_dbLog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'
AND [AllocUnitName] = 'dbo._Test_Delete'    

-------------------------------------------

dba_deletes
From the information provided to you, see if you can identify the problem transaction. Then grab the relevant transaction Id and use it in this query.

-------------------------------------------

SELECT
 [Begin Time], *
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:00324a74'
    AND [Operation] = 'LOP_BEGIN_XACT'

--------------------------------------------

The results from that query will show you the begin time of the transactions. (LOP_BEGIN_XACT marks the begin point of the transaction)

fn_dblog

Now you know you can restore your db to the LSN immediately Prior the one shown. That is however, not as easy as it sounds. First we need to break down the LSN
into a decimal that can be used in our restore script.

The Current LSN ( 00001a5c:0000670c:0001 )is split up into 3 parts.

-------------------------------------------
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00001a5c', 1))
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x00006794', 1))
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1))
-------------------------------------------

To make up the LSN number we need to add 000 to the second line to bring it to a length of 10 0’s. To the third line we need to make it up to 5 0’s.

— Outputs the decimal for the second section

SELECT RIGHT(‘0000000000’ + CAST(CONVERT(INT, CONVERT(VARBINARY, ‘0x0000670c’, 1)) AS NVARCHAR), 10)

— Outputs the decimal for the third section

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

To put it into one line query you can run this

--------------------------------------------

SELECT CONVERT (Varchar(10), CONVERT(INT, CONVERT(VARBINARY, '0x00001a5c', 1))) 
+ CONVERT(Varchar(10),RIGHT('0000000000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x00006794', 1)) AS NVARCHAR), 10)) 
+ CONVERT(VARCHAR(10),RIGHT('00000' + CAST(CONVERT(INT, CONVERT(VARBINARY, '0x0001', 1)) AS NVARCHAR), 5))

--------------------------------------------

Now we have all the information that we need, we just need a backup to restore from. And either take a transaction log backup if one hasn’t, or use one that has occurred after the incident.

--------------------------------------------
-- First restore the FULL BACKUP with NORECOVERY so Tran logs can be applied
RESTORE DATABASE DBAFIRE_DB
    FROM DISK = 'C:\temp\DBAFire_DB.bak'
  WITH  FILE = 1, REPLACE
   ,NORECOVERY;
GO

-- Restore LOG backup with STOBEFOREMARK option to recover exact LSN
RESTORE LOG  [DBAFire_DB] 
    FROM DISK = 'C:\Temp\DBAFire_DB.trn'
WITH
    STOPBEFOREMARK = 'lsn:6748000002651600001'
    
--------------------------------------------

Now you can rerun the query and if all has gone as it should have, your records have magically reappeared!

--------------------------------------------

SELECT * FROM _Test_Delete

--------------------------------------------

Other Methods of Recovery

The above methods should work for most situations – there are always other options however and here are a couple.

  • Restore to a point of time alongside and manually replace the records
  • If the dataset is small reenter the records

If you have another alternative method that you would like to share – please do!

REF: http://sqlbak.com/blog/recover-deleted-data-in-sql-server/

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.