Restore a newer filegroup backup

So this is my set up. In our data warehouse we have mostly tables that are filled with data from a ETL-process. We do not take backup of these tables since we have an option to do a full reset using ETL. We also have a couple of tables with historical data. These are valuable and need to be backed up.

What I did was to split the historical tables into a separate filegroup and do a backup of this filegroup pretty often.

Then comes the trouble. I want to restore this. Since the historical filegroup are newer than the primary, I’m unable to restore it because I get a SLN error. I have simple recovery mode on the database.

In case of a data crash my thought was to restore the primary filegroup (consisting of empty tables) than restore the newest historical filegroup with the latest data, then start the reset action of my ETL.

Any ideas or solutions to how I can manage to to this?
I’m running MSSQL Server 2014

One of the considerations implementing multiple databases is different RTP/RPO requirements. It seems this is the case in your situation because the RPO is different.

Consider creating a separate databases for the ETL tables and historical data. Backup the historical data database as needed to meet your RPO. The ETL database can be backed up infrequently (e.g. only after schema changes), or not at all if you can simply recreate the database script.

Personally, I would split the database only if the size of ETL tables are disproportionally larger than the historical data. The additional complexity might not be worth the effort, especially with backup compression. If the ETL tables can be memory optimized, you have the option to specify SCHEMA_ONLY durability for those tables as long as you can run the ETL reset process after an instance restart. That would avoid the additional backup overhead and allow you to keep all in a single database.

This is explained here, it boils down to that you have to have a full backup of the databases as well as a backup of the read-only filegroups:

A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary and all read/write, secondary filegroups.

In this example, a database named adb, which uses the simple recovery model, contains three filegroups. Filegroup A is read/write, primary filegroup, filegroup B and filegroup C are read-only. Initially, all of the filegroups are online.

1. Do a partial restore of the primary and filegroup A from a partial backup.

   At this point the primary filegroup and filegroup A are online. Files in filegroups B and C are recovery pending, and the filegroups are offline.

2. Online recovery of filegroup C.

   Filegroup C is consistent because the partial backup that was restored above was taken after filegroup C became read-only

3. Restore of filegroup B.

   Files in filegroup B must be restored. The database administrator restores the backup of filegroup B taken after filegroup B became read-only and before the partial backup.

So in your case. Do a Partitial backup of of the Primary filegroup and the Read/Write filegroup and restore that with recovery. The RO file will be recovery pending and then you can restore that filegroup with recovery to get the database up to date and then reload the data.