How to restore a database with a new name (Transact-SQL)

To restore a database with a new name

Important The system administrator restoring the files must be the only person currently using the database to be restored.

1. Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the database backup.
2. Execute the RESTORE DATABASE statement to restore the database backup, specifying:
   • The new name for the database.
   • The backup device from where the database backup will be 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 necessary. In this case, the database files for the original database already exist, and so different file names need to be specified when the database copy is created during the restore operation.

Examples

This example creates a new database called MyNwind2_Test. MyNwind2_Test is a copy of the existing MyNwind2 database that comprises two files: MyNwind2_data and MyNwind2_log. Because the MyNwind2 database already exists, the files in the backup need to 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.

USE master

GO

-- First determine the number and names of the files in the backup.

-- MyNwind_2 is the name of the backup device.

RESTORE FILELISTONLY

    FROM MyNwind_2

-- Restore the files for MyNwind2_Test.

RESTORE DATABASE MyNwind2_Test

    FROM MyNwind_2

    WITH RECOVERY,

    MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',

    MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'

GO

See Also