SQL Server: An example of Real Point In Time Recovery

Many a times due to incorrect query being fired from the developers or due to some abnormal behavior of the server, we are required to do a Point in Time Recovery. Point in Time Recovery means that we need to Recover the data to the point where the disaster happened. Let us consider the below case:

Consider we have a table named student present in the database named student on the server, it’s content is as shown below:

With 6 records in the table, at this point of time we have taken a Full Backup of the database. The name of the Full Backup is Student_Full_Backup_20120614.bak and is kept under the location named D:\Satnam on the server.

Once the Full Backup happens successfully, another record gets inserted into the table as shown below:

insert student
select 7,'Purvish','Shah'

After this you have a differential backup of the database being performed. The name of the differential backup is Student_Differential_Backup_20120614.bak and it resides under the path named D:\Satnam on the server.

Once the differential backup happens successfully 2 new records get inserted into the table as shown below:

insert student
select 8,'Khushboo','Jatav'

insert student
select 9,'Satish','Ughade'

Just after the above 2 records gets inserted into the table a developer fires a delete statement without a where clause as shown below:

delete from student

Once the above query got fired, the entire content of the table got deleted and at this point of time we need to recover all the data back to its original state.

Understanding the requirement, I decided to implement the below plan to Recover the entire data back.

• I first took the Transactional Log Backup of the database named student and kept it under the path named D:\Satnam on the server.
• I then started Restoring the Full Backup of the database named Student but with NORECOVERY mode so that I can restore additional backups over it. Restoring was done using the below T-SQL against the master database,

RESTORE DATABASE Student_Backup
FROM DISK='D:\Satnam\Student_Full_Backup_20120614.bak'
WITH MOVE 'student' TO 'D:\Satnam\student.mdf',
MOVE 'student_log' TO 'D:\Satnam\student_log.ldf',
NORECOVERY,STATS=10
GO

• I then started Restoring the Differential Backup of the database named Student over the existing Student_Backup database but with NORECOVERY mode so that I can restore additional backups over it. Restoring was done using the below T-SQL against the master database,

RESTORE DATABASE Student_Backup
FROM DISK='D:\Satnam\Student_Differential_Backup_20120614.bak'
WITH
NORECOVERY,STATS=10
GO

• Now Restore the latest Transactional Log Backup which we have taken in the First Step over the existing Student_Backup database but with RECOVERY option, Please note that the delete statement was fired at 10:32 PM on 14^th June 2012, so when I perform a restore I will explicitly force it to stop at 10:31 PM on 14^th June 2012, please refer the below T-SQL which will help you to achieve the same.

RESTORE LOG Student_Backup
FROM DISK='D:\Satnam\Student_Transactional_Log_20120614.trn'
WITH
RECOVERY,STATS=10,
STOPAT = 'June 14, 2012 10:31:00 PM'
GO

Once the above T-SQL got executed, I decided to check the contents of the student table in the student_backup database and the results obtained were as shown below:

I then transferred these records from the student_backup database to the original student database so that the original database has the Point in Time data with it; please refer the T-SQL below,

insert [student].[dbo].[student]
select * from [student_backup].[dbo].[student]

Now lets query the student table in the student database and review the data, please refer the screen capture below:

Isn’t it interesting, such situation can happen in Real World and the above solution will help you to come out of trouble.

Many Thanks to all the viewers for providing their valuable time in reading the article.Please do let us know in case if there is any better solution to this.

Regards

Satnam Singh

Like us on FaceBook  |  Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook