Basic walkthrough of Partial Backup and Restore for Partitioned and Read Only SQL Server Databases

Hi all,
Building on an excellent article from Pinal Dave, here is a walkthrough test of creating, partioning, backing up and restoring (partial and full) of a sql server database.  These tests were done on sql 2008 but should be pretty generic.

What I wanted to test is the ability to make parts of a large database read only and stop backing up the whole damn thing.  The basic steps (detail below) are:

1. Create partitioned table.  This can be a new table and database (as below) or you can repartition an existing table (create your partition function and scheme and apply a clustered index).  Note that repartitioning can take a long time and locks your table so test accordingly.
2. Set file group read only
3. Backup ReadWrite and ReadOnly filegroups separately.
4. Restore (Primary and) ReadWrite Filegroup (FG) in new db 
5. Restore ReadOnly filegroup
6. Verify
7. Make new Filegroup read only in original DB
8. New backup of  ReadWrite Filegroup and new backup of new ReadOnly  file group
9. Restore  ReadWrite Filegroup, tell database your first ReadOnly filegroup is in a good state already and to just recover it, restore newly ReadOnly Filegroup.
10. Verify

I do that a few times to verify the procedure and then have a disaster recovery version at the bottom.  Below is a step by step walkthrough.

First we create the test database, the partition function, scheme and table.  We then insert data that will span several partitions.  All of this is stolen more or less directly from Pinal Dave, I had more steps to test so I added a few more partitions.  Note, if you don't have a d drive, you will need to update the paths accordingly.

Update: see here for details on differential backups.

-- Step 1: setup basic test database

use master

exec master.dbo.usp_killconnections 'testdb'

IF EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TestDB')

DROP DATABASE TestDB;

GO

CREATE DATABASE TestDB

ON PRIMARY

