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

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 returns a list of the database and log files contained in the backup set. The basic syntax is as follows:

RESTORE FILELISTONLY FROM <backup_device> WITH FILE = backup_set_file_number

This statement also supports a number of WITH options. For more information, see RESTORE FILELISTONLY (Transact-SQL).

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 and 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 table describes arguments of this RESTORE statement in terms of restoring a database to a new location. For more information about these arguments, see RESTORE (Transact-SQL).

• new_database_name
  The new name for the database.

  Note

  If you are restoring the database to a different server instance, you can use the original database name instead of a new name.

• backup_device [ , ...n ]
  Specifies a comma-separated list of from 1 to 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 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 HEADERONLY statement.

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

  For more information, see "Specifying a Backup Set," in 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.

  Option	Description
  logical_file_name_in_backup	Specifies the logical name of a data or log file in the backup set. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created. Note To obtain a list of the logical files from the backup set, use RESTORE FILELISTONLY.
  operating_system_file_name	Specifies a new location for the file specified by logical_file_name_in_backup. The file will be restored to this location. Optionally, operating_system_file_name specifies a new file name for the restored file. This is necessary if you are creating a copy of an existing database on the same server instance.
  n	Is a placeholder indicating that you can specify additional MOVE statements.