Having a plan for how you manage your database backups is like having an insurance policy to cover your company’s data assets: With a good database backup policy, your data will always be recoverable with minimal data loss. Every DBA must consider what kind of backup requirements are necessary for each database, so an adequate backup solution can be deployed to meet the backup requirements. This article will discuss different database backup techniques that can be used to help minimize the data loss due to unforeseen database corruption, crash, and/or hardware failure.

Type of Corruption

Any number of things could happen that might corrupt your databases: One of your hard disks might go bad; a programmer might implement a new application enhancement that has not been fully tested, which causes widespread data corruption. These are just two of the many causes that might require you to recover one of your databases from a backup. When developing a backup strategy, you should develop a list of possible scenarios that you think are most likely to necessitate recovery of your database from a database backup. These scenarios will help you plan a backup strategy that will allow you to recover your database for each of scenarios you identify.

Backup Requirements

Each database may have the same or different backup requirements. As you develop databases to support new applications, you need to determine the backup requirements for these new databases. A database that supports an online order-entry system would probably have different backup requirements than a database that is only updated once a month and supports a read-only management reporting system.

When considering database backup requirements, consider the following items:

• Can your application tolerate some data loss?
• How frequently is the data in your database updated?
• How long can your application be down while you are recovering your database?
• How big is your database?

The answers to these questions should help guide you in determining what kind of database backup strategy is necessary to adequately allow you to recover your database in the event of a disaster.

Types of Backups

Microsoft SQL Server supports four different types of database backups: Complete, Differential, Transaction log, and File/Filegroup.

The Complete database backup is a backup that backs up an entire database to a single backup file. This single backup file can then be used to restore your database to the point in time when the backup was taken.

The Differential database backup backs up only the data that has changed since the last Complete database backup. To perform a Differential database backup, you must first have taken a Complete database backup. To restore your database from a Differential backup, you must first restore the Complete database backup, and then restore the Differential backup. After these two restore operations are accomplished, your database will be restored to the state it was in when the Differential backup was taken. Using a Differential backup reduces the time and database backup space required to perform a database backup.

A Transaction Log backup backs up all the transactions that have been processed against a database since the last Transaction Log backup was taken. To take a Transaction Log backup, you must first perform a Complete database backup. Using a Transaction Log backup allows you to perform point-in-time restores, so using a Transaction Log backup allows you to restore your database to any particular date and time that the Transaction Log backup covers. A Transaction Log backup is a great tool for backing out any process that might erroneously update your database.

A File/Filegroup backup backs up a single file or file group to a backup file. Using a File/Filegroup backup allows you to back up only parts of your database. The File/Filegroup backup is typically used for very large databases where the length of doing a complete backup is excessive.

Backup Strategy

Once you have determined what are your database backup requirements, you will need to develop a backup strategy. The strategy you choose to deploy will depend on the type and use of your database. Let’s review different strategies that might be deployed for three different types of databases.

Online Transaction Processing Database

Many users employ and update an online transaction processing (OLTP) database simultaneously throughout any given day (one example of an OLTP-type database would be an online order-entry system). Since OLTP databases are constantly being accessed and updated throughout the day, if you restore an OLTP database using only a complete database backup, many transactions would need to be reentered. Therefore, it is critical that a backup strategy be deployed that minimizes the number of transactions that are lost.

For the OLTP database, a combination of Complete, Differential, and Transaction Log backups is a good backup solution. How frequently you do the Transaction Log backups is determined by what level of transaction losses your application can afford. The more frequently the Transaction Log backups are taken, the smaller the number of transaction that might be lost should you have to recover your database from the backups. A typical backup strategy for this kind of database would be to perform a complete database backup once a week, differential backups nightly, and Transaction Log backups hourly during normal business hours.

Nightly-updated Database

A nightly-updated database is a database that is updated at night during a very short window of time. Typically, these updates are done via an automated batch process that might update a few or many records in the database (one example of a nightly updated database would be a type of billing database). Since this kind of database is only updated at night via an automated batch process, saving the transactions may not be as critical — that is, of course, if the automated batch processes can be re-run if the database must be restored.

For a database that is only updated via nightly automated batch process, you would typically back up your database with only a single night database backup. The size of your database would determine whether or not that nightly backup is a Complete database backup or just a Differential backup. A typical backup strategy for this type of database is to run a Complete database backup once a week and Differential backups each night right after the automated nightly update process is performed. This would allow you to get the most recent updates off to a database backup file soon after the database is updated.

Historical Records Retention Database

An historical records retention database is a database that retains records for historical purposes. One example of such a database might be a database developed to keep track of detailed sales records. This type of database grows over overtime as more and more records are appended to the database. Once a record is added to this kind of database, it is never updated, or deleted. This type of database can grow to a very large size over time.

Since this type of database can be quite large, the time required to back up one of these databases might be very significant. When this is the case, partitioning your database into different files/filegroups can allow you to take advantage of File/Filegroup backups. The File/Filegroup backup strategy then allows you to back up the File/Filegroups that contains the newly added records more frequently than those File/Filegroups that contain old records that are not updated .

Secondary Location for Backup

Most DBAs write their database backups to disk instead of tape; the disk backup files are then routinely written to tape. This procedure allows the DBA to write  the backups quickly, since disk I/Os are typically written faster than tape I/Os. This solution carries with it a problem, however: If a disk failure occurs after a database backup was taken but before the database backup was written to tape, any database backups that where not written to tape would be lost. Copying the database backup to an alternative location soon after it is written would eliminate this concern. This secondary location should be on a separate physical machine to help ensure some physical separation between the two different backup copies.

One way to get a secondary copy of a database backup to an alternative storage location would be to define a few ALERTs (one ALERT for each type of database backup). These ALERTs would be defined to detect when a database backup has been taken, and then would trigger a SQL Agent job to copy the newly written backup file to a secondary alternative location. Doing this provides the  means to have a copy of your database backup stored in two different physical locations soon after the backup is created. This kind of approach ensures that you do not lose your most recent database backups if you have a disk failure before database backups are written to tape.

Storage of Backups on Tape/removable Disks

There is one more factor to consider in managing your database backups: offsite storage. Just because you write database backups to tape does not mean you’ve eliminated risk. What if those tapes are stored in your data center, and your data center burns down? If this were to happen, all of your database backups would be lost. You need to develop a strategy for taking copies of your database backups to an offsite storage location. This strategy should identify how often tapes are taken offsite and how long they stay offsite.

Most shops purchase an offsite storage service. This kind of service provides a courier that comes by once a week to take tapes to an offsite location. However, if you are a smaller shop, you might not be able to afford this kind of service, but this does not mean you can’t have some other offsite storage solution. The important point is to make sure your data is stored offsite, so a reliable and secure method to get your valuable database backup to an offsite storage location should be considered.

Conclusion

Having an adequate database backup strategy in place is a critical piece in managing your database environment. Without adequate database backups, you will not be able to recover your database should you have any database corruption, or catastrophic database problem. The appropriate approach to backing up your databases and storing your database backups is critical to ensuring minimal data loss should you need to restore your database from backup.

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, UNIX, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different web publishing sites, as well as SQL Server Magazine. Greg also maintains a website that contains a number of SQL Server examples to common DBA and SQL developer issues.