(NAME='TestDB_Part1', FILENAME='D:\data\TestDB_Part1.mdf',

SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part2

(NAME = 'TestDB_Part2', FILENAME ='D:\data\TestDB_Part2.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part3

(NAME = 'TestDB_Part3', FILENAME ='D:\data\TestDB_Part3.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part4

(NAME = 'TestDB_Part4', FILENAME ='D:\data\TestDB_Part4.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestDB_Part5

(NAME = 'TestDB_Part5', FILENAME ='D:\data\TestDB_Part5.ndf',

SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );

GO

--- Step 2 : Create Partition Range Function

USE TestDB;

GOCREATE PARTITION FUNCTION TestDB_PartitionRange (INT)

AS RANGE LEFT FOR

VALUES (10,20,30,40);

GO

USE TestDB;

GO

--- Step 3 : Attach Partition Scheme to FileGroups

CREATE PARTITION SCHEME TestDB_PartitionScheme

AS PARTITION TestDB_PartitionRange

TO ([PRIMARY], TestDB_Part2, TestDB_Part3, TestDB_Part4, TestDB_Part5);

GO

--- Step 4 : Create Table with Partition Key and Partition Scheme

CREATE TABLE TestTable

(ID INT NOT NULL,

Date DATETIME)

ON TestDB_PartitionScheme (ID);

GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table

CREATE UNIQUE CLUSTERED INDEX IX_TestTable

ON TestTable(ID)

ON TestDB_PartitionScheme (ID);

GO

--- Step 6 : Insert Data in Partitioned Table

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1

VALUES (1,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (11,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (12,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4

VALUES (32,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (42,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (52,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3

VALUES (22,GETDATE());

GO

--- Step 7 : Test Data from TestTable

SELECT *

FROM TestTable;

GO

--- Step 8 : Verify Rows Inserted in Partitions

SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

GO

We can do two types of backups now.  We can do a standard full backup which is fine on this size of database but as the dbs get large, syncing around and restoring very large dbs can be a PITA.  If we can make some of our data read only (with all that entails), we can speed our backups, restores and copy times dramatically.

First, we want to set partition 2 to ReadOnly.  We do that with 

-- set FG 2 to read only

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY

GO

We can now backup the RW FG's separately

-- Backup rw and ro separately

BACKUP DATABASE [TestDB]

READ_WRITE_FILEGROUPS

TO  DISK = N'D:\data\TestDB_RW.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

BACKUP DATABASE [TestDB]

FILEGROUP = N'TestDB_Part2'

TO  DISK = N'D:\data\TestDB_RO_Part2.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

We have 2 backup files now.  Check the IsPresent column for each FileGroup with:

-- list backup files

RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RW.bak'

RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RO_Part2.bak'

We can now begin our restore on the TestDB2 (imaginatively named, i know).  First the RW filegroup (which, importantly, contains the PRIMARY FG)

--restore RW

use master

exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'D:\data\TestDB_RW.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10

GO

All, well and good but if we check our table, we get an error as not all the data is present.  One important note that is not shown below is that if we had another table in our database, we could query that w/ no problem.  It is only queries that try to hit tables w/ missing data that fail.  

--- Verify (should fail)

use testdb2

SELECT *

FROM TestTable;

GO

To see why this fails run: 

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

Note that our RO group is offline.  We need to restore it.

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'D:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf'

Now we can verify

--- Verify (should succeed)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

Lets add some more data to verify that we are restoring the correct backups.  Remember that you cannot add any data to Partition 2!

-- add data

use testdb

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4

VALUES (33,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (43,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5

VALUES (53,GETDATE());

Now we want to test what happens when we backup and restore a copy of the RW data.  We don't want to have to deal w/ the RO data

-- Backup rw

BACKUP DATABASE [TestDB]

READ_WRITE_FILEGROUPS

TO  DISK = N'D:\data\TestDB_RW2.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--restore RW

use master

exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'D:\data\TestDB_RW2.bak'  WITH  FILE = 1, 

      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10

GO

 --- Verify (should succeed)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

Now we set another file to RO

-- set FG 3 to read only

use master

exec master.dbo.usp_killconnections [TestDB]

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part3] READONLY

GO

You may be tempted to just send the RO backup over but you need to send a new backup of the PRIMARY FG w/ the meta data about the RO FG.  So we backup RW and the new RO and send them over.

-- Backup rw

BACKUP DATABASE [TestDB]

READ_WRITE_FILEGROUPS

TO  DISK = N'D:\data\TestDB_RW3.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- backup new RO

BACKUP DATABASE [TestDB]

FILEGROUP = N'TestDB_Part3'

TO  DISK = N'D:\data\TestDB_RO_Part3.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

We start our restore w/ the RW and expect the verification to fail bc it is missing filegroups.

--restore RW

use master

exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'D:\data\TestDB_RW3.bak'  WITH  REPLACE,  PARTIAL, FILE = 1, 

      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',

  NOUNLOAD,  STATS = 10

GO

--- Verify (should fail)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

Restore your 'new' RO file and you may expect this to be enough (but you would be wrong)

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM

DISK = N'D:\data\TestDB_RO_Part3.bak'   WITH 

MOVE 'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf'

--- Verify (should fail)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

If you notice, Partition 2 is still recovery pending.  But wasn't it there already?  Well, yes, you just have to tell SQL server it is in a good state.   Note that you could (if you wanted to waste IO) restore partition 2 from backup instead.

-- bring FG 2 online

RESTORE DATABASE [TestDB2] FILEGROUP='TestDB_Part2'

 WITH RECOVERY

--- Verify (should succeed)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

One more runthrough of setting a FG RO and restoring it.

-- set FG 4 to read only

use master

exec master.dbo.usp_killconnections [TestDB]

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part4] READONLY

GO

-- Backup rw

BACKUP DATABASE [TestDB]

READ_WRITE_FILEGROUPS

TO  DISK = N'D:\data\TestDB_RW4.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

-- backup new RO

BACKUP DATABASE [TestDB]

FILEGROUP = N'TestDB_Part4'

TO  DISK = N'D:\data\TestDB_RO_Part4.bak'

WITH NOFORMAT, INIT, 

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--restore RW

use master

exec master.dbo.usp_killconnections [TestDB2]

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'D:\data\TestDB_RW4.bak'  WITH  PARTIAL, FILE = 1, 

      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

--- Verify (should fail)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

-- bring FG 2, 3 online

use master

RESTORE DATABASE [TestDB2]

FILEGROUP='TestDB_Part2',

FILEGROUP='TestDB_Part3'

 WITH RECOVERY

--- Verify (should fail)

use testdb2

select * from sys.master_files  where database_id = 13

use testdb2

SELECT *

FROM TestTable;

GO

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part4'  FROM

DISK = N'D:\data\TestDB_RO_Part4.bak'   WITH 

MOVE 'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf'

--- Verify (should succeed)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

And finally, a quick run through of a complete restore from scratch.  Note that this uses the excellent usp_killconnections from here.

-- disaster recovery

use master

exec master.dbo.usp_killconnections [TestDB2]

drop database testdb2

use master

exec master.dbo.usp_killconnections [TestDB2]

-- restore RW

RESTORE DATABASE [TestDB2]

read_write_filegroups

  FROM  DISK = N'D:\data\TestDB_RW4.bak'  WITH  PARTIAL, FILE = 1, 

      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.mdf', 

      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf', 

      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf', 

      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf', 

      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.mdf', 

      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',

  REPLACE, NOUNLOAD,  STATS = 10, recovery

GO

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part2'  FROM

DISK = N'D:\data\TestDB_RO_Part2.bak'   WITH 

MOVE 'TestDB_Part2' TO N'D:\data\TestDB2_Part2.mdf'

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part3'  FROM

DISK = N'D:\data\TestDB_RO_Part3.bak'   WITH 

MOVE 'TestDB_Part3' TO N'D:\data\TestDB2_Part3.mdf'

-- restore RO

use master

RESTORE DATABASE [TestDB2]  FILEGROUP = 'TestDB_Part4'  FROM

DISK = N'D:\data\TestDB_RO_Part4.bak'   WITH 

MOVE 'TestDB_Part4' TO N'D:\data\TestDB2_Part4.mdf'

--- Verify (should succeed)

use testdb2

SELECT *

FROM TestTable;

GO

-- check filegroups.  especially state_desc

select * from sys.master_files 

where database_id = (select database_id from sys.databases where name = 'TestDB2')

Good luck!