Recover Data from Delete or Truncate Table without a Backup File


As a last resort, you may be able to recover your deleted data or truncated data from intact log file.
It is very unlikely for someone who knows how to recover data without a backup in their own work.
But the point is that SQL Server tracking all operations in its log file and as long as the log file is intact,
the real thing is to have the knowledge to get the data back.
The techniques are using undocumented functions: fn_dblog and DBCC PAGE to get access to the raw data inside the log file.
It takes extra efforts to retrieve the raw data back.
If you have full backup and log backups, the process can be a little simpler.
This blog by itself may not help you directly but it points to a few resources to get your hand dirty.
If you really want to do it now, download Mr. Imran’s scripts and give them a try.

I have listed a few pointers incase you are interested in this process.
1. Paul Randal has posted detailed techniques and inside knowledge about the internals of log file.
http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

2. Mladen Prajdić has a good write up in this topic with code samples:
http://weblogs.sqlteam.com/mladenp/archive/2010/10/12/sql-server-ndash-undelete-a-table-and-restore-a-single.aspx

3. Muhammad Imran posted two stored procedures to recover these lost data.
Recover_Deleted_Data_Proc:
http://raresql.wordpress.com/2011/10/
Recover_Truncated_Data_Proc
http://raresql.wordpress.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/
To get truncated data back in SQL Server 2014, you need to modify the code a little since the underneath data format changes.

In order to make these operations work, the codition is to be able to read intact log file.

In Full recover mode, most likely you have all your log chains since you don’t have a log backup. You can go ahead to try these two stored procedures posted by Mr. Imran.
If your database is in Simple Recover Mode, you may or may not be successful. If your log file is big enough and ther is no checkpoints run (before log clear),
you may be able to get your data back and you are lucky.

This excercise helps to get a peek inside SQL Server log file and to get a better understanding related to SQL Server log file management.
The normal route to make sure we can safeguard our data is to implement a robust disaster recovery solution with good backups in a non-broken chain.
It should be handy to get lost data back more efficiently.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s