SQL server, How to recover just deleted data (...ops i did it again!)

Hi Guys,

Since the previous post received a lot of reviews, today we continue with the series "omg I deleted the wrong record!"

Then let us immerse ourselves in the dense atmosphere of a server room.

This time you have dared too much. You didn't do your nice "begin tran" command before doing that damn delete .. and you deleted the wrong row.

In the previous post we saw that we can recover the data.

But if you have just made the cancellation, do you have any more hope of recovering the data on the fly? 

Enjoy the reading!

 

Deleted data or Ghost record?

The first important fact is that records are not physically deleted when you execute a delete.

In fact, for performance reasons when deleting data SQL Server only marks the data as ghost record.

Therefore, the deleted row stays on the database. In the row header it will be written that that the row is now a ghost row.

...and if you think about it, this is a very useful mechanism because it prevents us from reinserting the deleted data in case we have to do a rollback! great!

Then, it will be a task called "ghost cleanup task" to provide for the physical deletion of data according to a certain frequency.

Today we will not go into the details of how this task works, rather we will understand how to see the deleted data.

 

How to see the deleted data

For the example we will create a new database.

We need to disable the ghost cleanup task using the traceflag 661

Create database TestGhostRows

Use TestGhostRows

DBCC TRACEON(661,-1); DBCC TRACEON(3604,-1);

Then we will create a new table, insert some data, delete a part of it and try to find our data!

Create Table TestTable (id int identity(1,1) primary key, code char(1000))

Insert into TestTable (code) values ('Itzy Bitzy')
go 6

Delete from TestTable where Id = 1

 

Now execute the DBCC PAGE command:

DBCC PAGE('TestGhostRows',1,1,3) WITH TABLERESULTS

 

We can clearly see that there is a data page that contains ghost records.
Page number 119 in this case:

So take a look to this page 119 with the command:

DBCC PAGE('TestGhostRows',1,119,1) WITH TABLERESULTS 

We can see that we have 1 ghost record (m_ghostRecCnt=1) and we can see that in reality our record is still in place!

 

Disabling the traceflag 661 the ghost record will disappear because it will be physically deleted from the ghost cleanup task:

DBCC TRACEOFF(661,-1); DBCC TRACEOFF(3604,-1);

That'all for today mates and stay tuned for the next post!

Luca

 

Help me to share knowledge on my blog  

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