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:
- 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.
- Set file group read only
- Backup ReadWrite and ReadOnly filegroups separately.
- Restore (Primary and) ReadWrite Filegroup (FG) in new db
- Restore ReadOnly filegroup
- Make new Filegroup read only in original DB
- New backup of ReadWrite Filegroup and new backup of new ReadOnly file group
- Restore ReadWrite Filegroup, tell database your first ReadOnly filegroup is in a good state already and to just recover it, restore newly ReadOnly Filegroup.
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.
Now we can verify
Lets add some more data to verify that we are restoring the correct backups. Remember that you cannot add any data to Partition 2!
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
--- Verify (should succeed)
Now we set another file to RO
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.
We start our restore w/ the RW and expect the verification to fail bc it is missing filegroups.
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.
One more runthrough of setting a FG RO and restoring it.
And finally, a quick run through of a complete restore from scratch. Note that this uses the excellent usp_killconnections from here.