How to: Restore to a Point in Time (SQL Server Management Studio)
This topic is relevant only for databases that are using full or bulk-logged recovery mode.
To restore to a point in time
After you connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database.
Right-click the database, point to Tasks, and then click Restore.
Depending on whether you are restoring data backups or just transaction logs (for a database that is already in the restoring state), click either Database or Transaction Log.
On the General page, the name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box.
The location of the point-in-time option depends on whether you are restoring data backups or just transaction log backups:
- Restore Database: The To a point in time option is in the Destination for restore section.
- Restore Transaction Log: The Point in time option is in the Restore to section.
The default point in time is Most recent possible. To select a specific date and time, click the (...) browse button.
In the Point in Time Restore dialog box, click A specific date and time.
- In the Date list box, enter or select a date.
- In the Time list box, enter or select a time.
To specify the source and location of the backup sets to restore, click one of the following options:
- From database
Enter a database name in the list box. - From device
Click the (...) browse button. In the Specify Backup dialog box, select one of the listed device types in the Backup media list box. To select one or more devices for the Backup location list box, click Add.
After you add the devices you want to the Backup location list box, click OK to return to the General page.
- From database
After you have specified a specific point in time, only the backups that are required to restore to that point in time are selected in the Restore column of the Select the backup sets to restore grid. These selected backups make up the recommended restore plan for your point-in-time restore. You should use only the selected backups for your point-in-time restore operation.
For information about the columns in the Select the backup sets to restore grid, see Restore Database (General Page).
To view or select the advanced options, click Options in the Select a pagepane.
In the Restore options panel, you can choose any of the following options, if appropriate for your situation:
- Overwrite the existing database
- Preserve the replication settings
- Prompt before restoring each backup
- Restrict access to the restored database
For more information about these options, see Restore Database (Options Page).
Optionally, you can restore the database to a new location by specifying a new restore destination for each file in the Restore the database files as grid. For more information about this grid, see Restore Database (Options Page).
The Recovery state panel determines the state of the database after the restore operation. The default behavior is:
Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
Note
Choose this option only if you are restoring all of the necessary backups now.
Alternatively, you can choose either of the following options:
- Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
- Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)
For descriptions of the options, see Restore Database (Options Page).
See Also
Tasks
How to: Back Up a Transaction Log (SQL Server Management Studio)
How to: Restore a Transaction Log Backup (SQL Server Management Studio)
How to: Restore to a Point in Time (Transact-SQL)
Other Resources
SQL Server Management Studio Tutorial