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.

Note

A backup created on a newer version of SQL Server cannot be restored on an older version. For example, a backup from SQL Server 2019 cannot be restored on SQL Server 2017. However, you can transfer data between versions using alternative methods, such as generating SQL scripts or using import/export tools. To learn more, refer to the blog article.

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. Select a user database or expand the System Databases folder and select a system database.

3. Right-click the database and select Tasks > Restore. The Restore Database dialog 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 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 database name, enter the new name in the Database box.

6. In the Restore to box, leave the default as To the last backup taken or select Timeline to access the Backup Timeline dialog 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 shows the available backups for the selected location. By default, a recommended recovery plan is provided. To override the suggested recovery plan, change the selections in the grid. If a backup depends on an earlier one, it will automatically be clearer when the earlier backup is removed.

8. Optional: Go to the Files page to access the Browse Files dialog. 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, 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. It 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 and 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 chosen.

12. Restore operations may fail if there are active connections to the database. Select 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