Information is the fuel that drives the enterprise, and the most critical information is often stored in databases. Databases are where you will 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 is your job as a database administrator to perform backups and store the backups you create in a safe and secure location.
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 deleted accidentally all the time. Mission-critical data can become corrupt. Natural disasters can leave your office in ruins. With a solid backup and recovery plan in place, you can recover from any of these situations. Without one, you are left with nothing after a disaster of any kind.
Creating and implementing a backup and recovery plan takes time. You will 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 questions:
What type of databases 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 do not need to back up master every hour, as you might need 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 databases on a server.
How important is the data in the database? How you judge the data’s importance can help determine when and how you should back it up. Although 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. To protect the data in that development database, you would probably want to make a full backup once a week. For an in-house customer order database that is updated throughout each weekday, you would probably want to 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 in the 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 does not ordinarily change, it does not need to be backed up regularly. On the other hand, a database that is updated nightly should be backed up after the nightly changes are posted. A database that is updated around the clock should be backed up continually.
How quickly do you need to recover the data? It is important to consider the amount of time it will take to recover lost data 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, for example, you might 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 do not have the hardware, you cannot 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.
What is the best time to schedule backups? You will want to schedule backups when database usage is as low as possible. This will speed the backup process. However, in the real world, you cannot always schedule backups for off-peak hours. So you will need to carefully plan when important databases are backed up.
Do you need to store backups off site? Storing copies of backup tapes at an off-site location 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 required to restore operations on a new system.
Note
Availability options, such as log shipping, are not a substitute for backups. Even if you use log shipping, mirroring, or clustering, you will still need to create backups.
Backing up a database differs from backing up a server or a workstation, primarily because you 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:
Full database backups. Used to 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 portions of the transaction log that are needed while the backup is in progress. Because of this, you can use a full 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 full backup. Because you store only the changes, this type of backup is faster and you can perform it more often. As with full backups, differential backups include portions of the transaction logs that are needed to restore the database to the time when the backup operation finishes.
Tip
You can use differential backups only in conjunction with full backups, and you cannot perform differential backups on the master database. Do not confuse differential backups with incremental backups. Differential backups record all changes since the last full backup (which means the amount of data that is backed up grows over time). Incremental backups record changes since the most recent full or incremental backup (which means the size of the data backed up incrementally 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 full 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 if you are dealing with large databases and you want to back up individual files rather than the entire database in order to save time. 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 cannot 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.
Note
Full-text catalogs are treated as a type of database file and are associated with a specific filegroup. You set the location of the catalog when you create the catalog. Whenever the associated filegroup is backed up or restored, the catalog will be backed up or restored as well.
SQL Server 2005 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. Three recovery models 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 2005 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. Using 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 will 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. The model database 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 SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
If you 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.
Expand the Databases folder. If you are configuring recovery for a system database, expand the System Databases folder as well.
Right-click the database you want to change and choose Properties. This displays the database’s Properties dialog box.
Use the Recovery Model drop-down list on the Options page 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.
SQL Server 2005 includes several features that allow you to create standby servers. The three general types of standby servers are:
Hot standby server. An automatically updated server that automatically comes online if a primary server/database fails.
Warm standby server. An automatically updated server that must be brought online manually if a primary server/database fails.
Cold standby server. A manually updated server that must be brought online manually if a primary server/database fails.
Database mirroring, log shipping, and database copies allow you to create standby servers. You use database mirroring to establish a hot standby server, called a mirror server, on which the database is continuously brought up to date and to which failover can occur automatically if the primary database fails. You use log shipping to establish a warm standby server, called a secondary server, on which the database is automatically updated from log backups, but which must be brought online manually if the primary database fails. You create a copy of a database to establish a cold standby server, on which the database is manually updated and which must be brought online manually if the primary database fails.
Mirroring allows you to create hot standby servers. You can mirror any database except for master, msdb, temp, and model. You can configure and enable mirroring using the Mirroring page in the Database Properties dialog box. As discussed in the subsection titled "Ensuring Connectivity and Data Access" in Chapter 2, mirroring requires up to three servers: a principal server, a mirror server, and a witness server.
Backups are not used with mirrored databases in the same way as they are with other databases. When mirroring is configured, backups of a principal database are used to initialize the mirror database on the mirror server. As part of the mirror creation process, you can back up and restore individual files and filegroups. However, you must restore all files and filegroups before you begin mirroring. If you only want to work with a subset of a database and its objects, use replication instead, as discussed in Chapter 12.
When mirroring databases, remember the following information:
While database mirroring is active, you cannot back up or restore the mirror database.
Although you can back up the principal database, you cannot use BACKUP LOG WITH NORECOVERY.
You cannot restore the principal database (that is what mirroring is for). The mirror will correct itself after failover.
Databases that are replicated present a special problem for backup and restoration planning, primarily because the traditional database architecture is extended to include three server roles (which all have related databases):
Publisher. A server that makes data available for replication, tracks changes to data, and maintains other information about source databases. Each publisher has a publication database.
Distributor. A server that distributes replicated data and stores the distribution database. Each distributor has a distribution database.
Subscriber. A destination server for replication. The subscriber databases store the replicated data, receive updates, and in some cases can also make changes to data. Each subscriber has a subscription database.
As with other system databases, you should regularly back up the publication, distribution, and subscription databases. At the publisher, distributor, and all subscriber servers, you should back up both the master and msdb system databases at the same time as you back up the replication databases. When you restore the publication database, you should also restore the master and msdb databases at the publisher server. When you restore the distribution database, you should also restore the master and msdb databases at the distributor server. When you restore the subscription database, you should also restore the master and msdb databases at the subscriber server.
Subscription database backups should be no older than the shortest retention period of all publications to which the subscriber subscribes. If the shortest retention period is 10 days, the backup you plan to restore should be no older than 10 days. To ensure successful recovery of a subscription database, subscribers should synchronize with the publisher before the subscription database is backed up. They should also synchronize after the subscription database is restored. Synchronizing prior to backup helps ensure that if a subscriber is restored from backup, the subscription is still within the publication retention period.
You can restore replicated databases to the same server and database from which the backup was created or to another server or database. If you restore a backup of a replicated database to another server or database, replication settings are not preserved, and you will need to recreate all publications and subscriptions after backups are restored, except in the case of log shipping. If you use log shipping, you can restore a replicated database to a standby server and the replication settings are preserved.
With merge replication, any replication-related changes should be captured in the log backups. If you do not perform log backups, the publication database should be backed up whenever a setting relevant to replication is changed. After restoring the publication database from a backup, you should either synchronize the publication database with a subscription database or reinitialize all subscriptions to the publications in the publication database. You can synchronize the publication database or reinitialize subscriptions as discussed in the section titled "Subscribing to a Publication" in Chapter 12. Be sure to check the identity ranges in tables that contain IDENTITY columns after restoring a database.
Note
In merge replication, the distribution database has a limited role. It does not store any data used in change tracking, and it does not provide temporary storage of merge replication changes to be forwarded to subscription databases (as it does in transactional replication).
With transactional replication, you set the Sync With Backup option on the distribution and publication databases. You should:
Turn this option on for the distribution databases to ensure that transactions in the log of the publication database will not be truncated until they have been backed up at the distribution database. This allows the distribution database to be restored to the last backup, and any missing transactions then can be delivered from the publication database to the distribution database while replication continues unaffected. Although this has no effect on replication latency, it can delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up.
Turn this option on for the publication database if your application can tolerate additional latency to ensure that transactions are not delivered to the distribution database until they are backed up at the publication database. This allows you to restore the last publication database backup at the publisher without any possibility of the distribution database having transactions that the restored publication database does not have. Latency and throughput are affected because transactions cannot be delivered to the distribution database until they have been backed up at the Publisher.
If you must develop a plan to back up and restore very large databases, you may want to take advantage of parallel backup and restore. The parallel backup and restore process allows SQL Server to use multiple threads to read and write data. This means SQL Server can read data from, and write data to, multiple data sources. The backup and restore process uses 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 as it initializes a database that it is creating for the restore process if 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 this information, 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.
After you determine the backup operations to use on each database and how often you want to back up each database, you can select backup devices and media that meet these requirements. The next section covers backup devices and media.