Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Perform a database restore to a point in time

  1. Home
  3. Perform a database restore to a point in time

Go back to DP-300 Tutorials

In this tutorial we will learn how to perform a database restore to a point in time.

Using SQL Server Management Studio

To restore a database to a point in time

  • Firstly, in Object Explorer, connect to the appropriate instance of the SQL Server Database Engine, and expand the server tree.
  • Secondly, expand Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database.
  • Thirdly, right-click the database, point to Tasks, point to Restore, and then click Database.
  • Next, on the General page, use the Source section to specify the source and location of the backup sets to restore. Select one of the following options:

Database

Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history.

Device
  1. Firstly, click the browse (…) button to open the Select backup devices dialog box. In the Backup media type box, select one of the listed device types. To select one or more devices for the Backup media box, click Add.
  2. Secondly, after you add the devices you want to the Backup media list box, click OK to return to the General page.
  3. Next, in the Source: Device: Database list box, select the name of the database which should be restored.
  • After that, in the Destination section, the Database box automatically populate with the name of the database to restore. To change the name of the database, enter the new name in the Database box.
  • Then, click Timeline to access the Backup Timeline dialog box.
  • In the Restore to section, click Specific date and time.
  • Use either the Date and Time boxes or the slider bar to specify a specific date and time to where the restore should stop. Click OK.
  • After you have specified a specific point in time, the Database Recovery Advisor ensures that only backups that are required for restoring to that point in time are selected in the Restore column of the Backup sets to restore grid.
  • On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:
    • Overwrite the existing database (WITH REPLACE)
    • Preserve the replication settings (WITH KEEP_REPLICATION)
    • Restrict access to the restored database (WITH RESTRICTED_USER)
  • Select an option for the Recovery state box.
  • Take tail-log backup before restore will be selected if it is necessary for the point in time that you have selected. You do not need to modify this setting, but you can choose to backup the tail of the log even if it is not required.
Dp-300 practice tests

Using Transact-SQL

Before you begin

A specified time is always restored from a log backup. In every RESTORE LOG statement of the restore sequence, you must specify your target time or transaction in an identical STOPAT clause. As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time. That full database backup can be older than the most recent full database backup as long as you then restore every subsequent log backup, up to and including the log backup that contains your target point in time.

Basic Transact-SQL syntax

RESTORE LOG database_name FROM WITH STOPAT =time, RECOVERY…

To restore a database to a point in time

  • Firstly, connect to server instance on which you want to restore the database.
  • Secondly, execute the RESTORE DATABASE statement using the NORECOVERY option.
  • Thirdly, restore the last differential database backup, if any, without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
  • After that, 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).

Example (Transact-SQL)

The following example restores a database to its state as of 12:00 AM on April 15, 2020 and shows a restore operation that involves multiple log backups. On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).

RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=4, NORECOVERY, STOPAT = ‘Apr 15, 2020 12:00 AM’;

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=5, NORECOVERY, STOPAT = ‘Apr 15, 2020 12:00 AM’;
RESTORE DATABASE AdventureWorks WITH RECOVERY;
GO

Perform a database restore to a point in time DP-300 online course

Reference: Microsoft Documentation

Go back to DP-300 Tutorials

Menu