How to recover deleted records of SQL Server without updated backup?

Have your colleague or partner deleted some of the records of your SQL server table? He has deleted their corresponding records from the audit log too. Are you searching an effective method for recovery of records? I am assuring you that you have reached at the right blog. Here we will discuss about recovery of deleted records in SQL server table.

Prerequisites for recovery of records:

• If your database was in full recovery model then recovery is possible otherwise not.
• Database backup prior to the deletion of the records.
• Transaction log backup of the database, if you have not taken any transaction log backup of your database then make transaction log backup of the database now.

Steps to recover deleted records: Follow the below steps to recover your deleted records:

• Make the backup of transaction log where records have been deleted and the script will be

BACKUP LOG yourdatabasename TO DISK = 'some path';

• Restore your database backup (prior to the deletion of records) to a test environment with different name & NORECOVERY option and the script will be

RESTORE DATABASE yourdatabasename FROM DISK = 'some path to full' WITH NORECOVERY;

• Now restore the transaction log using STOPAT and the script will be...

RESTORE DATABASE dbname FROM DISK = 'some path to transaction log' WITH RECOVERY STOPAT 'August 15, 2012 00:00:00.000';

• Check your records

• Now take the backup of your database WITH RECOVERY option and the script will be...

RESTORE DATABASE yourdatabasename WITH RECOVERY;

Example: For easy understanding of all above steps, I want to mention an example and the example is written below:

RESTORE DATABASE employee-records from DISK='E:\BackupFiles\employee-records.bak'

WITH NORECOVERY;

RESTORE LOG employee-records FROM DISK='E:\BackupFiles\employee-records.trn'

WITH NORECOVERY, STOPAT = 'August 15, 2012 04:00 PM';

RESTORE DATABASE employee-records WITH RECOVERY; 

Where database name is employee-records & location of the database is E drive

Note: Not forget to backup your transaction log as well as database.

Don’t have any prior backup before the deletion: It is recommended to try any data recovery software for all those database administrators who have not maintained any backup before the deletion.