Top 11 Backup Tips

SQL Server's powerful backup and restore capability offers reliable protection for your data. If you have an effective backup-and-restore strategy, you can sleep well at night knowing that if a problem occurs, you can recover from it with minimal downtime and minimal disruption to your users. Here are 11 tips for getting the most out of the backup and restore utility that comes with SQL Server.

1. Use the Native SQL Server backup software

The Windows 2000 and Windows NT backup programs don't back up open files. Whenever SQL Server is running, all the database files are open, so those files aren't included in a Windows backup. You can stop the MSSQLServer service and then back up the database files, but doing so negatively affects production. The SQL Server backup utility can back up a database that's in use and even includes any changes made during the time it takes to run the backup job.

2. Thoroughly Evaluate third-party software before you use it

Several third-party software packages can back up SQL Server, although some charge extra for that capability. Make certain that any third-party software packages you choose can back up active databases. Some of these packages actually stop the MSSQLServer service and then back up the files, which is something you could do with a simple batch file and the Win2K or NT backup program. (For a comparative review of seven enterprise backup products that support SQL Server, see Ed Roth, "Enterprise Backup Software.")

3. Have a Backup plan

Your backup-and-recovery plan should include not only how you'll back up the data but also how you'll restore it. In fact, the best way to design your backup strategy is to evaluate what you'd need if you had to restore the database in various situations. For example, how would you recover if you lost one of the disks on the server? What if the disk were a data disk or the disk containing the transaction log? How would you recover if the whole server were destroyed, say in a fire? The most important consideration is not just how you'll recover but how quickly you can get the database back in production. Although the backups run quickly and let users continue to connect to the database, keep in mind that a restore takes longer than a backup and users can't connect to the database during the restore process. Defining your recovery strategy can tell you which type of database backups you need and how frequently to schedule them.

4. Choose the Right database-recovery model

This tip applies only to SQL Server 2000, which is the first release to offer the option of using recovery models. As Figure 1 shows, you can configure each database with the appropriate model—either Full, Simple, or Bulk-Logged. (For a description of the recovery models, see the sidebar "Recovery Models.") In setting your recovery model, you need to evaluate the trade-off between the ability to recover without redoing work and the amount of resources required for the recovery level you've chosen. After you decide which recovery model to use, you can plan your backup schedule to support that level of recovery. The question to ask here is how much work you can redo. If you have a database supporting a telephone sales staff or a Web-based ordering system, you can't afford to lose any data. If a customer places an order and that order vanishes from the database, your business will suffer. However, if you loaded the data from paper records or exported it in ASCII format from another database, you might be able to rerun the data-loading operation. So in the latter cases, you probably don't need to incur the overhead of the Full recovery model; you can just restore from a recent full-database backup, then reload the recent data.

5. Consider using Filegroup backups

If your database is so large that you can't perform a full backup in your allocated time, a filegroup strategy might help. This strategy can also significantly reduce the time needed to restore the database in case of a disk failure. The first step in implementing a filegroup strategy is to design the database to use filegroups effectively (e.g., placing different tables on different filegroups). Now, instead of running a full backup, you can back up just one filegroup.

Suppose that you need to back up an order-entry system. Instead of backing up the whole database every night, you split it into four filegroups. You leave the system tables and smaller reference tables on the primary filegroup. Then, you place the Customers table on a second filegroup, the Products table on a third, and the Sales table on a fourth. You back up the entire database on the weekend. And you schedule backups of the Customers filegroup on Monday and Thursday nights, the Products table on Tuesday and Friday nights, and the Sales table on Wednesday and Saturday nights, as Figure 2 shows. Now, let's say that the disk containing the Customers table fails on Thursday, right before the backup is due to start. To recover, you simply restore the backup of the Customers filegroup from Monday, then apply the transaction-log backups just as you would with a typical restore. In this case, SQL Server extracts only the log transactions that apply to the Customers filegroup. Restoring the filegroup takes much less time than restoring the entire database, so normal database operations can resume with less downtime.

