Partial Backups With Dell Litespeed

We currently use Dell’s Litespeed for SQL Server for our SQL backup compression and encryption needs. It has an intuitive GUI, manages log shipping and we are generally pretty happy with it. I’ve got a couple of problem databases where I don’t want the vanilla out of the box full backups. A couple of our data warehouse dbs are now over a terabyte in size and continuing to grow. We’re implementing partitioning to help with index and statistics maintenance as well as backups. The down side is that there is no GUI for partial backups with Litespeed so I’ve had to fumble through it with the help of their support. Here then are the working scripts for taking partial backups (and restores) of databases using the Litespeed stored procedures. Our database is in simple recovery mode and we’ll be backing up a read-only file group as well as all our active files.

First backup your read-only file group. You only need to back this up once. Or until an end user has you set it to read-write, and updates records. When that happens be sure to set the file group back to read-only and then back it up again.

EXEC master.dbo.xp_backup_database @database = '<database name>'
    , @filename = 'Y:\SQL_BACKUPS\<name of backup file>'
    , @filegroup = '<filegroup to backup>'
    , @compressionlevel = 4
    , @cryptlevel = 8
    , @encryptionkey = N'IMMAENCRYPTCHYU'
    , @verify = 1
    , @returndetails = 1
    , @init = 1
    , @with = 'CHECKSUM'

The procedure xp_backup_database also has a @read_write_filgroups parameter we’ll use later. It’s important to note that if you include it here with a value of 0 (which seems an intuitive thing to do) you will essentially take a full backup rather than a backup of just the intended filegroup. Including @returndetails is optional but will instruct Litespeed to return a row of information about the backup as it was processed.

Once you have your read-only files backed up you can back up the read-write file groups daily (or hourly or whatever works for your SLA) with the following code. Notice we’ve removed the specific filegroup parameter and added the @read_write_filegroups parameter.

EXEC master.dbo.xp_backup_database @database = '<database name>'
    , @filename = 'Y:\SQL_BACKUPS\<name of backup file>'
    , @compressionlevel = 4
    , @cryptlevel = 8
    , @encryptionkey = N'IMMAENCRYPTCHYU'
    , @read_write_filegroups = 1
    , @verify = 1
    , @returndetails = 1
    , @init = 1
    , @with = 'CHECKSUM';

Once you’ve taken a full backup you can do differential backups of the read-write file groups as well by including an additional @with:

EXEC master.dbo.xp_backup_database @database = '<database name>'
    , @filename = 'Y:\SQL_BACKUPS\<name of backup file>'
    , @compressionlevel = 4
    , @cryptlevel = 8
    , @encryptionkey = N'IMMAENCRYPTCHYU'
    , @read_write_filegroups = 1
    , @verify = 1
    , @returndetails = 1
    , @init = 1
    , @with = 'CHECKSUM'
    , @with = 'DIFFERENTIAL';

Restore scripts look very similar to the backup scripts. If you do not have differential backups of your read-write file groups change NORECOVERY to RECOVERY.

EXEC master.dbo.xp_restore_database @database = '<database name>'
    , @filename = 'Y:\SQL_BACKUPS\<name of backup file>'
    , @encryptionkey = N'IMMAENCRYPTCHYU'
    , @read_write_filegroups = 1
    , @with = 'NORECOVERY'
    , @returndetails = 1;

If you have differentials to restore the script is very similar. RECOVERY should be changed to NO RECOVERY if there are multiple differentials to apply:

EXEC master.dbo.xp_restore_database @database = '<database name>'
    , @filename = 'Y:\SQL_BACKUPS\<name of backup file>'
    , @encryptionkey = N'IMMAENCRYPTCHYU'
    , @read_write_filegroups = 1
    , @with = 'RECOVERY'
    , @returndetails = 1;

Finally read-only file groups can be restored after the database has been brought online after restoring all backups of read-write file groups:

EXEC master.dbo.xp_restore_database @database = '<database name>'
    , @filename = 'Y:\SQL_BACKUPS\<name of backup file>'
    , @filegroup = '<filegroup name>'
    , @encryptionkey = N'IMMAENCRYPTCHYU'
    , @with = 'RECOVERY'
    , @returndetails = 1