Piecemeal restore only one filegroup without primary filegroup restore

Question

I have a db in SIMPLE recovery model, and regularly take FULL,DIFF backups.

This db also have filegroups for each month. Each filegroup has exactly one NDF file.

Like this:

FileGroup: PRIMARY
File: Primary.mdf

FileGroup: FG201801
File: 201801.ndf

FileGroup: FG201802
File: 201802.ndf

FileGroup: FG201803
File: 201803.ndf

etc

My goal would be twofold:

1. Be able to take backup per partition level. As I read its only possible when I mark the filegroup readonly. So i have separated partial backup BAK files. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server

2. Second goal would be (my question is here), to be able to restore only one filegroup, without restoring the PRIMARY filegroup or touching any other filegroup.

Is it even possible?

As understood, if i want to restore for let's say FG201802 only, leaving PRIMARY and the others untouched, then first i have to restore the FULL backup containing the PRIMARY filegroup, then I can restore my partial backup of FG201802. How could I do the restore of FG201802 without restoring PRIMARY?

Could anybody point me to an online resource where this is demonstrated? All article on the net (which i found) always starts to restore the PRIMARY full backup then applying the rest of the partial backups one by one.

I wish only restore a partial backup, how could it be done?

Thank you!

Answer 1

You can't restore a file or filegroup without restoring the PRIMARY filegroup. There are a number of reasons for this given your scenario:

1. Under SIMPLE mode, a Partial Backup always contains the PRIMARY filegroup and all read/write filegroups. You would need to switch to FULL recovery to utilise file/filegroup only backups that can exclude the PRIMARY.
2. If no PRIMARY filegroup existed, the system tables and metadata for your database would not exist, so no database users, no schema information, nothing that SQL Server needs to know about your data, files and filegroups would be available.
3. Under SIMPLE, no log backups are taken, so you cannot replay the transactions against the non-PRIMARY read/write filegroups meaning they must be restored from a backup consistent with PRIMARY.

Even under FULL recovery, the primary filegroup must be restored to allow you to restore individual files/filegroups because all the core metadata about your database is there.

The idea of Partial Backups and Piecemeal restores is to allow you to get back online in a disaster quicker by restoring the essential filegroups needed for the database to be operational, and you can restore additional filegroups later.

I would suggest you look at making some changes to your database:

1. Move all user objects and data out of the PRIMARY filegroup so that restoring it is less time consuming.
2. Switch to FULL recovery mode and implement separate filegroup backups and frequent log backups.
3. Mark any filegroups that should not be updatable as READ ONLY.

These change will allow you to simplify your restore process. Assuming you'd made the above changes and wanted to restore the file you've mentioned, the process would be:

1. Take a tail-log backup
2. Restore the primary filegroup from backup
3. Restore all log files with no recovery (except the last restore of the tail log backup, which is done with recovery) (DB is online at this point)
4. Run "RECOVER" on all the undamaged filegroups (Data in these FGs is now accessible)
5. Restore the damaged filegroup from backup (DB is now fully recovered)

This would be quicker than partial backups under SIMPLE recovery because the undamaged filegroups do not have to get restored from backup, instead the data files on disk are simply recovered by SQL Server and transactions replayed from the log onto those files as appropriate. If the PRIMARY filegroup is small, and you're taking frequent log backups, the whole process should be quicker than restoring all the read/write filegroups as with partial backups under SIMPLE.