How to restore files to a new location (Transact-SQL)

How to Install SQL Server 2000

How To

How to restore files to a new location (Transact-SQL)

To restore files to a new location

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 name of the database to restore.

    • The backup device from where the database backup will be restored.

    • The MOVE clause for each file to restore to a new location.

    • The NORECOVERY clause. If the files have not been modified since the backup was created, specify the RECOVERY clause.
  3. If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
    • The name of the database to which the transaction log will be applied.

    • The backup device from where the transaction log backup will be restored.

    • The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.

      The transaction log backups, if applied, must cover the time when the files and filegroups were backed up.

Examples

This example restores two of the files for the MyNwind database that were originally located on the C:\ drive to new locations on the D: \drive. Two transaction logs will also be applied to restore the database to the current time. The RESTORE FILELISTONLY statement is used to determine the number and logical and physical names of the files in the database being restored.

USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
   FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH NORECOVERY,
   MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', 
   MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH RECOVERY
GO

See Also

RESTORE

Copying Databases