Alternative Backup Strategies

SQL Server backups aren’t limited to full, log, and differential. More advanced options include the file or file group backup strategy, the partial backup strategy, and the copy-only backup strategy.

File/File Group Backup

This strategy is relevant only if your database is comprised of multiple files or file groups. If size or performance requirements make backing up the full database impractical, and if you need to be able to recover quickly in case of failure, then you might consider using a file/file group backup strategy.

In this backup strategy, which you can use for SQL Server 2005 or SQL Server 2000, you specify which files or file groups (or combinations) that you want backed up for any given operation. It’s a good idea to do a full database backup as a baseline shortly after creating the database; from that point forward, you can do file or file group backups regularly. If you’re using the simple recovery model for this database, all read/write files or file groups must be backed up at the same time. If you want to minimize the amount of data loss on recovery, choose the full or bulk-logged recovery model for this database, and add log backups to your strategy.

Restoring still means limiting access to the database, but not for as long as if you were doing a full database restore. During the restore period, only the file groups being restored are offline.

In the worst case, if you must restore the entire database and you’re using the full recovery model, you need all the transaction log backups that you’ve taken since the database came online. In addition, if you want to be able to recover to a specific point in time, you must have a full set of transaction log backups.

Partial Backup

This strategy, which is new in SQL Server 2005, was designed primarily for use with databases that have multiple read-only file groups and are using the simple recovery model. The idea is that because a database of this type is primarily read-only, a full recovery model and a full backup strategy are overkill. However, you can use the partial backup strategy for any database using any recovery model.

When you do a partial backup, you back up all the data in the primary file group, all read/write file groups, and any read-only file groups that you specify. Because read-only tables aren’t subject to frequent changes, they theoretically don’t need to be backed up as often as the read/write tables.

You must do some planning before you can perform partial backups. When you create a database, you must create different file groups, and when you create tables, you must specifically place each table in its appropriate file group—database catalog tables in the primary file group, read-only tables in the read-only file group(s), and read/write tables in the read/write file group(s).

A partial backup lets you restore in less time than is necessary for a full database restore. If your entire database is read-only, then only the primary file group is included in a partial backup, unless you designate otherwise. Also, you can use a partial backup instead of a full backup as the basis for a set of differential backups. The partial backup gives you several options and increased flexibility for your backup strategy.

Restoring a partial backup still means limiting access to the database, but not for as long as if you were doing a full database restore—and only for the primary file group, read/write file groups, and any read-only file groups that are part of the backup. For additional information, see SQL Server 2005 Books Online, "Partial Backups."

Copy-Only Backup

If you need to take a backup for a special purpose (e.g., a one-time restore to create a demo for the sales group to show a prospective client), and you don’t want to affect the order of files needed to restore the database, consider using the SQL Server 2005 copy-only backup strategy. You can make a copy-only backup regardless of what type of recovery strategy the database is using—full, bulk-copy, or simple.

Don’t count on using a copy-only backup as part of your restore strategy. In fact, you can make a copy-only backup, restore it to a laptop for demo purposes, then delete the copy-only backup file. Other “normal” backups won’t even realize the copy-only backup ever existed and won’t request it as part of a restore process.

You can’t use the copy-only backup strategy as a base for making differential backups, because copy-only doesn’t update the differential bitmap (i.e., the bitmap of changed pages that the differential backup uses as a guide to know which extents to back up and which to leave). In fact, a differential backup won’t even know that a copy-only backup has been made, and won’t need the copy-only backup for the full plus differential restore process.

A copy-only transaction log backup doesn’t truncate the log file, as a normal transaction log backup does. Nor does a copy-only backup affect the log chain that’s used when doing a full plus log restore; the copy-only backup isn’t even in the list of log backups that queue for the restore process. For additional information, see SQL Server 2005 BOL, "Copy-Only Backups."