SQL Server 2000 Administrator's Pocket Consultant:Database Backup and Recovery

SQL Server 2000 Administrator's Pocket Consultant: Database Backup and Recovery.

Information is the fuel that drives the enterprise, and the most critical information is often stored in databases. Databases are where you'll find an organization's customer account information, partner directories, product knowledge base, and other important data. To protect an organization's data and to ensure the availability of its databases, you need a solid database backup and recovery plan.
Backing up databases can protect against accidental loss of data, database corruption, hardware failures, and even natural disasters. It's your job as a database administrator to perform backups and store them in a safe and secure location.

Creating a Backup and Recovery Plan
Creating and implementing a backup and recovery plan is one of your most important duties as a database administrator. Think of database backup as an insurance plan for the future—and for your job. Important data is accidentally deleted all the time. Mission-critical data can become corrupt. Natural disasters can leave your office in ruins. With a solid backup and recovery plan you can recover from any of these situations. Without one, you're left with nothing to fall back on.
Initial Backup and Recovery Planning
Creating and implementing a backup and recovery plan takes time. You'll need to figure out which databases need to be backed up, how often the databases should be backed up, and more. To help you create a plan, consider the following:

• What type of database are you backing up? System and user databases often have different backup and recovery needs. For example, the master database is essential for all Microsoft SQL Server operations. If the master database fails or becomes corrupt, it takes the whole server down with it. But you don't need to back up master every hour or every half hour—as you might have to do with a critical user database that handles real-time customer transactions. You need to back up master only after you create a database, change configuration values, configure SQL logons, or perform similar activities that make changes to the database.
• How important is the data in the database? How you judge the data's importance can go a long way toward determining when and how you should back it up. While you may back up a development database weekly, you would probably back up a production database at least daily. The data's importance also drives your decision about the type of backup. With that development database, you'd probably do a full backup once a week. With an in-house customer order database that's updated throughout the weekday, you'd probably perform full backups twice a week and supplement this with daily differential backups and hourly backups for the transaction logs. You may even want to set named log marks that allow recovery up to a specific point of work.
• How often are changes made to the database? The frequency of change can drive your decision about how often the database should be backed up. Because a read-only database doesn't ordinarily change, it doesn't need to be backed up regularly. On the other hand, a database that's updated nightly should be backed up after the nightly changes are posted. A database that's updated around the clock should be backed up continually.
• How quickly do you need to recover the data? It's important to consider time when you create a backup plan. For mission-critical databases, you may need to get the database back online swiftly; to do this, you may need to alter your backup plan. Instead of backing up to tape, you may want to back up to disk drives or use multiple backup devices. Both options are much faster than restoring from a single tape device.
• Do you have the equipment to perform backup? You need backup hardware to perform backups. If you don't have the hardware, you can't perform backups. To perform timely backups, you may need several backup devices and several sets of backup media. Backup hardware includes a tape drive, optical drives, removable disk drives, and plain old disk drives. Generally, tape drives are less expensive but slower than other types of drives.
• What's the best time to schedule backups? You'll want to schedule backups when database usage is as low as possible. This will speed the backup process. However, in the real world you can't always schedule backups for off-peak hours. So you'll need to carefully plan when key databases are backed up.
• Do you need to store backups off-site? Storing copies of backup tapes off-site is essential to the recovery of your systems in the case of a natural disaster. In your off-site storage location, you should also include copies of the software you may need to install in order to restore operations on a new system.
  Backing up a database is a bit different than backing up a server or a workstation. This is primarily because you'll often need to combine all (or nearly all) of the available techniques to ensure that you can recover a database completely. The basic types of backups you can perform include
• Complete database backups Perform a full backup of the database, including all objects, system tables, and data. When the backup starts, SQL Server copies everything in the database and also includes any needed portions of the transaction log as the backup is in progress. Because of this, you can use a complete backup to recover the complete state of the data in the database at the time the backup operation finishes.
• Differential backups Designed to back up data that has changed since the last complete backup. Because you store only the changes, the backup is faster and you can perform it more often. As with complete backups, differential backups include needed portions of the transaction logs, which allow you to restore the database to the time when the backup operation finishes.
  
