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:

1_An_example_of_Real_Point_In_Time_Recovery

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 14th June 2012, so when I perform a restore I will explicitly force it to stop at 10:31 PM on 14th 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:

2_An_example_of_Real_Point_In_Time_Recovery

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:

3_An_example_of_Real_Point_In_Time_Recovery

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

6 Comments on “SQL Server: An example of Real Point In Time Recovery”

  1. The SQL standard names are LOWER and UPPER, not LCASE and UCASE. Some prctuods like MySQL alias LCASE and UCASE to the LOWER and UPPER functions for increased compatibility with other non-standard prctuods and some prctuods that are not databases. MS Access uses LCASE and UCASE as does the non-database prctuods Excel and OOCalc. There are some programming languages which use LCASE and UCASE. There may be other DB prctuods that do not use the SQL standard LOWER/UPPER names for these functions. Oracle does use LOWER/UPPER. DB2 supports both. PostgreSQL uses LOWER/UPPER.

  2. This is a well known feature, but how about discussing when you don’t know the exact time and user says maybe an hour ago I did it? How would someone go about finding when it happened? What if it was corruption, how would you find the time? These are more typical answers that are needed.

Leave a Reply to MSSQL DBA Cancel reply

Your email address will not be published. Required fields are marked *