How to take PARTIAL or Filegroup backup? How to perform restore of PARTIAL backup file?

In SQL Server we have an option of taking backup of only certain part of the database, something like if we have a database which has multiple file groups and we want to take backup of only a single FileGroup that can be achieved. It is termed as Filegroup backup or Partial backup.

This kind of backups are needed in situations like if we have a large table in separate file group of production database and that table is not required to be available in test environment, so to save space in test environment and to reduce backup/restore times we can only take the backup of required file group and restore it to test environment.

FILEGROUP BACKUP:

BACKUP DATABASE AdventureWorks2012

FILEGROUP = 'PRIMARY'

TO DISK = 'C:\SQL2K8\Backups\AdventureWorks2012.bak'

WITH INIT,COMPRESSION

The backup command is almost same as normal backup command it will have section named ‘FILEGROUP’, in this section we need to mention the file group name which we want to backup. With this command SQL engine will back up only the file group name we specify, in above case only PRIMARY file group will be backed up.

PARTIAL RESTORE:

RESTORE DATABASE AdventureWorks2012_Test

FROM DISK = 'C:\SQL2K8\Backups\AdventureWorks2012.bak'

WITH PARTIAL,

MOVE 'AdventureWorks2012_Data' TO 'C:\SQL2K8\Backups\AdventureWorks2012_Data.mdf',

MOVE 'AdventureWorks2012_Log' TO 'C:\SQL2K8\Backups\AdventureWorks2012_log.ldf',

RECOVERY,REPLACE,STATS=10

While restoring the PARTIAL backups the only difference from normal restore command is we need to specify the option ‘PARTIAL’ in the restore command and rest of the command is same.

Points To Remember:

·        Database should be in FULL recovery in order to perform PARTIAL backup.

·        In partial restore one thing to note is, if the database has 2 file groups like PRIMARY and SECONDARY and we restored a PARTIAL backup which has backup of only PRIMARY file group, after restore through GUI we can still see the SECONDARY file group which was non-existent in backup file we used. But if we try to query the objects of that FG it throws error. Explained in below captures. So, even though we can see the FG through GUI it doesn't exist physically.

Performed a PARTIAL restore as shown in above capture using backup file which has only PRIMARY file group backup.

After restore when I verified properties of database it still shows SECONDARY FG.

Now I try to query the ‘SalesOrderDetail’ table which resides in SECONDARY FG, as the FG is not part of the partial backup we used the query throws below error:

Thanks!!