• Tip You can use differential backups only in conjunction with complete backups, and you can't perform differential backups on the master database. Don't confuse differential backups with incremental backups. Differential backups record all changes since the last full backup (which means the size of the incremental backup grows over time). Incremental backups record changes since the most recent full or incremental backup (which means the size of the incremental backup is usually much smaller than a full backup).
• Transaction log backups Transaction logs are serial records of all database modifications and are used during recovery operations to commit completed transactions and to roll back uncompleted transactions. When you back up a transaction log, the backup stores the changes that have occurred since the last transaction log backup and then truncates the log, which clears out transactions that have been committed or aborted. Unlike complete and differential backups, transaction log backups record the state of the transaction log at the time the backup operation starts (not when it ends).
• File and filegroup backups Allow you to back up database files and filegroups rather than the entire database. This is useful with large databases where, to save time, you want to back up individual files rather than the entire database. Many factors affect file and filegroup backups. When you use file and filegroup backups, you must back up the transaction log as well. Because of this dependency, you can't use this backup technique if Truncate Log On Checkpoint is enabled. Furthermore, if objects in the database span multiple files or filegroups, you must back up all the related files and filegroups at the same time.
  SQL Server 2000 uses recovery models to help you plan backups. The types of databases you are backing up and the types of backups you perform drive the choices for recovery models. The three recovery models shown on the following page are available:
  
• Simple The simple recovery model is designed for databases that need to be recovered to the point of the last backup. The backup strategy with this model should consist of full and differential backups. You cannot perform transaction log backups when the simple recovery model is enabled. SQL Server 2000 turns on the Truncate Log On Checkpoint option, which clears out inactive entries in the transaction log on checkpoint. Because this model clears out transaction logs, it is ideal for most system databases.
• Full The full recovery model is designed for databases that need to be recovered to the point of failure or to a specific point in time. With this model all operations are logged, including bulk operations and bulk loading of data. The backup strategy with this model should include full, differential, and transaction log backups, or full and transaction log backups only.
• Bulk-logged The bulk-logged recovery model reduces the log space usage yet retains most of the flexibility of the full recovery model. With this model, bulk operations and bulk loads are minimally logged and cannot be controlled on a per operation basis. You'll need to manually redo bulk operations and bulk loads if the database fails before you perform a full or differential backup. The backup strategy with this model should include full, differential, and transaction log backups, or full and transaction log backups only.
  Each database can have a different recovery model. By default, master, msdb, and tempdb use the simple recovery model, and the model database uses the full recovery model. Model is the template database for all new databases, so if you change the default setting all new databases for the database server instance use the new default model. You set the recovery model by completing the following steps:
  
• Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with.
• If plan to switch from bulk-logged recovery to simple recovery, perform a transaction log backup prior to making the change and then change your backup strategy so that you no longer perform transaction log backups.
• Click the plus sign (+) next to the server you want to work with again and then click the plus sign (+) next to the Databases folder.
• Right-click the database you want to work with and then, from the shortcut menu, choose Properties. This displays the database's Properties dialog box.
• Use the Model selection list in the Options tab to change the recovery model and then click OK.
• If you switched from simple recovery to full or bulk-logged recovery, add transaction log backups to your backup strategy for the database.
  Planning for Backups of Replicated Databases
  Databases that are replicated present a special problem for backup and restore. This is primarily because the traditional database architecture is extended to include three server roles:
  
  Publisher Servers that make data available for replication, track changes to data, and maintain other information about source databases.
  
• Distributor Servers that distribute replicated data and store the distribution database.
• Subscriber The destination servers for replication. They store the replicated data, receive updates, and in some cases can also make changes to data.
  Because of these roles, you'll need to use additional strategies for backing up and restoring replicated databases. For complete details see the section of the SQL Server Books Online entitled "Backing Up and Restoring Replication Databases." (Click Start, then Programs, then Microsoft SQL Server 2000, and then choose Books Online.)
  Planning for Backups of Very Large Databases
  When backing up and restoring very large databases, you may want to take advantage of parallel backup and restore. Parallel backup and restore allow SQL Server 2000 to use multiple threads to read and write data. This means SQL Server can read data from, and write data to, multiple data sources. Backup and restore use parallel input/output (I/O) in different ways:
• Backup uses one thread per disk device to read data from the database when a database has files on several disk devices.
• Restore uses one thread per disk device while it initializes a database that it's creating for the restore process, provided the database is defined with files on several disks.
• Both backup and restore use one thread per backup device when a backup set is stored on multiple backup devices.
• As you can see from the previous list, to take advantage of parallel I/O you must implement your backup strategy so that databases use
• Multiple disk drives for storing data
• Multiple backup devices for backing up and restoring data
• Once you determine the backup operations you'll use on each database and how often you'll back up each database, you can select backup devices and media that meet these requirements. The next section covers backup devices and media.
  
  Selecting Backup Devices and Media
  Many different solutions are available for backing up data. Some are fast and expensive. Others are slow but very reliable. The backup solution that's right for your organization depends on many factors, including
