Export (0) Print
Expand All

How to: Restore a Database to a New Location and Name (Transact-SQL)

Updated: 14 April 2006

This topic explains how to restore a database with a new location and, optionally, a new name.

ms190447.security(en-US,SQL.90).gifSecurity 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.

  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.
      ms190447.note(en-US,SQL.90).gifNote:
      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.
      ms190447.note(en-US,SQL.90).gifNote:
      To obtain a list of the logical files from the backup set, use RESTORE FILELISTONLY.

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.

ms190447.note(en-US,SQL.90).gifNote:
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).

ms190447.note(en-US,SQL.90).gifNote:
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

Community Additions

ADD
Show:
© 2014 Microsoft