by Mark Spenik
Backups are copies of SQL Server databases or transaction logs used to recover a database if the database becomes corrupted or is lost in a catastrophic event. The task of backing up and protecting the data is probably the number one job responsibility of the DBA. I have been to many organizations where the sole job responsibility of the DBAs is backup and database maintenance. Being able to recover a database when several disk drives crash or a table or database becomes corrupted is when good DBAs earn their keep. Remember that your ability to recover depends on current backups of your databases. SQL Server 6.5 has made many improvements and enhancements to the backup and recovery process. For example, you can now back up and restore an individual table. One improvement that will greatly simplify your life is that SQL Server 6.5 maintains a history of database backups in the msdb database; this history allows you to back up a database without knowing on which devices the backup is stored.
In this chapter, you learn about the different methods available to you to back
up SQL Server databases, tables, and transaction logs. Even more important, you learn
how to create a backup and recovery strategy.
NOTE: Database, table, and transaction log backups are commonly referred to as dumps (that is, a database dump, a table dump, or a Transaction log dump). The name dump comes from the SQL Server Transact SQL command used to back up databases and transaction logs: the DUMP command. Having worked with SQL Server for many years, I use the terms dump and backup interchangeably--as you will in the real world. As mentioned in Chapter 8, "Managing Devices," before SQL Server 6.5, a backup device was referred to as a dump device; don't be confused if you see a reference to a dump device--it's just another (older) name for a backup device.
Better Safe than Sorry!
I learned the value of having a good set of database backups the hard way. Several years ago, while working on a Sybase 4.2 UNIX system, I was hired as a contractor to help out an organization with several large Sybase databases. They had recently lost several key MIS employees and were short handed. I was working with a gentleman whom I'll refer to as Don, "The Man," one of the few really good jack-of-all trades (UNIX system administrator, Sybase DBA, and client/server developer) individuals I have met. I had been at the organization for only two days; Don and I were busy trying to get a clean set of backups for the database and the UNIX system.
The nightly UNIX backup had failed for the last two days and the database backups were going slow because several databases were flagged with errors during DBCC (database consistency check). We were working with Sybase tech support to correct the database problems. Don was preparing one of the larger databases for a backup when I heard him exclaim "Oh no! I can't believe I did that, I can't believe what I just did. We are doomed!" It turned out that Don had entered a SQL statement incorrectly that had started one large transaction that was deleting all the rows of a table with about four million rows! I said, "No problem. We'll just recover from a database backup." It was then that Don informed me that the only database backup was 30 days old and outdated! What to do?
Because Don had blocked the delete within a single transaction, we killed the server before it could complete the operation and commit the transaction. The server took a while to come back up as several thousand transactions were rolled back. We then verified the table row count from some numbers taken earlier that day to verify that no data had been lost (information was always added to the table, but never updated). At this point, Don and I realized that we needed backups--now. We stayed late that night verifying previous backup tapes that we found could not be read because of media problems. We continued to work early into the morning and created a whole new set of UNIX system backups and database backups.
The moral of the story is "backups are serious business!" Too often I have heard someone say, "The nightly backup did not run; not sure why, but I'll run it again tonight." Big mistake. Remember that you don't know the hour, day, or minute when the disk drive will give up, the building will be hit by a natural disaster, or someone like Don will issue a SQL command that will ruin the production database! If you don't have a good backup, you will find yourself trying to explain to your boss how you lost a day's worth of data because the nightly backup did not work and you did nothing about it that morning.
Think of a database backup as a full backup of a database. When you perform a
database backup, SQL Server copies all user-defined objects, system tables, and data
to the backup device, including the transaction log. Remember from Chapter
8, "Managing Devices," that backup devices can be files, floppy drives,
or tape drives. SQL Server 6.x supports backup devices on a Novell file server and
can perform striped backups to multiple devices.
TIP: You can dump (back up) a database or transaction log while the database is in use. When a database DUMP command is issued, SQL Server writes all completed transactions to disk and then begins to copy the database. Any incomplete transactions or transactions that occur after the database dump process is started are not backed up. To back up changes that occurred while the backup process was running, you must dump the transaction log after the backup completes. Backing up a database slows SQL Server down, so consider performing backups during nonpeak hours. Note: When backing up a table, an exclusive lock is placed on the table, prohibiting user access to the table until the table backup is complete.
If you think of a database dump as a full backup, think of a transaction log dump
as an incremental database backup (as stated in Chapter 9,
"Managing Databases," a transaction log contains all the various transactions
that have occurred on a database before the last transaction log dump).
NOTE: If you create your database and transaction log on the same device, you cannot perform an incremental (transaction log) backup.
A transaction log backup performs one or both of the following operations, depending on the dump options selected:
The inactive part of the transaction log is all the completed transactions up
to and not on the same page as the earliest outstanding transaction or the earliest
transaction that has not been moved to the distribution database and is marked for
replication.
NOTE: Performing a full database backup does not clear out the inactive part of the transaction log. If you only perform database backups, eventually your transaction log will fill up and you will be unable to perform any transactions in the database (no INSERT, UPDATE, or DELETE actions) until you dump the transaction log. You have to perform transaction log dumps, even if you rely on full database backups, to clear out the inactive part of the transaction log.
A table backup is a copy (snapshot) of a single table within the database. Table
backups are a new feature in SQL Server 6.5. Backing up an individual table allows
you to perform selective backups that use fewer system resources and save time because
you are backing up only a table and not the entire database. You can also use a table
backup to move an individual table to another database--for example, if you want
to manually synchronize a table between a publisher and subscriber for data replication
or in any particular case for which your only previous option was BCP (BulkCopy).
You cannot back up a table with columns that have text or image datatypes.
CAUTION: Although being able to back up an individual table is a powerful enhancement, be careful how you use this feature. You can get into a lot of trouble loading a table that has many different relationships (for example, one that has FOREIGN KEY constraints) with other tables. Loading a copy of a table into such an environment can cause data inconsistencies between the tables. Data in the table you are loading may have changed since the last backup, cascading changes to other tables. Other tables that participate in relationships with the table may have changed since the last table backup (for example, there may be referential integrity problems). Microsoft's documentation recommends restoring from a table backup only in disaster recovery situations. As such, I would rely on transaction log dumps to pick up any changes that occur in the database and database dumps to recover a database. I recommend using table dumps only in cases where a snapshot of the table makes sense (for example, tables that have no FOREIGN KEYS or relationships), such as when you move a single table to another server.
Examine the steps required to perform a database backup or transaction log backup using the SQL Server Enterprise Manager.
TIP: Before running a database backup, it is good practice to run database maintenance commands. The commands recommended by Microsoft documentation and backed up by real-world experience are the following DBCC commands: CHECKDB, NEWALLOC (or CHECKALLOC), and CHECKCATALOG. It is important to keep up with database maintenance even though it adds time to the backup process. A database backed up with errors will have the same errors on a restored database and, in some severe cases, may prevent a successful restoration of the database. Also consider performing DBCC commands after the backup occurs to validate that the database is still in good order (because changes to the database can occur during backups).
From the Enterprise Manager, select Tools and then select Database Backup/Restore. The Database Backup/Restore dialog box appears (see Figure 14.1).
Figure 14.1.
The Database Backup/Restore dialog box.
NOTE: When SQL Server performs a database or transaction log backup, SQL Server reads one extent at a time (an extent is eight 2K data pages), skipping unallocated extents, and writes the extent to the database device.
The following sections quickly review the options in the Database Backup/Restore dialog box.
The Database Backup/Restore dialog box has two tabs: the Backup tab and the Restore tab. In this chapter, you concentrate on the functionality provided with the Backup tab. The functionality of the Restore tab is covered in Chapter 15, "Recovery."
The Backup Devices frame contains a list and icon representations of the backup devices on the server. The functions performed by the three buttons located at the bottom of the Backup Devices frame are as follows:
LOAD HEADERONLY
From dump device
Figure 14.2.
The Backup Device Information dialog box.
NOTE: Some of the improved features added to SQL Server 6.x are the enhancements for backing up to a disk file instead of to a tape. Many of the features that existed for tape backups in previous versions (such as displaying header information or multiple dumps to a single tape) have now been implemented with disk file dump devices. In version 6.5, the procedures to back up to tape and disk file dump devices are almost identical except for one or two option flags specific to tape drives.
To back up a database, transaction log, or table, follow these steps:
Figure 14.3.
The Backup Volume Labels dialog box.
Figure 14.4.
The Backup Progress dialog box.
Figure 14.5.
The Backup Progress Completion dialog box.
NOTE: To dump a database or transaction log to the floppy disk dump devices diskettedumpa or diskettedumpb, you must run the Console utility program from a DOS shell. Alternatively, you can dump the database or transaction log to a disk file backup device and then copy the dump file to the floppy disk.
The Transact SQL command used to back up the database and transaction log is the DUMP command, which has the following format for a database:
DUMP DATABASE {dbname | @dbname_var}
TO dump_device [, dump_device2 [..., dump_device32]]
[WITH [[,] {UNLOAD | NOUNLOAD}]
[[,] {INIT | NOINIT}]
[[,] {SKIP | NOSKIP}]
[[,] {{EXPIREDATE = {date | @date_var}}
| {RETAINDAYS = {days | @days_var}}]
[[,] STATS [ = percentage]]]
To dump a transaction log, use the following format:
DUMP TRANSACTION {dbname | @dbname_var}
[TO dump_device [, dump_device2 [..., dump_device32]]]
[WITH {TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE}
[[,[{UNLOAD | NOUNLOAD}]
[[,] {INIT | NOINIT}]
[[,] {SKIP | NOSKIP}]
[[,] {{EXPIREDATE = {date | @date_var}}
| {RETAINDAYS = {days | @days_var}}]]
SQL Server also recognizes the following shortcut syntax (rather than DUMP TRANSACTION) to dump the transaction log:
DUMP TRAN
To dump a table, use the following format:
DUMP TABLE [[dbname.]owner]Table_Name
TO dump_device [, dump_device2 [..., dump_device32]]
[WITH [[,] {UNLOAD | NOUNLOAD}]
[[,] {INIT | NOINIT}]
[[,] {SKIP | NOSKIP}]
[[,] {{EXPIREDATE = {date | @date_var}}
| {RETAINDAYS = {days | @days_var}}]
[[,] STATS [ = percentage]]]
For all three DUMP DATABASE, DUMP TRANSACTION, and DUMP TABLE commands, dump_device has the following format:
{dump_device_name | @dump_device_namevar}
| {DISK | TAPE | FLOPPY | PIPE} =
{`temp_dump_device' | @temp_dump_device_var}}
[VOLUME = {volid | @volid_var}]
The optional parameters INIT and NOINIT, available with tape
devices on SQL Server 4.2x, are now available for other backup devices on SQL Server
6.x. Use the INIT option to overwrite the information stored on the dump
device. Use NOINIT to append the information. Remember that the capability
to overwrite a device also depends on the expiration and retention dates set for
the backup device.
Temporary Backup Devices
SQL Server 6.x enables you to create and use temporary backup devices when backing up databases or transaction logs. A temporary backup device is a backup device that is created at the time of the DUMP command and that has not been added to the system table sysdevices with the system stored procedure sp_addumpdevice. To back up a database to a temporary backup device, you must specify the type of media the backup device is on (use the options DISK, FLOPPY, TAPE, or PIPE) and then specify the complete path and filename. In the case of PIPE, you must specify the name of the named-pipe used in the client application. You can also use variables to create a temporary backup device. Look at some examples using temporary devices.
Example: Dump the master database to a temporary disk backup device called tdump_master.dat, located in the directory C:\MSSQL\BACKUP. Using the path and filename:
DUMP DATABASE masterto DISK='C:\MSSQL\BACKUP\tdump_master.dat'
Using a variable:
Declare @temp_dump varchar[255]
Select @temp_dump = `C:\MSSQL\BACKUP\tdump_master.dat'
DUMP DATABASE master
to DISK = @temp_dump
The different options available for a transaction log and when to use the different options can be confusing to new DBAs. Examine each of the options individually and determine the correct time to use them.
The TRUNCATE_ONLY option removes the inactive part of the transaction log (truncates) without backing up (copying) the log to a dump device. You do not have to specify a dump device when using TRUNCATE_ONLY because the log is not copied to a dump device. For example, the syntax to dump the master database transaction log with the TRUNCATE_ONLY option is as follows:
DUMP TRANSACTION master
WITH TRUNCATE_ONLY
Use the TRUNCATE_ONLY option in the following cases:
CAUTION: Always perform a full database backup before using the TRUNCATE_ONLY option. If you use the TRUNCATE_ONLY option without a full database backup, you will not be able to recover the completed transactions in the inactive part of the transaction log at the time the DUMP TRAN with TRUNCATE_ONLY command was issued.
When a DUMP TRANSACTION command is issued with the NO_LOG option,
SQL Server truncates the inactive part of the transaction log without logging the
DUMP TRANSACTION command.
CAUTION: After using the NO_LOG option, always perform a full database backup; otherwise, the changes that had been in the transaction log at the time the log was truncated with the NO_LOG option will not be re-coverable.
Use the NO_LOG option only when the transaction log has become so full that you cannot dump the transaction log normally to free up space. This occurs when SQL Server is attempting to log the DUMP TRANSACTION command with no room left in the transaction log. Like the TRUNCATE_ONLY option, the NO_LOG option does not require a database device because the log is not copied to a device.
TIP: Microsoft SQL Server has added a feature, available with the first NT release, called a threshold dump (or threshold alert). The threshold dump monitors the space in the transaction log and prevents the log from filling up by performing a transaction dump when the log reaches a user-defined threshold. See Chapter 26, "Automating Database Administration Tasks," to learn how to set up a transaction log threshold. Once you have set up SQL Server to automatically dump transaction logs, you should not have to use the NO_LOG option. If you do need to use the NO_LOG option, you can execute the command by clicking the Truncate button on the database's Edit Database dialog box.
Use the NO_TRUNCATE option when the database you are trying to access is corrupted and you are about to rebuild the database. To use NO_TRUNCATE, the following must be true:
The NO_TRUNCATE option writes all the transaction log entries from the
time of the last transaction dump up to the point of the database corruption. You
can then load the transaction log dump as the last dump in the recovery process for
up-to-the-minute data recovery.
TIP: Become familiar with the NO_TRUNCATE option. I have met many DBAs who were unfamiliar with the option or were not sure when to use it.
SQL Server 6.x adds the capability to perform a database, table, or transaction log backup to multiple backup devices called parallel striped backups. A parallel striped backup lessens the amount of time required to back up a database, table, or transaction log by creating a single thread for each backup device. The backup device threads read an allocated extent in a round-robin fashion and then write the extents to the thread's assigned backup device, taking advantage of asynchronous I/O capabilities. An example of a parallel striped backup is shown in Figure 14.6.
Figure 14.6.
An example of a parallel striped backup.
The database, table, or transaction log can be dumped to multiple devices of the
same or different media (tape, disk, and so on) called the striped set. If
a database or transaction log is dumped to multiple devices, it must also be read
from multiple devices during restoration. SQL Server can perform parallel backups
from 2 to 32 database backup devices. To perform a parallel striped backup to multiple
devices using the DUMP DATABASE command, list the backup devices separated
by commas. For example, to back up the master database to three backup disk
devices called dump1, dump2, and dump3, the syntax is
as follows:
DUMP DATABASE master
to dump1, dump2, dump3
To use the SQL Server Enterprise Manager to perform a parallel striped backup, access the Database Backup/Restore dialog box (refer back to Figure 14.1). Select the database, table, or transaction log you want to back up and set the appropriate backup options. To create a striped dump set, follow these steps:
Figure 14.7.
The Backup Volume Labels dialog box with a backup striped set.
Use the striped parallel backup feature when you have very large databases or are
in a production environment that tolerates little or no downtime.
CAUTION: When you use a striped dump set and then have to restore the transaction log, table, or database, you must read the entire striped set. SQL Server 6.5 simplifies this requirement if you restore from a backup using backup history information that tracks all the devices used to create the backup. If any of the striped devices fail, you cannot recover the data. Remember that striping is not done to protect the data; it is done to speed up the system.
Microsoft SQL Server 6.x excels with scheduling capabilities (the scheduling capabilities
and improvements are covered in Chapter 26, "Automating
Database Administration Tasks"). Setting up SQL Server to automatically perform
database and transaction log backups is a snap. To use SQL Server scheduling, you
must run the SQL Server Executive service installed during SQL Server installation.
NOTE: SQL Server 6.x backup scheduling capabilities have improved substantially over those in SQL Server 4.21. SQL Server 4.21 scheduling involved running the SQL Monitor. I heard many complaints and problems about scheduled backups and the SQL Monitor under SQL Server 4.21. For example, if a backup took an extended period of time and the SQL Monitor properties in the system registry for the backup time were not set high enough to allow the backup to complete, SQL Monitor would halt the backup. I also ran into a few people who used the NT scheduler and AT command instead of SQL Monitor to schedule backups. SQL Server 6.x scheduling has improved substantially; I recommend using the new features along with the SQL Server Executive service.
Backups are scheduled from the Database Backup/Restore dialog box (refer back to Figure 14.1). To schedule a database, table, or transaction log for backup, select the appropriate options and the backup device(s), and then click the Schedule button. The Backup Volume Labels dialog box appears (refer back to Figure 14.3). Use the default volume label or assign a volume label to the selected backup device(s) and click the OK button. The Schedule Backup dialog box appears (see Figure 14.8).
Figure 14.8.
The Schedule Backup dialog box. To schedule the backup, follow these steps:
Figure 14.9.
The Task Schedule dialog box.
NOTE: The Options button on the Schedule Backup dialog box allows you to set up e-mail notification on the success or failure of the scheduled backup, the number of retries, and whether the event is to be written to the event log. The Options button is covered in detail in Chapter 26.
The success or failure of the scheduled task can be viewed from the task scheduling window using the Enterprise Manager (select the Server menu and then select Scheduled Tasks). Scheduled backups and user-initiated backups are now written to the NT event log.
You can create a database backup from SQL Server 6.5 that can be loaded into SQL Server 6.0. To create a 6.0 backup, the following must be true before generating the backup:
Once you have met the necessary requirements, perform the normal backup procedures.
NOTE: You can load SQL Server 6.5 databases in SQL Server 6.0 if you use service pack 3.0.
The following database options affect your ability to perform transaction log dumps on a database:
Before you learn how to create a backup database schedule, it is important to understand how to use database dumps and transaction log dumps to restore a database with up-to-the minute information. To help you understand how to use database and transaction dumps in the real world, walk through the following example (see Figure 14.10).
Figure 14.10.
An example of database and transaction log dumps.
Using the example in Figure 14.10, the backup schedule for a database is as follows:
The backup schedule was set up this way because the majority of the people working on the database go to lunch at noon and go home for the evening before 6:00 p.m.. The two incremental backups and the daily full database backup meet the user's recovery needs. Follow through the drawing starting with Day 1.
Day 1 is the starting point for this example. All you are concerned about is that at 9:00 p.m. on Day 1, SQL Server successfully performs a full database backup.
Between 8:00 a.m. and 11:59 a.m., the database users log on to the database and make minor modifications and changes to the data stored in the database.
Between 12:00 noon and 12:59 p.m., many of the database users are at lunch, although some continue to work. The SQL Executive kicks off the scheduled transaction log dump. The transaction log of the database is dumped to a dump device, saving all the changes made to the data since the last full backup at 9:00 p.m. the previous evening.
Between 1:00 p.m. and 5:59 p.m., the database users continue to make minor modifications to the data in the database. By 6:00 p.m., the majority of the users have logged off the database and are on their way home.
Between 6:00 p.m. and 6:59 p.m., the SQL Executive starts the evening transaction log dump, saving all the committed transactions made to the database before the previous transaction log dump at noon. Shortly after the transaction log backup completes, User 1 modifies ten records on the database.
At 7:00 p.m., the database becomes corrupted and users are no longer able to access the database. The DBA is called in to remedy the problem. Now what?
In Chapter 15, "Recovery," the commands and requirements to recover a corrupted database are discussed in detail. In this chapter, an example of a high-level walk- through of restoring the database using the available backups is covered. As you understand from this example, the database has become corrupted. So where do you start the recovery process?
The first thing to do is to dump the transaction log with the NO_TRUNCATE option and then drop the corrupted database and re-create it. Once the database has been re-created, you are ready to restore the data.
Next, you must load a full database backup to the newly created database. So you load the database dump performed on Day 1 at 9:00 p.m. The new database now exists in the exact same state as the corrupted database on Day 1 at 9:00 p.m. But how do you get back the work that was done on Day 2? You guessed it: you use the incremental database dumps (that is, the transaction log dumps).
Transaction log dumps are sequenced and must be loaded in the correct order. You load the first transaction log dump that was made at 12:00 noon on Day 2. Loading a transaction log (also referred to as applying the transaction log) causes the transactions in the transaction log to re-execute. When the transaction log has completed, the database is now in the exact state the database was in as of 12:00 noon on Day 2.
To regain the 12:00 noon until 6:00 p.m. transactions, you load the second transaction log dump performed at 6:00 p.m. on Day 2. Once the second transaction log successfully loads, the database is in the same state as the original database at 6:00 p.m. on Day 2. But what about the ten records modified by User 1 after the transaction log dump completed but before the database was corrupted? If the database and transaction log were on the same device or you forgot to run the DUMP TRANSACTION with NO_TRUNCATE command, those modified records are lost because you do not have a transaction log dump or a full database dump with the modifications in them. User 1 would have to manually go back and update the records. But because you had the database and transaction log on separate devices and you executed the DUMP TRANSACTION with NO_TRUNCATE command, you load the transaction log dump pro-duced by the NO_TRUNCATE dump command, and the database is back in the same state (including the ten modified records) as the original database just before it became corrupted. You now know how transaction logs and database dumps are used to recover a database--but when should you dump your databases and transaction logs?
You know how to schedule and dump transaction logs and databases but when should you dump them? To answer this question, you are going to create two separate categories: Category 1 consists of actions performed in a database that warrant an immediate database dump. Category 2 consists of the dumps required to meet your recovery needs.
In general, you are aware that you should perform database backups on a timely schedule. Backups should also be performed after certain actions occur in a database to ensure full and easy recovery. User Databases After you perform certain actions on a user database, you should dump the database as soon as possible to guarantee the recovery of your changes. For example, perform a database backup in the following cases:
The master Database Of course, the master database has its own set of rules for when it should be backed up. Remember that keeping a healthy master database is a high priority, so backing up the master database regularly is a must. The master database should be backed up when changes are made to system tables. A list of the commands that modify the system tables can be found in the SQL Server documentation. Here is a short list of some of those commands:
Because many of you use the Enterprise Manager to perform your database administrative tasks, you may be unaware of the SQL Server commands and system stored procedures being executed. In Enterprise Manager lingo, back up the master database after you have done the following:
Unfortunately, there is no exact formula to tell you when you should dump your databases. Why? Because each database has its own backup requirements. For example, in the backup and recovery example you stepped through earlier in this chapter, transaction log dumps were performed twice a day. In the example, it was acceptable to lose a half day's work if the example SQL Server suddenly lost all its databases, including the master. Many organizations cannot afford to lose any data and require up-to-the minute recovery.
As another example, maybe you are in a development environment in which a bimonthly database backup is all that is required. Your backup strategy should enable you to recover any of your databases within an acceptable amount of time and for an acceptable data loss limit for each database. Before discussing backup strategies, I want to point out that it is just as important to perform routine database and table maintenance (database maintenance plans are discussed in more detail in Chapter 25, "Developing a SQL Server Maintenance Plan") as it is to properly back up your databases.
In general, you can find more information on setting up appropriate backup schedules
in the documentation that ships with SQL Server or the white papers found on Microsoft
TechNet. Now review a few questions and suggestions you can use to help you set up
a backup plan.
NOTE: In my opinion, a backup plan and a recovery plan are one in the same. To test and verify your backup plan, you must use the database backups to restore your SQL Server databases; thus, the two go hand-in-hand. Also, don't forget that databases require routine maintenance, including the use of DBCC commands.
System Databases Having up-to-date, valid database backups can save you a lot of time, especially if you need to restore a system database (such as the master database). Take special care with the master database; consider mirroring the master device for added protection. I recommend backing up the following system databases (at the minimum) daily:
Transaction Log Threshold Alerts All database transaction logs should have threshold events scheduled to prevent the database transaction logs from filling up. (Enough said!) How Often Should I Dump the Transaction Log Database? If the database and the transaction log both became corrupted, how many trans-actions can you afford to lose? How many transactions are performed in an hour? A day? You must ask and answer these questions and more to determine how often you should dump the transaction log and database. Try to perform your database and transaction log dumps during nonpeak hours.
Also keep in mind what is required to recover a database using full database dumps
and transaction logs. For example, if you perform transaction log dumps (incremental
backups) six times a day and a full database backup every five days, what do you
have to do to recover the database? Depending on when the database became corrupted,
you stand the possibility of having to load a full database backup and 0 to 30 transaction
log dumps. Is this acceptable? Get the picture? If you have a database that is not
updated very often, performing a biweekly transaction log dump and a weekly database
dump may meet your requirements.
How Do I Manage the Dumps? How are you going to manage the various database
and transaction log dumps on tapes or dump files and how long are you going to keep
your backups? Believe me, this is a problem in organizations with several databases.
Organization is the key here. Come up with a consistent naming convention and filing
system for your backups. You will want to keep old backups around for several weeks
or months. Organization makes it easy to find dumps that are several weeks or months
old.
How Long Will It Take to Recover the Database? If your database becomes corrupted,
how long will it take to recover the database? Is the recovery time acceptable? If
you find that the recovery time is not acceptable, you may have to consider hot backups.
A hot backup is a term given to a system that uses specialized hardware to
mirror the main database server; the hot backup can be used immediately if the main
database server goes down. SQL Server 6.5 now provides a mechanism to support fallback
recovery when two computers share the same hard drive. If one computer fails, the
other computer takes over. The bottom line is that by using a hot backup or fallback
recovery configuration, you decrease the chance that you will experience any downtime.
In What Other Ways Is the Database Protected? It never hurts to have more
than one level of data protection for very sensitive data. For example, is the database
on a device that is mirrored or does it reside on a RAID 5 drive configuration? Is
the SQL Server shut down weekly and the SQL Server directory and database devices
backed up to tape by a system administrator? Always know what other recovery options
are available to you, just in case your well-constructed backup and recovery plan
fails.
Test and Practice Once you have created a backup plan, don't stop there and
say, "Well, I've got a plan--I'm done." Make sure that you test your backup
plan by actually recovering the databases. And when you are done testing, test your
recovery plan again. When the day comes and a database fails, you should feel very
comfortable and confident in your ability to recover the database. The bottom line
is, test and practice your backup and recovery plans.
For database backups, keep in mind the following:
Maintaining a good set of database backups is one of the most important responsibilities of a DBA. Use the ideas and suggestions in this chapter to help build your own backup plan. The next chapter examines the other side of the coin: how to use the database and transaction log backups to restore or move a database.