How to: Restore a Differential Database Backup (SQL Server Management Studio)
This topic explains how to restore a differential database backup.
Important
Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio).
To restore a differential database backup
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.
Click Database, which opens the Restore Database dialog box.
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.
In the To a point in time text box, either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box. For more information, see How to: Restore to a Point in Time (SQL Server Management Studio).
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, which opens the Specify Backup dialog box. In the Backup media list box, select one of the listed device types. 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
In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. When one or more differential database backups exist, the default recovery plan includes the following:
- The most recent full database backup.
- The most recent differential database backup.
- If the database uses the full or bulk-logged recovery model, the log backups taken since the most recent differential database backup.
To override the suggested recovery plan, you can change the selections in the grid; for example, if the most recent differential backup is problematic, you can select a previous differential backup instead of the most recent one (and the subsequent transaction logs, if any). Any backups that depend on a deselected backup are deselected automatically.
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 page pane.
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 to 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: Create a Differential Database Backup (SQL Server Management Studio)
How to: Restore to a Point in Time (SQL Server Management Studio)
How to: Restore a Transaction Log Backup (SQL Server Management Studio)
Other Resources
SQL Server Management Studio Tutorial