Restore Files and Filegroups in SQL Server from Backup

In the previous article we have discussed about SQL Server database files and filegroups and how to backup the file and filegroups. In this article, we will discuss how to restore files and filegroups in SQL Server.

Restore File and Filegroup in SQL Server

Here we will discuss how to restore file and filegorup in SQL Server using SQL Server Management Studio and Transact-SQL

Via SQL Server Management Studio

1. Start the SQL Server Management Studio and connect to the appropriate instance of the SQL Server Database engine.
2. Expand Databases, select user database or system database.
3. Right click on the database > move the cursor to Tasks and click on Restore.
4. Click on File and Filegroups > Restore Files and Filegroups will appear.
5. In General page > To database list box > enter the new database or choose an existing database. But you can not restore master and tempdb.
6. Determine the source and location of the backup sets to restore a file or filegroups
      a. From Database
    In the list box enter the database name.
     b. From Device
    Click on the Browse button > select the device types in backup media type. Click Add to select more than one device for the Backup media list.
7. Now select the backups to restore from Select the backup sets to restore grid.
8. In the Select a page pane click Options to view or select the advanced options.
9. Choose the appropriate options from the Restore options panel.
    a. Restore as file group
    b. Overwrite the existing database
    c. Prompt before restoring each backup
    d. Restrict access to the restored database
10. You can choose a new location to restore the database from Restore database files as grid.
11. After the restore operation you can determine the state of the database from Recovery state panel .
    a. RESTORE WITH RECOVERY
    This is the default option. This option is similar to the WITH RECOVERY in a T-SQL restore statement.
    b. RESTORE WITH NORECOVERY
    This option is similar to specifying WITH NORECOVERY in a T-SQL restore statement.
    c. RESTORE WITH STANDBY
    After choosing this option the database go to the standby state. This option is similar to the T-SQL RESTORE WITH STANDBY statement.
    d. Rollback Undo File
    This option leave the database in read-only mode.

Via Transact-SQL

To restore file and filegroup in SQL Server using T-SQL execute the below queries.

Restore the files and filesgroups

RESTORE DATABASE MyDatabase
      FILE = 'MyDatabase_data_1',
      FILEGROUP = 'new_customers',
      FILE = 'MyDatabase_data_2',
      FILEGROUP = 'first_qtr_sales'
      FROM MyDatabase_1
      WITH NORECOVERY;
    GO

First transaction log backup

RESTORE LOG MyDatabase
      FROM MyDatabase_log1
      WITH NORECOVERY;
    GO

Last transaction log backup

RESTORE LOG MyDatabase
      FROM MyDatabase_log2
      WITH RECOVERY;
    GO

Limitations of Restore File and Filegroup in SQL Server

1. The person who is performing the restore operation of the file and filegroups must be a system administrator.
2. Implicit and Explicit transaction don’t allowed the restore process of file and filegroups.
3. If you are using the simple recovery model, then it is recommended that the file must be in read-only filegroup.
4. If you are performing the restore operation in full or bulk recovery model it is highly recommended that you must back up the active transaction log.
5. If you restoring a database that is in encrypted state and you don’t have certificate or asymetric key, then you can’t perform the restore operation on file and filegroups.

Conclusion

In this article we have discussed how to restore file and filegroups in SQL Server and understand the limitation of restoring process.