There are plenty of scenarios where you may want to move data to its own filegroup. Perhaps you have data that is required to be kept even though it’s not as relevant today or you want logging information to be separate from the rest of the data.
When a scenario comes up where you need to restore a database but only care about certain data, not having to bother with an extra filegroup or filegroups can save time. Also, if you have multiple filegroups and one of them gets corrupted, you may be able to restore uncorrupted filegroups on their own.
Adding Filegroup and File
Let’s say we have a Person table in the PRIMARY filegroup.
We’ll assume this table is filled with a bunch of old data we don’t want to bother with as much but have to keep. Let’s move it to its own filegroup. First, we’ll create the filegroup:
ALTER DATABASE ExampleDB ADD FILEGROUP ArchiveFG;
GO
Followed by adding a file to the filegroup:
ALTER DATABASE ExampleDB ADD FILE (
NAME = 'archive_person_dat'
,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\archive_person_dat.ndf'
) TO FILEGROUP ArchiveFG;
GO
We can check sys.filegroups and see that our ArchiveFG additions were successful:
SELECT * FROM sys.filegroups;
GO
Moving Data From PRIMARY
Our Person data is still in the PRIMARY filegroup so let’s get it moved to our new ArchiveFG. Starting with SQL 2016 SP2, we can use SELECT INTO and specify a filegroup. The means one option for moving our data could be:
SELECT * INTO Person_Archive ON ArchiveFG
FROM Person;
GO
We can check our new table:
SELECT * FROM Person_Archive;
GO
Let’s check filegroup information and see where our data is located:
SELECT o.[name], o.[type], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id AND o.type = 'U';
GO
Since we have a copy of our data in Person_Archive in the ArchiveFG filegroup, we could drop Person.
Restoring PRIMARY Filegroup Only
If we are now in a scenario where we only want to restore our PRIMARY filegroup, we can use a good full backup and run the following:
RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ExampleDB_Full.bak'
RESTORE DATABASE ExampleDB_Copy FILEGROUP = 'PRIMARY'
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ExampleDB_Full.bak'
WITH MOVE 'ExampleDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB_Copy.mdf',
MOVE 'ExampleDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ExampleDB_Copy_log.ldf',
PARTIAL, RECOVERY
We have our database copy. Since only restored the PRIMARY filegroup, what happens if we try to query data that was in the ArchiveFG filegroup?
We get an error that we would expect based on how we restored the backup:
Msg 8653, Level 16, State 1, Line 4
The query processor is unable to produce a plan for the table or view ‘Person_Archive’ because the table resides in a filegroup that is not online.
To further see that Person_Archive is in a filegroup that is not online, we can check for the file in sys.database_files:
SELECT name, state_desc
FROM sys.database_files;
GO
While archive_person_dat is RECOVERY_PENDING as expected, we can carry on querying the rest of the data in our database without a problem.
Thanks for reading!
Chad Callihan 