• Capacity The amount of data that you need to back up on a routine basis. Can the backup hardware support the required load given your time and resource constraints?
• Reliability The reliability of the backup hardware and media. Can you afford to sacrifice reliability to meet budget or time needs?
• Extensibility The extensibility of the backup solution. Will this solution meet your needs as your organization grows?
• Speed The speed with which data can be backed up and recovered. Can you afford to sacrifice speed to reduce costs?
• Cost The cost of the backup solution. Does the solution fit within your budget?
  Capacity, reliability, extensibility, speed, and cost are the issues that will influence your choice of a backup plan. If you determine the relative value of these issues to your organization, you'll be on the right track to selecting an appropriate backup solution. Some of the most commonly used backup solutions include
• Tape drives Tape drives are the most common backup devices. They use magnetic tape cartridges to store data. Magnetic tapes are relatively inexpensive but aren't highly reliable. Tapes can break or stretch. They can also lose information over time. The average capacity of tape cartridges ranges from 100 MB to 2 GB. Compared with other backup solutions, tape drives are fairly slow. Still, the key selling point is the low cost of tape drives and magnetic tapes.
• DAT drives DAT (digital audio tape) drives are quickly replacing standard tape drives as the preferred backup devices. DAT drives use 4-mm tapes and 8-mm tapes to store data. DAT drives and tapes are more expensive than standard tape drives and tapes, but they offer higher speed and more capacity. DAT drives that use 4-mm tapes typically can record over 30 MB per minute and have capacities of up to 16 GB. DAT drives that use 8-mm tapes typically can record more than 10 MB per minute and have capacities of up to 10 GB (with compression).
  Tip To perform faster backup and recovery operations, you can use multiple backup devices with SQL Server. For example, if it normally takes four hours to perform a full backup or restoration of the database, you can cut the backup and restoration time in half using two backup devices; with four backup devices you could fully back up or restore the database in an hour.
  
• Autoloader tape systems Autoloader tape systems use a magazine of tapes that create extended backup volumes capable of meeting the high capacity needs of the enterprise. With an autoloader system, tapes within the magazine are automatically changed as needed during the backup or recovery process. Most autoloader tape systems use DAT tapes. The typical system uses magazines with between 4 and 12 tapes. The key drawback to these systems is the high cost.
  
• Magnetic optical drives Magnetic optical drives combine magnetic tape technology with optical lasers to create a more reliable backup solution than DAT. Magnetic optical drives use 3.5-inch disks and 5.25-inch disks that look similar to floppies but are much thicker. Typically, magnetic optical disks have capacities of between 1 GB and 4 GB.
  
• Tape jukeboxes Tape jukeboxes are similar to autoloader tape systems. Jukeboxes use magnetic optical disks rather than DAT tapes to offer high- capacity solutions for the enterprise. These systems load and unload disks stored internally for backup and recovery operations. The key drawback to tape jukeboxes is the high cost.
  
• Removable disks Removable disks, such as Iomega Jaz, are increasingly being used as backup devices. Removable disks offer good speed and ease of use for a single drive or single system backup. However, the disk drives and the removable disks tend to be more expensive than standard tape or DAT drive solutions.
  
• Disk drives Disk drives provide the fastest way to back up and restore databases. With disk drives, you can often accomplish in minutes what a tape drive takes hours to do. When your needs mandate a speedy recovery, nothing beats a disk drive. The drawbacks to disk drives, however, are high cost and low extensibility.
  Although backup device selection is an important step in implementing a backup and recovery plan, it isn't the only step. You also need to purchase the tapes or the disks, or both, that will allow you to implement your backup and recovery plan. The number of tapes, disks, or drives you need depends on
  
  How much data you'll be backing up
  How often you'll be backing up the data
  How long you'll need to keep additional data sets
• The typical way to implement backups is to use a rotation schedule whereby you rotate through two or more sets of tapes, disks, or files. The idea is that you can increase media longevity by reducing media usage and at the same time reduce the number of actual tapes, disks, or files you need to ensure that you have data on hand when necessary.
  Best Practice For important databases, I recommend using four media sets. Use two sets for regular rotations. Use the third set for the first rotation cycle at the beginning of each month. Use the fourth set for the first rotation cycle of each quarter. This technique allows you to recover the database in a wide variety of situations.
  Using Backup Strategies
  The backup strategies are based on the type of database as well as the type of data. Two key things to keep in mind when planning a backup strategy are shown on the following page.
• The master database stores important information about the structure of other databases, including the database size. Any time database information or structure changes, master can get updated without your knowing about it. For example, the size of most databases changes automatically, and when this happens master is updated. Because of this, often the best backup strategy for master is to schedule backups every other day and to rotate through several backup sets so that you can go back to several different versions of master if necessary.
• You can use transaction logs to recover databases up to the point of failure and up to a point of work. To recover to a point of work you must insert named log marks into the transaction log using BEGIN TRANSACTION WITH MARK. You can then recover to a mark in the log using RESTORE LOG WITH STOPATMARK or RESTORE LOG WITH STOPBEFOREMARK.