Restore a database to a new location (SQL Server)

Applies to: SQL Server

This article describes how to restore a SQL Server database to a new location, and optionally rename the database in SQL Server, by using SQL Server Management Studio (SSMS) or Transact-SQL. You can move a database to a new directory path or create a copy of a database on either the same server instance or a different server instance.

Limitations

• The system administrator restoring a full database backup must be the only person currently using the database to be restored.

Prerequisites

• When you use the full or bulk-logged recovery model, before you can restore a database, you must back up the active transaction log. For more information, see Back up a transaction log.

• To restore an encrypted database, you must have access to the certificate or asymmetric key used to encrypt the database. Without that certificate or asymmetric key, you can't restore the database. You must retain the certificate used to encrypt the database encryption key for as long as you need the backup. For more information, see SQL Server certificates and asymmetric Keys.

Recommendations

• For other considerations for moving a database, see Copy databases with backup and restore.

• If you restore a SQL Server 2005 (9.x) or higher database to SQL Server, the database is automatically upgraded. Typically, the database becomes available immediately. However, if a SQL Server 2005 (9.x) database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes are unavailable during the upgrade. Depending on the amount of data being indexed, importing can take several hours, and rebuilding can take up to 10 times longer. Also, when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog isn't available. To change the setting of the upgrade_option server property, use sp_fulltext_service.

Security

For security purposes, we don't recommend that you attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might run unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Permissions

If the database being restored doesn't exist, the user must have CREATE DATABASE permissions to be able to run RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which isn't always the case when RESTORE is run, members of the db_owner fixed database role don't have RESTORE permissions.

Restore a database to a new location, and optionally rename the database, by using SSMS

1. Connect to the appropriate instance of the SQL Server Database Engine, and then, in Object Explorer, select the server name to expand the server tree.

2. Right-click Databases, and then select Restore Database.... The Restore Database dialog opens.

3. On the General page, in the Source section, 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 dropdown list. The list contains only databases that have been backed up according to the msdb backup history.

     Note

     If the backup is created from a different server, the destination server won't have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.

   • Device

     Select the browse (...) button to open the Select backup devices dialog. In the Backup media type box, select one of the listed device types. To select one or more devices for the Backup media box, select Add.

     After you add the devices you want to add to the Backup media list, select OK to return to the General page.

     In the Source: Device: Database list, select the name of the database that should be restored.

     Note

     This list is only available when Device is selected. Only databases that have backups on the selected device are available.

4. In the Destination 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.

5. In the Restore to box, leave the default of 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. See Backup timeline for more information on designating a specific point in time.

6. In 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. 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 deselected when the earlier backup is deselected.

   For information about the columns in the Backup sets to restore grid, see Restore Database (General page).

7. To specify the new location of the database files, select the Files page, and then select Relocate all files to folder. Provide a new location for the Data file folder and Log file folder. For more information about this grid, see Restore Database (Files Page).

8. On the Options page, adjust the options if you want to. For more information about these options, see Restore Database (Options Page).

Restore database to a new location, and optionally rename the database, by using T-SQL

1. Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore. This statement shows the basic syntax for returning a list of the database and log files contained in the backup set:

   RESTORE FILELISTONLY FROM backup_device WITH FILE = *backup_set_file_number
   

   Here, backup_set_file_number indicates the position of the backup in the media set. You can obtain the position of a backup set by using the RESTORE HEADERONLY statement. For more information, see Specifying a backup set.

   This statement also supports several WITH options. For more information, see RESTORE FILELISTONLY.

2. Use the RESTORE DATABASE statement to restore the full database backup. By default, data and log files are restored to their original locations. To relocate a database, use the MOVE option to relocate each of the database files, and to avoid collisions with existing files.

The basic Transact-SQL syntax for restoring the database to a new location with a new name is:

RESTORE DATABASE <new_database_name>
FROM <backup_device> [ , ...n ]
[ WITH
 {
    [ RECOVERY | NORECOVERY ]
    [ , ] [ FILE = { <backup_set_file_number> | @backup_set_file_number } ]
    [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ , ...n ]
} ]
[ ; ]

The following information describes arguments of this RESTORE statement that relate to restoring a database to a new location. For more information about these arguments, see RESTORE Statements.

new_database_name

The new name for the database.

backup_device [ , ...n ]

Specifies a comma-separated list of between 1 and 64 backup devices from which the database backup is to be restored. You can specify a physical backup device, or you can specify a corresponding logical backup device, if one is defined. To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } = physical_backup_device_name

For more information, see Backup devices.

{ RECOVERY | NORECOVERY }

If the database uses the full recovery model, you might need to apply transaction log backups after you restore the database. In this case, specify the NORECOVERY option.

Otherwise, use the RECOVERY option, which is the default.

FILE = { backup_set_file_number | @backup_set_file_number }

Identifies the backup set to be restored. For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium, and a backup_set_file_number of 2 indicates the second backup set. You can obtain the backup_set_file_number of a backup set by using the RESTORE statements - HEADERONLY statement.

When this option isn't specified, the default is to use the first backup set on the backup device.

For more information, see RESTORE arguments (Transact-SQL).

MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ , ...n ]

Specifies that the data or log file specified by logical_file_name_in_backup is to be restored to the location specified by operating_system_file_name. Specify a MOVE statement for every logical file you want to restore from the backup set to a new location.

Example (Transact-SQL)

This example creates a new database named MyAdvWorks by restoring a backup of the AdventureWorks2022 sample database, which includes two files: AdventureWorks2022_Data and AdventureWorks2022_Log. This database uses the simple recovery model. The AdventureWorks2022 database already exists on the server instance, so the files in the backup must be restored to a new location. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The database backup is the first backup set on the backup device.

USE master;
GO
-- First determine the number and names of the files in the backup.
-- AdventureWorks2022_Backup is the name of the backup device.
RESTORE FILELISTONLY
   FROM AdventureWorks2022_Backup;

-- Restore the files for MyAdvWorks.
RESTORE DATABASE MyAdvWorks
   FROM AdventureWorks2022_Backup
   WITH RECOVERY,
   MOVE 'AdventureWorks2022_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf',
   MOVE 'AdventureWorks2022_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf';
GO

For an example of how to create a full database backup of the AdventureWorks2022 database, see Create a full database backup.

Related tasks

• Create a full database backup
• Restore a database backup using SSMS
• Back up a transaction log
• Restore a transaction log backup (SQL Server)

Related content

• Manage Metadata When Making a Database Available on Another Server
• RESTORE Statements (Transact-SQL)
• Copy Databases with Backup and Restore