Restore SQL Server Database to the Point In Time - A Right Way

Problem:

“A detrimental command was issued against my database due to which I lost my data. Now, I want to restore my data back especially last week data. So, please guide me, if there is any reliable method using which I can restore my last week data back immediately.”

Solution:

Many SQL Server users may face the above-stated issue. One option is provided by SQL Server that is the ability to do point in time restore of user’s data in order to restore the database back to the point right before that detrimental command was issued. When a user wants to recover his/her data prior to the transaction that caused the problem, then restore to any point in time works best. Thus, in this post, we are going to discuss how to restore a database to a point in time in SQL Server by using Server Management Studio.

Steps to Restore SQL Server Database to a Point in Time

One has to follow the steps given below to restore SQL Server database to a point in time using SQL Server Management Studio.

• Connect to the suitable instance of the SQL Server Database Engine in Object Explorer and expand the server tree
• Now, depending on the database, select a user database or expand System Databases
• Then select a system database and right-click on the database, point to Tasks, point to Restore and then click Database
• Now, specify the source and location of the backup sets to restore in the Source section via going to General page
• Select one the following options given below:

Databases: Click on the drop-down list and select the database to be restored. Databases that have been backed up according to the MSDB backup history are present in the list.

Device: Now, to open the Select backup devices dialog box, click on the browse button (…). Select one of the listed device types, in the Backup media type box. To select one or more devices for the Backup media box, click on Add. Once you have added the devices to the Backup media list box, click OK and return to General page.

Note: This list is only available when the Device is selected.

• The Database box is populated with the name of the database to be restored in the Destination section. Change the name of the database, by entering the new name in Database box.
• To access Backup Timeline dialog box, click on Timeline
• In the Restore To section, click on the Specific date and time
• Now, use the Date and Time boxes or the slider bar to specify a particular date and time to where the restore should stop. Click on OK button

Note: To change the amount of time displayed, use the Timeline Interval box.

• Once you have stated a specific point in time then, Database Recovery Advisor makes sure the backups of that specific point, which is selected in Restore column of Backup sets to restore grid. These nominated backups make up the recommended restore plan for your point-in-time restore.
• Now, on the Options page, in the Restore options panel, multiple options are given so you can select any of the following options according to your situation:
  • Overwrite the existing database
  • Preserve the replication settings
  • Restrict access to the restored database
• Now, for the Recovery State box, select one of the options given below:
  • Restore With Recovery: Option that leaves the database ready for use by rolling back the uncommitted transactions.
  • Restore With Non-Recovery: This leaves the database non-functional and does not roll back the uncommitted transactions.
  • Restore With Standby: This leaves the database in read-only mode. It undoes uncommitted transactions.
• Now, if it is important for the point in time that you have selected, then select a Take a tail-log backup before restore option
• Make sure that all active connections between Management Studio and the database are closed. Sometimes, restore operation fails due to active connection to the database. Just check the Close existing connections option
• If you want to be prompted between each restore operation, then select Prompt before restoring each backup option

Alternate Solution to Restore SQL Server Database to a Point in Time

The user can also go for an alternate solution that is SQL Log Reader to restore SQL Server database to a point in time. This tool also provides Date Filter option using which user can filter data according to a specific date. Overall, this utility analyzes SQL log file transactions and quickly perform SQL LDF file recovery in a secure and sophisticated way.

Conclusion

After reading this post, users will be able to recover data via using SQL Server Point in Time Recovery. An alternate solution is also given, it totally depends on the user which solution he/she want to opt to perform this operation.