Restoring SQL Files or Filegroups

After a system failure, you can restore files or filegroups from database backups. File or filegroup restores are commonly used to restore damaged individual data files on a database. By default, files or filegroups are restored on the same location from where they were backed up.

Use any of the following methods to restore files or filegroups:

• Restore one or more files in a database.

• Restore all the files or filegroups in a database.

• If the filegroup size is large, partially restore files or filegroups in a database in stages. This is known as a Piecemeal Restore. For SQL Server 2005 and later versions, you must perform Piecemeal Restores.

  Note

  When you run a piecemeal restore to a destination where the database does not exist, the restore operation may complete with an error message. In such cases, run the piecemeal restore with the Norecovery option. For more information, see KB article 64250.

To restore a single file, the application restores both the latest full and differential backups and the transaction log backups up to the specified restore time. To restore multiple files or filegroups, the application restores the latest full and differential backups for each file and the transaction log backups up to the oldest log required by a file.

This feature applies to user-defined databases that have a full or bulk logged model. You cannot restore multiple databases at the filegroup level.

Best Practice: Back up the transaction logs regularly because files or filegroups restores cannot run without the transaction logs.

Procedure

1. From the CommCell Browser, click Client Computers > client > SQL Server.

2. Right-click the instance, and then click All Tasks > Browse and Restore.

3. In the Browse and Restore Options dialog box, click Advanced Restore, then click the Advanced Options tab.

4. Select File/File Group, and then click View Content.

5. In the Browse dialog box, specify how you want to restore the files or filegroups:

   • To restore one or more files or filegroups, select the files that you want to restore:

     1. In the left pane, select the databases that contains the files or filegroups you want to restore.

     2. In the right pane, select one or more files or filegroups you want to restore.

     3. Click Recover All Selected.

   • To restore all the files or filegroups in a database, select the database that you want to restore:

     1. In the left pane, select the database that contains the files or filegroups you want to restore.

     2. Click Recover All Selected.

   • To restore a database in stages, perform a Piecemeal Restore:

     1. In the left pane, select the databases that contains the files or filegroups you want to restore.

     2. In the right pane, select one or more files or filegroups you want to restore.

     3. Click Recover All Selected.

     4. Click Advanced.

     5. In the SQL Restore Options dialog box, click Advanced.

     6. In the Advanced Restore Options dialog box, click the Options tab.

     7. Select the Partial Restore check box, and then click OK.

     To restore the entire database, repeat the above steps one at a time, to perform a partial restore of each filegroup.

6. To restore the database to the same location where it was backed up, in the SQL Restore Options dialog box, select the Unconditionally overwrite existing database or files check box.

   The existing database files are overwritten.

7. Click OK.