Understanding Point in Time Recovery in SQL Server

Point In Time recovery is a process to recover or restore your database till the point it was lost. Sometimes detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued.

Related Articles:

• Understanding SQL Server Recovery Models
• Read Different Types of SQL Server Backups
• SQL Server Backup and Recovery Interview Questions
• How to get Last Restore Time of your database?

Point in Time Restore

1-This topic is relevant only for databases that are using full or bulk-logged recovery mode. Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.
2-Restore the last full database backup and, if any, the last differential database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
3-Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM WITH STOPAT=time, RECOVERY).
4-The basic syntax is:

RESTORE LOG database_name FROM WITH STOPAT =time, RECOVERY

T-SQL Code:

-- Restore the full database backup.
RESTORE DATABASE AdventureWorks
FROM disk='backup file location'
WITH NORECOVERY;
GO
-- Restore the required Log backup with STOPAT keyword.
RESTORE LOG AdventureWorks
FROM disk='backup file location'
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';
GO

I hope you like this article. Please follow our facebook page and Twitter handle to get latest updates.

• Author
• Recent Posts

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.

Follow me:

Latest posts by Manvendra Deo Singh (see all)

• How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
• How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
• How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023