How to: Restore Files and Filegroups (Transact-SQL)
This topic explains how to restore files and filegroups.
Important
The system administrator restoring the files and filegroups must be the only person currently using the database to be restored.
To restore files and filegroups
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 NORECOVERY clause. If the files have not been modified after the backup was created, specify the RECOVERY clause.
Important
To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database cannot be restored. As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. For more information, see SQL Server Certificates and Asymmetric Keys.
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 until the end of log (unless ALL database files are restored).
Note
After you restore a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending on the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. To change the setting of the upgrade_option server property, use sp_fulltext_service.
Example
This example restores the files and filegroups for the MyNwind database. 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
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