1

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.

2

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
4
  • That's brilliantly simple! Quick follow up question - in this method, the database is 'aware' of the secondary filegroup, it just isn't online. Are you aware of any side-effects to this? Mar 24 at 14:01
  • @FrugalShaun I couldn't find any side-effect on the RESTORE Statements doc. The only thing I could see on the doc related to performance after after a restore was: Restoring a database clears the plan cache for the database being restored. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance.
    – Ronaldo
    Mar 24 at 14:17
  • One side-effect that came to my mind now is that if that filegroup is marked as the default on the database. You should change it on the restored database or new objects that users try to create without specifying a different filegroup would not work with the error of a filegroup not available.
    – Ronaldo
    Mar 24 at 14:25
  • OK good point I'll bear the in mind. Mar 24 at 14:48
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.

9
  • Hi. Sorry I probably wasn't clear. I absolutely need the data in the database, I just don't need the data for that one table, which happens to be the biggest, because it's basically just an auditing table that tracks activity. The purpose of the backup/restore is to get that data into other environments to have realistic data for application testing. To explain further, if we used replication, we'd basically be replicating the entire database, minus the audit table. It's not unfeasible, but I'd have concerns over the network I/O during peak times. Mar 24 at 12:12
  • @FrugalShaun Gotcha, that's a different story then. Size of database <> I/O bandwidth, so without knowing how active your database is, e.g. how many transactions for how much data per X,Y,Z (second, minute, hour, etc) it's hard to say if that should be a real concern. Though I've worked with both multi-TB databases in a fairly high transactional environment (for Financial Tech.) and with Replication, and I've never encountered that being a bottleneck. The network bandwidth use is only between the SQL Servers involved in the Replication stack so it definitely doesn't impact end users.
    – J.D.
    Mar 24 at 12:21
  • @FrugalShaun Anyway, I'll think of what you can do, given my new understanding of your situation. My current recommendation would be to keep your history table in it's own database to decouple it from the main functions of your database and then even the backup and restore methodology would be cleaner / faster. But I understand that's probably not the most easily accomplishable solution since it'll likely involve code changes.
    – J.D.
    Mar 24 at 12:23
  • 1
    Regarding the other environments, I'm not sure what they're all used to be honest but believe it's primarily testing by different teams. Different environments may also be different stages of the software release process and therefore have some minor schema differences (that are re-applied after the restore). It's not great and long been a source of frustration, but it is what it is for the short term at least! Mar 24 at 13:36
  • 1
    Just to follow up. The synonym solution I mentioned seems to work well. So I can move the history table to its own database (as per your suggestion), drop it from the source database and replace it with a synonym that points to the new table. This appears to work seamlessly. Mar 24 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.