Restoring one File Group

Today, I came across a situation where I had to explain the FILEGROUP restore of SQL Server. Since I have not done this for a long time, I brushed my knowledge with Preethi's help and thought to blog it too. If you have a database that has more than one file group, you may face to a situation where you need to restore only one file group, without restoring the entire database, saving time. One thing we have to remember is, secondary file group (or file) restore needs Primary file group restore. If your Primary file group contains a lot of tables, again, it takes time for restore operation (Usually, database that contains secondary file groups does not use primary for user-defined tables). So, make sure that you do not use Primary file group for your tables. Here is an example: Assume that we have a database that has three file group: Primary, FG1, and FG2. In order to restore file groups, you should have backups of all file groups (both full and differential are possible). Here are steps for restoring a file group (FG1); -- Backup tail-log if require BACKUP LOG MyDatabase TO DISK = 'D:\Backups\TailLog.bak' WITH NO_TRUNCATE, NORECOVERY -- Restore the Primary file group RESTORE DATABASE MyDatabase FILEGROUP = 'Primary' TO DISK = 'D:\Backups\MyDatabase_FG1.bak' WITH NORECOVERY -- Restore the FG1 file group RESTORE DATABASE MyDatabase FILEGROUP = 'FG1' TO DISK = 'D:\Backups\MyDatabase_FG1.bak' WITH NORECOVERY -- Restore the log (if need, can be restored to a specific time) RESTORE LOG MyDatabase FROM DISK = 'D:\Backups\TailLog.bak' WITH RECOVERY