Listing 1: Code to Create a Database with Multiple File Groups
GO
ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup2;
GO
ALTER DATABASE ExampleDB ADD FILE (
NAME = TestFile1,
FILENAME = 'C:\SQLskills\TestFile1.ndf',
SIZE = 5MB)
TO FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILE (
NAME = TestFile2,
FILENAME = 'C:\SQLskills\TestFile2.ndf',
SIZE = 5MB)
TO FILEGROUP TestFileGroup2;
GO
CREATE TABLE ExampleDB..t1 (c1 INT) ON TestFileGroup1;
CREATE TABLE ExampleDB..t2 (c1 INT) ON TestFileGroup2;
GO
INSERT INTO ExampleDB..t1 VALUES (1);
INSERT INTO ExampleDB..t2 VALUES (2);
GO
BACKUP DATABASE ExampleDB TO DISK = 'c:\SQLskills\ExampleDB.bck';
GO
Now on a different system you can restore just the primary file group and the first file group. The key is to restore the primary file group first using WITH PARTIAL to let SQL Server know you aren’t restoring the entire database, as shown in Listing 2.
Listing 2: Restoring the Primary File Group Using WITH PARTIAL
FILEGROUP = 'primary'
FROM DISK = 'c:\SQLskills\ExampleDB.bck'
WITH PARTIAL, NORECOVERY;
GO
RESTORE DATABASE ExampleDB
FILEGROUP = 'TestFileGroup1'
FROM DISK = 'c:\SQLskills\ExampleDB.bck'
WITH NORECOVERY;
GO
Next, you can restore any differential and/or transaction log backups to get the restored file groups to the desired point in time, and then bring the database online using the following code:
RESTORE DATABASE ExampleDB WITH RECOVERY;
If I query the database using the code in Listing 3, it will show me what’s online.
Listing 3: Code Used to Determine Which File Groups Are Online
SELECT [name], [state_desc] FROM ExampleDB.sys.database_files;
GO
name state_desc
---------------- -----------------
ExampleDB ONLINE
ExampleDB_log ONLINE
TestFile1 ONLINE
TestFile2 RECOVERY_PENDING
Now, of course, I can only use the portions of the database that I’ve restored. If I try to access anything in an offline file group, I’ll get an error similar to the following:
SELECT * FROM ExampleDB..t2;
GO
Msg 8653, Level 16, State 1, Line 1
The query processor is unable to produce a plan for the table or view 't2' because the table resides in a filegroup which is not online.