How to: Restore a Database to a New Location and Name (Transact-SQL)
This topic explains how to restore a database with a new location and, optionally, a new name.
|We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute 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.|
Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the full database backup.
Execute the RESTORE DATABASE statement to restore the full database backup, specifying:
The new name for the database.
Note: If you are restoring the database to a different server instance, you can choose to use the original name instead a new name.
The backup device from where the full database backup is restored.
The NORECOVERY clause if you have transaction log backups to apply after the file backups are restored. Otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files were backed up.
The MOVE clause for each file to restore to a new location if the file names already exist. For example, creating a copy of an existing database on the same server for testing purposes may be required. In this case, the database files for the original database already exist, and so different file names must be specified when the database copy is created during the restore operation.
The syntax for the MOVE clause is as follows:
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
[ ,...n ] [ , ]
Here logical_file_name_in_backup is the name of a data or log file in the backup set, and operating_system_file_name is the location to which the file should be restored. n is a placeholder indicating that you can specify additional MOVE statements. Specify a MOVE statement for every logical file you want to restore from the backup set to a new location.
Note: To obtain a list of the logical files from the backup set, use RESTORE FILELISTONLY.
- The new name for the database.
This example creates a new database named
MyAdvWorks is a copy of the existing
AdventureWorks database that includes two files:
AdventureWorks_Log. Because the
AdventureWorks database already exists, the files in the backup must be moved during the restore operation. The
RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored.
|For an example of how to create a full database backup of the AdventureWorks database, see How to: Create a Full Database Backup (Transact-SQL).|
The examples of backing up and restoring the transaction log, including point-in-time restores, use the |
USE master GO -- First determine the number and names of the files in the backup. -- AdventureWorks_Backup is the name of the backup device. RESTORE FILELISTONLY FROM AdventureWorks_Backup -- Restore the files for MyAdvWorks. RESTORE DATABASE MyAdvWorks FROM AdventureWorks_Backup WITH RECOVERY, MOVE 'AdventureWorks_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', MOVE 'AdventureWorks_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf' GO
ConceptsManaging Metadata When Making a Database Available on Another Server Instance
Security Considerations for Backup and Restore
Copying Databases with Backup and Restore
Other ResourcesRESTORE (Transact-SQL)
SQL Server Management Studio Tutorial