I'm trying to find out if it's possible to bring a SQL Server 2016 Enterprise database online by restoring only the primary Filegroup. My scenario is, we have a fairly large OLTP database, of around 2TB. About 25% of that size is a single 'history' table, which as the name suggests, just tracks activity.
Each day, this database (in full recovery mode) is backed up and restored to several other environments where it is used for various, typically none-critical purposes. This restore process is slow due to the database size, and the 'history' table is obviously a significant contributer.
Although this 'history' table needs to exist in these other environments (it is referenced by numerous stored procedures), it doesn't need to contain any data. We just need it to exist but it could be blank, or ideally, just contain a few thousand of the most recent rows (not essential).
My first thought in achieving this was to:
- Move the 'history' table to a secondary filegroup (the database has a single, primary filegroup currently)
- Perform a backup of only the primary filegroup (this would probably be an extra 'copy_only' backup).
- Restore this backup to the other environments.
The problem is, after step 3, I can bring the database online, however, I obviously can't reference the 'history' table, since it belongs to an FG that hasn't been restored. If I do, I get an error as expected:
The query processor is unable to produce a plan for the table or view 'History' because the table resides in a filegroup that is not online.
So my questions are:
- Is what I'm trying to achieve even possible using this (or a similar) approach?
- After step 3, is there a way by which I could 'drop' the secondary filegroup and recreate a blank copy of the 'history' table in the primary filegroup? This would allow the application/stored procedures that rely on its presence (but not it's data) to function. This doesn't appear possible without restoring the secondary filegroup first.
- Is there just a flat-out better approach that I've not considered?
Note - The 'history' table is not referenced by any FKs, so there are no data integrity issues caused by not having the data there.
I'm aware that there are alternative ways of 'synchronising' multiple databases rather than a backup/restore (replication being one). Unfortunately, that is somewhat out of my control so for now, I'd like to focus on the backup/restore approach. Of course, I'm happy to hear suggestions.
UPDATE - Just to be clear, the rest of the data in the database is needed in each environment, but minus the 'history' table.