SQL Server, how to recover deleted data or data from a dropped table… without backup!

Hi Guys,

Welcome to this midweek post! Today's topic is...

 

"How to recover deleted data or data from a dropped table… without backup!"

Sometimes a carelessness or a mistake is enough, and in the end, it happens! ...yes, In no time at all you find yourself having deleted rows or entire tables from the database that you should not have deleted!

And now? …What can we do?

Enjoy the reading mates!

How to recover deleted data or data from a dropped table

If you've just deleted some data or deleted a table (panic aside) you need to know that there is good news.

Each data change occurs within a transaction and then goes through the transaction log.
Have you performed an update? ..or did you perform a deletion? Then these data changes are passed through the transaction log.

Always!

Even if you haven't explicitly opened a transaction, SQL Server has opened one (implicit) for you.

At this point you will have already understood where we are going to look for the data deleted inadvertently: the transaction log!

We can read the content of the SQL Server transaction Log via the undocumented T-SQL function fn_dblog.

You can use this function by optionally passing two parameters called start and end LSN where the acronym LSN stands for log sequence number.

LSN is a uniquely incrementing three-part value used to maintain the sequence of the transaction log records in the database.
Keep in mind that each transaction (explicit or implicit) has its own unique number.

Before moving on to an example, it must be said that there is also another similar function called fn_dump_dblog.
This second function returns the same results but can read, in addition to the online transaction log file, also the SQL Server transaction log Backup.

As an example we create a table and populate it with some data then see what our function fn_dblog gives us.

 

CREATE TABLE [dbo].[EMPLOYEE](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NAME] [varchar](80) NULL,
	[SALARY] [float] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[EMPLOYEE] VALUES ('LUKE',19900 )
GO
INSERT INTO [dbo].[EMPLOYEE] VALUES ('LUKE',19800 )
GO 
INSERT INTO [dbo].[EMPLOYEE] VALUES ('MARK',20000 )
GO 

SELECT * FROM sys.fn_dblog(NULL,NULL) 
      

Just by running these few T-SQL commands, we can see that over 500 rows have appeared in the transaction log file.

The transaction log file has many columns, but the current LSN, the operation, and the transaction ID columns are very important.

Let’s try now to delete a row!

SELECT MAX([Transaction ID]) FROM sys.fn_dblog(NULL,NULL) 

DELETE FROM [EMPLOYEE] WHERE ID = 2

SELECT MAX([Transaction ID]) FROM sys.fn_dblog(NULL,NULL) 
      

We have a new transaction ID  (SQL Server opened an implicit one for us)
 

We will now take a look at the transaction log with this T-SQL command:

SELECT * FROM sys.fn_dblog(NULL,NULL) WHERE [transaction ID] = '0000:000004ad'
      

Here is what we get:

We have 3 rows:

•    The first row represents the opening of the transaction
•    The second row where the operation column has the value LOP_DELETE_ROWS represents our deleted row
•    The first row represents the closing of the transaction

Now look to the columns RowLog Contents 0 … RowLog Contents 5

The first value is this “0x3000100002000000000000000056D34003000001001B004C554B45”

Now, to view the deleted data, you just need to decode the hexadecimal strings contained in the RowLog fields

It is not such a simple process and therefore today I will only show you schematically the format used to store the data.

 

The first 2 bytes, called “Status Bits A” and “Status Bits B”, are the bitmaps containing the information about the row.

We have these two values:

30 is the status Bits A
00 is the status Bits B

Next 2 bytes contain an offset, this value is used to find number of column.
The value of “1000” must be read from right to left so the real value is 0001

Attention that we are starting to see the data right now!

The next 2 bytes (in yellow in the pics above) contain a value of “02000000”
This value is nothing else that the ID of the deleted row.
Note that you must read data "byte by byte" from right to left so our ID is equal to 00000002
This is true in fact we have deleted the row where ID =2

The next 6 bytes (in light blue in the pics above) contain the value for the column SALARY.
The value of “000000000056D340” must be read from right to left and it is the representation of the float value 19800.

I will not go into further details on the following fields but we will continue to analyze the string until we find the sequence "4C554B45" (in light purple in the photos above)
The string “4C554B45” is the value deleted from the NAME column. The string “4C 55 4B 45” is “LUKE”

OK! 

We found all the data we needed but as you may have understood at this point data recovery is not an easy process.
True, a dedicated script could be developed but I recommend that you use specific dedicated software to recover deleted records.
This is because the recovery process will have been tried and validated over and over again.

 

Recover data deleted automatically with Stellar Data Repair

One of the Stellar software that I have reviewed here called Stellar Repair for MS SQL is very useful in this situation!

 

Even if the database is not corrupt, it still allows you to recover deleted records.

Just check the “Include Deleted Records” box.

 

 

Press the button Repair!

 

 

That'all all for today!

Stay tuned and follow me on linkedin.

I wish a good week continuation.

 

If you think that this post it is a masterpiece or simply you find it useful you can offer to me an icecream  

Previous post: SQL Server, how to use a user function as default for a column