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.
- 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
-- 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
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018