Restore Transaction Logs for Point in Time Recovery

In this example we have taken FULL and Transaction log backups. In order to achieve the point in time recovery you need to restore initial the full backup with NORECOVERY and then you need to restore transaction logs (PointInTimeRecovery_LOG1.TRN & PointInTimeRecovery_LOG2.TRN) with NORECOVERY followed by the PointInTimeRecovery_TAIL_LOG.TRN WITH RECOVERY, STOPAT = ‘Oct 22, 2008 00:06:50 AM’. The different database recovery options which can be used while restoring the database are mentioned below.

NORECOVERY: When this option is used while restoring the database, you are informing the SQL Server that there are subsequent database restore files available which needs to be applied. In this state the database will not be available for user connections.

RECOVERY: When this option is used while restoring the database, you are informing the SQL Server that there are no subsequent database files available for restore. In this state the database will be available for user connections.

STANDBY: When this option is used while restoring the database, you are informing the SQL Server that there are subsequent database files available for restore. However, in this state the database will be available for user connections. The limitation is that before restoring the rest of the transaction logs all the user connections to the database should be disconnected. You can refer to the article titled “Obtain Exclusive Access to Restore a SQL Server Database“ to know more about disconnecting users connected to the database.

While performing the restore for a database, DBA first needs to restore the FULL backup with NORECOVERY option and then apply the latest differential backup if existing else you need to restore all the transaction logs in the order they were backed up after applying the latest differential backups with NORECOVERY option expect for the last transaction log which you need to restore with RECOVERY option. If differentials backups doesn’t exist then restore transaction logs in the same order of backups taken with NORECOVERY option expect for the last transaction log which you need to restore with RECOVERY option.

Restoring PointInTimeRecovery Database
Execute the below TSQL Scripts to restore the PointInTimeRecovery database to a point in time.

Use master
GO

/* Kill Connected Database Users to Get Exclusive Access For Database Restore*/
EXEC sp_KillDatabaseUsers ‘PointInTimeRecovery’
GO

/* Restore PointInTimeRecovery Database Using Full Database Backup */
RESTORE FILELISTONLY
FROM DISK = N’C:DatabaseBackupsPointInTimeRecovery_FULL.BAK’
GO
RESTORE DATABASE [PointInTimeRecovery]
FROM  DISK = N’C:DatabaseBackupsPointInTimeRecovery_FULL.BAK’
WITH   
MOVE N’PointInTimeRecovery’ TO N’C:DatabaseFilesPointInTimeRecovery.mdf’, 
MOVE N’PointInTimeRecovery_Log’ TO N’C:DatabaseFilesPointInTimeRecovery_Log.ldf’, 
NORECOVERY, NOUNLOAD,  REPLACE,  STATS = 20
GO

/* Restore PointInTimeRecovery Database Using The First Transaction Log */
RESTORE LOG  [PointInTimeRecovery]
FROM  DISK = N’C:DatabaseBackupsPointInTimeRecovery_LOG1.TRN’
WITH  NORECOVERY,  STATS = 20
GO

/* Restore PointInTimeRecovery Database Using The Second Transaction Log */
RESTORE LOG  [PointInTimeRecovery]
FROM  DISK = N’C:DatabaseBackupsPointInTimeRecovery_LOG2.TRN’
WITH  NORECOVERY,  STATS = 20
GO

/* Restore PointInTimeRecovery Database Using Tail Log */
RESTORE LOG [PointInTimeRecovery]
FROM  DISK = N’C:DatabaseBackupsPointInTimeRecovery_TAIL_LOG.TRN’
WITH RECOVERY, STOPAT = ‘Oct 22, 2008 00:06:50 AM’
GO

Once the restoration process has completed successfully you will be able to see that data in the Product table in PointInTimeRecovery database.  Execute the below TSQL script to verify the existence of Product table and the availability of records in the table.

USE [PointInTimeRecovery]
GO
SELECT * FROM PRODUCT
GO

 

From the above snippet you can see that all the 200 rows which were inserted into the Product table are available once we performed the point in time recovery using the transaction logs.

Conclusion
Restoring a SQL Server database to achieve a point in time recovery is a tedious job for a Database Administrator especially when the disaster time is not exactly known. You might end up doing the entire process many a times to actually come to a conclusion which could be the best known timestamp against which you can finally recover the database. A point in time restore of a database will allow you to stop the database restore just prior to the point in time that your database became corrupted. In order to achieve a point in time recovery your database should be in FULL recovery model.

]]>

Leave a comment

Your email address will not be published.