SQL Server - Restore only primary Filegroup but bring the database online

Question

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:

1. Move the 'history' table to a secondary filegroup (the database has a single, primary filegroup currently)
2. Perform a backup of only the primary filegroup (this would probably be an extra 'copy_only' backup).
3. 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:

1. Is what I'm trying to achieve even possible using this (or a similar) approach?
2. 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.
3. 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.

Answer 1

Since J.D. has already given you some good advice on what could be the alternatives, I'm gonna just add how you could achieve specifically what you asked and if you still think it's a good approach for your problem, you can use it.

Here's a database with two filegroups for this example:

USE [master]
GO

CREATE DATABASE [Lab]
 ON  PRIMARY 
( NAME = N'Lab', FILENAME = N'C:\Databases\Lab.mdf'), 
 FILEGROUP [HISTORY] 
( NAME = N'Lab_History', FILENAME = N'C:\Databases\Lab_History.ndf')
 LOG ON 
( NAME = N'Lab_log', FILENAME = N'C:\Databases\Lab_log.ldf')
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

Now we create two tables on that database (each residing on a different filegroup):

CREATE TABLE Users (ID int, UserName varchar(50))
ON [PRIMARY];

CREATE TABLE History (ID int, UserName varchar(50))
ON [HISTORY]; --Table History resides on a different Filegroup

After that, we backup the filegroup we want to restore later:

--Take a COPY_ONLY backup of the PRIMARY Filegroup
BACKUP DATABASE Lab
FILEGROUP = 'PRIMARY'
TO DISK = 'C:\BackupSQL\Lab_PRIMARY.bak'
WITH COPY_ONLY;

You can drop the Lab database and restore only the filegroup with our Users table:

--Restore the Filegroup Backup
RESTORE DATABASE Lab
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\BackupSQL\Lab_PRIMARY.bak';

Finally, the trick to have an empty History table online:

--Rename the original table
USE Lab;
EXEC sp_rename 'dbo.History', 'History_';

--Recreate an empty History table
CREATE TABLE History (ID int, UserName varchar(50))
ON [PRIMARY]; --Recreates a History table now on the PRIMARY Filegroup

Answer 2

I know you mentioned it's a little bit out of your control, but if you don't need the data at all and just the schema itself, doing a backup and restore on a 2 TB database is going to be the least efficient way to accomplish your goal, by far, among probably 10 different options. (You should familiarize yourself with the alternatives and bring this to the attention of whoever's control it is in.)

Some of those alternative options are Replication (as you mentioned), SSIS, SQL Agent Job that keeps the other instances up to date, scripting out the database, or using a DACPAC to deploy just the schema, which would be my choice for you as it's the simplest / makes the most sense. (Also if your limitation in using a different features is because you're not allowed to setup anything on the server, DACPAC is an already enabled feature, and the process is simpler than backups since you just use SSMS to generate the file for you, similar to if you were generating scripts. No need to install or setup anything.)

If your schema doesn't change often, you can even dedicate one empty copy of the database as your "model" which always remains data-less and can easily be used to generate new databases off of (even if you still stick to the backup and restore methodology, since it'll be a tiny database). Or even if it does change often, you can still do the backup and restore to just this one model database, and then truncate the data or use a DACPAC there to generate the other instances so that way you don't need to wait on a 2 TB restore more than once.