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

backupfilegroupsrecoveryrestoresql server

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.

Best Answer

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
Related Question