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.

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

Concepts

Other Resources

Help and Information