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," page 39.)
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.
End of Article
Prev. page
1
[2]
next page -->
