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.

Security Note:

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.

To restore a database with a new name

1. Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the full database backup.

2. 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.

Example

This example creates a new database named MyAdvWorks. MyAdvWorks is a copy of the existing AdventureWorks database that includes two files: AdventureWorks_Data and 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.

Note

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).

Note

The examples of backing up and restoring the transaction log, including point-in-time restores, use the MyAdvWorks_FullRM database that is created from AdventureWorks just like the following MyAdvWorks example. However, the resulting MyAdvWorks_FullRM database must be changed to use the full recovery model: ALTER DATABASE MyAdvWorks_FullRM SET RECOVERY FULL.

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

See Also

Concepts

Managing Metadata When Making a Database Available on Another Server Instance
Security Considerations for Backup and Restore
Copying Databases with Backup and Restore

Other Resources

RESTORE (Transact-SQL)
SQL Server Management Studio Tutorial

Help and Information

Getting SQL Server 2005 Assistance