Although this backup strategy works fairly well, you have an even more effective option. You know that the Sales table is very active, with thousands of orders input per day. The Customers table is less active, with perhaps 200 new customers per day. And the Products table is relatively static, with a few new products or price changes per day. So, you change the backup schedule. Now, you back up Sales on Monday, Wednesday, and Friday. On Tuesdays and Thursdays, you back up the Customers filegroup. You can back up Products on Saturday or just back it up with the full database backup on Sunday. Figure 3 shows this improved filegroup strategy.

Even if you don't want to split your backups into smaller, more manageable pieces as I've described, placing tables on different filegroups can still help with restoring your database. Using the previous example, suppose you didn't back up the Products filegroup separately. What happens if the disk containing the Products filegroup fails on Thursday? Do you have to restore the full backup and all the logs? Fortunately, the answer is no. You can perform a filegroup-only restore from the full backup you made the previous weekend. (For more details about filegroup backups, see Kalen Delaney, "The Road to Recovery.")

6. Use Differential backups when appropriate

A differential backup backs up all the changes since the last full backup. This type of backup captures only the current value of each row, not all the changes that have occurred in the row since the last full backup. You might not care about the intermediate data values, for example, if you're tracking stock prices or the value of a bid on an item in an online auction. With differential backup, you give up the ability to perform a point-in-time restore, in which you specify that SQL Server should restore only up to a certain point in the transaction log. But with differential backup, a restore runs faster because you restore only the full backup plus the differential backup and you skip all the intermediate data values.

7. Use a Mix of differential and log backups

Suppose that you perform a full backup of your active database on the weekend. Every evening, you perform a differential backup to record the state of the data at the end of the day. And several times during the day, you back up the transaction log, which captures every change made to the data during the day. Now, suppose that the system fails at 10:00 a.m. Thursday. To restore, you apply the full backup, the differential backup from Wednesday evening, and any transaction log backups you made since that differential backup. This mixed strategy is faster than restoring the full database backup and multiple transaction-log backups you created over several days.

8. Don't use differential backups if they aren't appropriate

The key to knowing when differential backups are appropriate is looking at how your data is indexed and updated. If your tables have clustered indexes and the index columns are incrementing numbers such as order number or customer number, SQL Server adds all the new data to the end of the existing data pages. That fact is good for differential backups because all the new data is concentrated on a limited range of pages. However, if your data entry occurs randomly throughout the tables, differential backups might not be the best solution. If any page—and by implication, any row—within an extent changes, SQL Server must include the entire extent in the backup. (An extent is eight contiguous pages, or 64KB.) Even if a very small percentage of the data changes—in some cases, as little as 1 percent—you might actually be backing up almost all the extents in a table. So, consider how your data is indexed, stored, and modified before assuming that you need a differential backup.

9. Back up First to files, then to tape

One common restriction on database backups is that they must happen within a narrow time window. Backing up to tape can be slow, although some newer tape systems offer respectable speeds, and striped backup can be extremely fast to a tape array. An effective strategy is to first back up to a file by using the SQL Server backup process. Then, you can back up the files to tape by using whatever backup software you prefer. To restore the data, you first restore the files to their location on disk, then use the SQL Server restore process to recover the database.

10. Use Multiple backup devices, especially if you back up to tape

SQL Server can use multiple backup devices to stripe the backup in the same way a RAID array stripes data across multiple disks. If your tape device is creating a backup bottleneck, adding more devices can speed the backup process significantly. A backup that takes 6 hours on one tape drive could take 2 hours when three drives are working simultaneously. And SQL Server can restore from these three backup tapes, using only one drive if necessary.

Backing up to multiple devices also works for file backups. If your backup file is 40GB and you only have a set of 20GB drives, use multiple files on separate disks as the target of the backup. You could alternatively build a volume set with Windows, or even a stripe set. Either configuration would give you an effective 40GB drive for your backup.

11. Test your plan

The time to make sure the restore process works is now, not when a disaster has already occurred, the users (and your boss) are yelling, and you're expected soon at your child's Little League game. You might not be able to perform a restore on a production server, but you could restore from your backup files or tapes to a test server. This testing method can also help you check for problems with your tape drives. Restoring the tapes on a different system, using a different drive, verifies that the tapes are readable on another drive. Test your plan and ensure that the restore process works as it's supposed to. Then, when disaster does strike, you'll be ready. You might even make it home in time for that game.