How to: Restore Files and Filegroups over Existing Files (Transact-SQL)
This topic explains how to restore files and filegroups over existing files.
The system administrator who is restoring the files and filegroups must be the only person currently using the database to be restored.
To restore files and filegroups over existing files
Execute the RESTORE DATABASE statement to restore the file and filegroup backup, specifying:
The name of the database to restore.
The backup device from where the full database backup will be restored.
The FILE clause for each file to restore.
The FILEGROUP clause for each filegroup to restore.
The REPLACE option to specify that each file can be restored over existing files of the same name and location.
Use the REPLACE option cautiously. For more information, see Using the REPLACE Option.
The NORECOVERY option. If the files have not been modified after the backup was created, specify the RECOVERY clause.
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.
The following example restores the files and filegroups for the
MyNwind database, and replaces any existing files of the same name. Two transaction logs will also be applied to restore the database to the current time.
USE master GO -- Restore the files and filesgroups for MyNwind. RESTORE DATABASE MyNwind FILE = 'MyNwind_data_1', FILEGROUP = 'new_customers', FILE = 'MyNwind_data_2', FILEGROUP = 'first_qtr_sales' FROM MyNwind_1 WITH NORECOVERY, REPLACE 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