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
- Start the SQL Server Management Studio and connect to the appropriate instance of the SQL Server Database engine.
- Expand Databases, select user database or system database.
- Right click on the database > move the cursor to Tasks and click on Restore.
- Click on File and Filegroups > Restore Files and Filegroups will appear.
- In General page > To database list box > enter the new database or choose an existing database. But you can not restore master and tempdb.
- 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.
- Now select the backups to restore from Select the backup sets to restore grid.
- In the Select a page pane click Options to view or select the advanced options.
- Choose the appropriate options from the Restore options panel. a. Restore as file group
- You can choose a new location to restore the database from Restore database files as grid.
- After the restore operation you can determine the state of the database from Recovery state panel . a. RESTORE WITH RECOVERY
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.
b. Overwrite the existing database
c. Prompt before restoring each backup
d. Restrict access to the restored database
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.
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'
First transaction log backup
RESTORE LOG MyDatabase
Last transaction log backup
RESTORE LOG MyDatabase
Limitations of Restore File and Filegroup in SQL Server
- The person who is performing the restore operation of the file and filegroups must be a system administrator.
- Implicit and Explicit transaction don’t allowed the restore process of file and filegroups.
- If you are using the simple recovery model, then it is recommended that the file must be in read-only filegroup.
- 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.
- 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.
In this article we have discussed how to restore file and filegroups in SQL Server and understand the limitation of restoring process.