Using Partial Database Availability for Targeted Restores

Q: I have a large database that I’d like to restore on a development system pretty regularly, but I don’t want to restore all the data. Is there a way to perform a partial restore without creating multiple databases?

A: The easiest way to do so is to split the database into multiple file groups and restore only some of the file groups. This procedure uses an Enterprise Edition–only feature called partial database availability (available in SQL Server 2005 and later), which allows a database to be online as long as the primary file group is online (obviously, the file groups that are offline aren’t accessible). If your development system runs on the much-less expensive Developer Edition, you’ll still be able to use partial database availability because Developer Edition is basically Enterprise Edition that you just can’t use in production.

In addition to enabling the scenario you describe, partial database availability is a fantastic way to reduce downtime during disaster recovery using backups, because the most critical portion of the data can be brought online first, and other portions are restored using online piecemeal restore while the main workload is running.

The two easiest ways to split the data into multiple file groups are to place tables in separate file groups manually or to partition individual tables using table and index partitioning. With manual partitioning, you can isolate one or more tables (and their indexes) into each file group. For example, if you had a parts sales database, you might decide to create a sales table for each state, plus a parts table, and have each table in its own file group. In your scenario, you might pick the smallest sales file group and the parts file group to restore to the development system. In a disaster recovery scenario, you might choose to restore the sales file group for the state with the largest population first (to minimize the downtime for the portion of the database handling the most sales), bring the database online, and then restore the file groups for the remaining states in sequence.

Using SQL Server’s table/index partitioning feature, you can perform horizontal partitioning on a table (and indexes) and have each portion of the table in a separate file group. For instance, in a sales database that contains the last few years’ data, you might partition by date and have a separate file group for each year’s data. In your scenario, you would likely restore just the current year’s data on the development system to test the OLTP system. In a disaster recovery scenario, you’d restore the current year’s data first to be able to bring the OLTP workload online, and restore the historical data later.

Once you’ve split the database into multiple file groups, you can restore a subset of them by performing a partial restore. First, you’ll create a database with three file groups, then you’ll put a table on each of the non-primary file groups using the code in Listing 1.

Listing 1: Code to Create a Database with Multiple File Groups

CREATE DATABASE ExampleDB;
GO 

ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILEGROUP TestFileGroup2;
GO 

ALTER DATABASE ExampleDB ADD FILE (
      NAME = TestFile1,
      FILENAME = 'C:SQLskillsTestFile1.ndf',
      SIZE = 5MB)
TO FILEGROUP TestFileGroup1;
ALTER DATABASE ExampleDB ADD FILE (
      NAME = TestFile2,
      FILENAME = 'C:SQLskillsTestFile2.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:SQLskillsExampleDB.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

RESTORE DATABASE ExampleDB
FILEGROUP = 'primary'
FROM DISK = 'c:SQLskillsExampleDB.bck'
WITH PARTIAL, NORECOVERY;
GO

RESTORE DATABASE ExampleDB
FILEGROUP = 'TestFileGroup1'
FROM DISK = 'c:SQLskillsExampleDB.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;
GO

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.

For more information about using the native partitioning feature, I recommend reading two excellent white papers: "Partitioned Tables and Indexes in SQL Server 2005,"  which was actually written by Kimberly, and "Partitioned Table and Index Strategies Using SQL Server 2008," which uses a different example scenario.