Recover deleted SQL Server data with the Transaction Log and LSNs

Recover deleted SQL Server data with the Transaction Log and LSNsThe idea that SQL Server keeps track of deleted data, so you can recover deleted data and objects using the transaction logs and LSNs is one that has been discussed multiple times. I wrote about this here. There is also an article by Manvendra Singh on this subject, showing examples.

If you know when your data was deleted, then it is not as challenging a task to recover the data.  However, if you are not aware of the date and time when a particular data was deleted, then we will find out in a similar way as we determined who deleted data from the database in my last tip. Here we will first find the exact LSN under which the delete statement ran and then we will recover data until that LSN.

As per SQL Server Books Online “Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.”

Recover deleted SQL Server data with the Transaction Log and LSNs

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