Contents

How to restore a SQL Server database to a new server

This topic describes how to restore a database backup by using dbForge Studio for SQL Server. You can use this feature to copy your database from one server to another one.

To restore a database:

1. After connecting to a required instance of the Microsoft SQL Server Database Engine, in Database Explorer, click the server name to expand the server tree.

2. Either select a user database or expand the System Databases folder and select a system database.

3. Right-click the database, point to Tasks, and then click Restore. The Restore Database dialog box appears.

4. On the Source and Target page, use the Source for restore 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.
• Files
  Select to specify a path to a backup file.

5. In the Destination to restore section, the Database box is automatically populated with the name of the database to be restored. To change the name of the database, enter the new name in the Database box.

6. In the Restore to box, leave the default as To the last backup taken or click on Timeline to access the Backup Timeline dialog box to manually select a point in time to stop the recovery action.

7. In the Backup set 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. To override the suggested recovery plan, you can change the selections in the grid. Backups that depend on the restoration of an earlier backup are automatically cleared when the earlier backup is cleared.

8. Optionally, click the Files page to access the Browse Files dialog box. From here, 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.

9. To view or select the advanced options, on the Options page, in the Restore options pane, 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)

10. Select an option for the Recovery state box. This box determines the state of the database after the restore operation.

• RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
• RESTORE WITH NORECOVERY leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
• RESTORE WITH STANDBY leaves the database in the read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

11. Take tail-log backup before restore will be selected if it is necessary for the point in time that you have selected.

12. If there are active connections to the database, restore operations may fail Check the Close existing connections to destination database option to ensure that all active connections between dbForge Studio for SQL Server and the database are closed.

13. Click Restore.

Download dbForge Studio for SQL Server and try it absolutely free for 30 days!

Want to Find out More?

• Previous
• Next