Chapter 14
Backups



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.

What Is a Database Backup (Dump)?

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.

What Is a Transaction Log Backup (Dump)?

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.

What Is a Table Backup (Dump)?

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.

Performing Database, Transaction Log, and Table Backups

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.

Backup and Restore Tabs

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."

Backup Devices Frame

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.

The Steps to Perform a Backup

To back up a database, transaction log, or table, follow these steps:

  1. From the Database Backup/Restore dialog box, select a backup device from the Backup Device frame by clicking the device.

  2. Using the Database Backup combo box, select the database for which you want to perform the database, table, or transaction log backup.

  3. Select the Entire Database radio button to perform a database backup; select the Transaction Log radio button to perform a transaction log backup; select the Table radio button to perform a table backup. If you select the Table radio button, you must also select the table you want to back up from the drop-down list box under the Table radio button.

  4. To write over an existing backup on the selected device, select the Initialize Device checkbox. Note: You must select Initialize Device when attempting to back up to a newly created backup device (that is, one that has never been used). The Skip Tape Header and Eject Tape at End of Backup checkboxes are specific to tape drive devices. If the Skip Tape Header box is selected, SQL Server skips reading the ANSI label on the tape. If the Eject Tape at End of Backup checkbox is selected, SQL Server rewinds and unloads the tape at the end of the backup.


    NOTE: The capability to append database and transaction log dumps to a disk file dump device is a welcome feature first added to SQL Server 6.0. SQL Server 4.2x did not have this capability and many DBAs were forced to write script files that would execute after a dump was executed and then move the disk dump file to another directory or rename the file so that the next backup would not overwrite the previous backup. I have seen some very good disk dump file management schemes using directories and file-naming conventions. Being able to append to an existing dump device should eliminate many script files and the problem of overwriting previous backups. However, be careful about how much information is appended to a single backup device. The danger in using a single large dump device for an extended period of time with many different databases and transaction log dumps is that you run the risk of losing all your backups with a single media failure.

  5. If you select the Initialize Device checkbox, set one of the following three checkboxes to determine at what time an existing dump device tape or file can be overwritten with new information:
  6. To start the database, table, or transaction log backup, click the Backup Now button in the Database Backup/Restore dialog box. The Backup Volume Labels dialog box appears (see Figure 14.3).

    Figure 14.3.
    The Backup Volume Labels dialog box.

  7. Each backup device involved in the backup is displayed along with the volume label of the backup device. Volume labels can be changed only if the Initialize Device checkbox has been selected. To change the volume label on the backup device, enter the name of the volume in the Volume column. Volume labels can be up to six ASCII characters; the default volume name is SS0001.

  8. To continue the backup, click the OK button in the Backup Volume Labels dialog box. The database, table, or transaction log backup begins writing to the selected devices. The Backup Progress dialog box is displayed (see Figure 14.4).


    Figure 14.4.
    The Backup Progress dialog box.

  9. The Backup Progress dialog box uses a progress indicator to display the progression of the backup. To cancel a backup in progress, click the Cancel button. When the backup is complete, the Backup Progress Completion dialog box is displayed (see Figure 14.5). Note: If you enable the Select into/BulkCopy option for a database and a nonlogged operation such as a bulk copy has occurred, you cannot perform a transaction log dump on the database.

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 master

to 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


Understanding DUMP TRANSACTION Options

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.

TRUNCATE_ONLY

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.

NO_LOG

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.

NO_TRUNCATE

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.

Performing Striped Backups

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:

  1. Select a backup device displayed in the Backup Device frame.

  2. Press and hold the Ctrl key and use the mouse to select backup devices that are to be part of the dump striped set. When you are done selecting back- up devices, release the Ctrl key and click the Backup Now button. The Backup Volume Labels dialog box appears (see Figure 14.7), showing each backup device that is a part of the striped set. Click the OK button to perform the striped backup.

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.

Scheduling Backups

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:

  1. Enter a name for the scheduled task in the Task Name text box in the Schedule Backup dialog box or accept the default name Backup - databasename.


    TIP: Use a descriptive name for the scheduled task. Using a descriptive name makes it easier to identify the task later in the NT event log or in the SQL Server scheduled task history log.

  2. The Transact SQL command scheduled to be executed when the scheduled task runs is displayed in the SQL Command list box. You can edit the SQL command by typing over the existing text or by typing new text.


    TIP: If you were wondering how to use the Enterprise Manager to perform a DUMP TRANSACTION with NO_LOG, TRUNCATE_ONLY, or NO_TRUNCATE, you won't find any checkbox options like the ones in SQL Server 4.21 SQL Administrator! Instead, you must select the device and database for the transaction log dump, click the Schedule button, and then edit the command in the SQL Command list box, adding the transaction log dump options. If you do not back up the transaction log for recovery purposes and rely on full database dumps, you may want to add a DUMP TRANSACTION with TRUNCATE_ONLY command before or after the DUMP DATABASE command in the SQL Command list box. That way, the inactive part of the transaction log will be cleared out when the database is dumped and is scheduled as a single task instead of scheduling two separate tasks (one to dump the database and one to truncate the transaction log). If data modifications occur during backups, back up the transaction log to a backup device for full database restoration instead of using the TRUNCATE_ONLY option.

  3. Select when you want the scheduled backup to occur. For the backup to occur immediately as a background task, select the Immediately radio button. For the task to occur one time only, select the One Time radio button and then set the date and time you want the backup to occur. To set up a recurring backup, select the Recurring radio button. To schedule the recurring backup, click the Change button; the Task Schedule dialog box appears (see Figure 14.9).

  4. Using the Task Schedule dialog box, you can easily schedule the backup to occur daily, weekly, or monthly on a given day or time. (The Task Schedule dialog box is covered in detail in Chapter 26, "Automating Database Administration Tasks.")

    Figure 14.9.
    The Task Schedule dialog box.

  5. After you choose when you want the backup to occur, click OK to schedule the backup.


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.

Dumping a SQL Server 6.5 Database into a SQL Server 6.0 Format

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.

Understanding Database Options and the Transaction Log

The following database options affect your ability to perform transaction log dumps on a database:

Using Database Dumps and Transaction Log Dumps to Recover 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: Full Database Backup Occurs

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.

Day 2: Database Modified, Database Corrupted

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?

Using the Dumps to Recover the Database

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?

Understanding When to Dump a Database and Transaction Log

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.

Category 1: Actions that Warrant Dumping a Database

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:

Category 2: Scheduled Database Dumps

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.

Between the Lines

For database backups, keep in mind the following:

Summary

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.