Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 8

Backing Up and Restoring


Yesterday you examined permissions and how to set them in order to secure your database. SQL Server is an inherently secure system; users can't do anything unless you explicitly grant them permissions to perform a particular action.

Today you turn to what is perhaps the most important but least glamorous aspect of supporting a relational database: backup and recovery. No matter how much work you do to make your database secure and to make your data available, data protection is the most critical aspect of your job. If your server crashes and all your data is lost, it's likely you've lost something else as well--your job! Start by examining protection you can provide so that you don't lose disks and your server stays up and running.

To begin that process, you examine fault tolerance protection for disks on your server, look at SQL Server mirroring, and examine backup and recovery. Backup and recovery is all about preventing data loss; implementing fault tolerance for disks and devices also guards against data loss. However, implementing fault tolerance doesn't imply that you don't need to back up your databases.

Mirroring, Duplexing, and Striping

It's easiest to start by defining some key terms. Two disks are said to be mirrored if they contain the same data. When one disk is modified, the other is also modified. Duplexing implies that the disks are mirrored, but a separate disk controller device (such as SCSI cards) controls each disk. This is different from mirroring because mirrored disks are assumed to use the same disk controller. Striped disks imply that the data is distributed evenly across multiple disks, with part of each logical piece of data on each disk in the stripe set of disks.

All of this fits into a scheme known as RAID (Redundant Array of Inexpensive Disks). You configure disks into these RAID arrays to protect data on the disks and to provide high availability. If you can prevent your data from becoming unavailable, then you can insulate your users from most hardware failures. There are six commonly accepted RAID levels.

RAID 0 Striped sets without parity information
RAID 1 Disk mirroring or duplexing
RAID 2,3,4 Iterations leading to RAID 5 (rarely implemented)
RAID 5 Striped sets with parity information evenly distributed within the stripe


TIP: Parity information is data that, when combined with the rest of your disks (minus one missing disk), has enough information to rebuild the missing data from the failed disk.

RAID 0 provides no fault tolerance in and of itself. However, it can be of benefit for performance, and is discussed again on Day 20, "Performance Tuning and Optimization." Windows NT server has built-in support for RAID 0 striping.

RAID 1 is either mirroring or data duplexing. If you lose the SCSI card controlling your mirrored disks, the data on those disks becomes unavailable. You can lose one of the disks and continue to access the data. If you duplex your disks, you can lose either a single disk or a disk controller card and still access your data. Windows NT Server also has built-in support for RAID 1 mirroring and duplexing.

RAID levels 2, 3, and 4 were iterations of striping with parity that were functionally replaced with RAID 5. RAID 5 is a set of three or more disks logically grouped. Each disk either contains a stripe, or section of data, or parity information. In RAID 5, this parity information for each stripe is distributed evenly throughout the set of disks. For example, if your RAID stripe set included three disks, it might look like this:

Disk 1 Disk 2 Disk 3
ROW 1 DATA DATA PARITY
ROW 2 PARITY DATA DATA
ROW 3 DATA PARITY DATA

In ROW 1, half the data is on Disk 1, the other half is on Disk 2, and parity information (which when combined with either of the disks is enough information to rebuild the missing data) is on Disk 3. As you add disks to the set, it becomes more efficient in terms of overhead, with a higher risk of having more than one disk become unavailable at the same time.

RAID 5 is generally accepted as the most inexpensive option; it has the least overhead, especially as you increase the number of disks. Mirroring or duplexing is considered to be fairly expensive, as it requires you to double the amount of disk space you need to hold the same amount of information. For example, if you wanted to have 12GB of data space and implemented a RAID 5 array with 4GB SCSI disks, you would need at least four disks (because you effectively lose one disk total for parity, so only three disks are available for data storage, and 3x4 = 12GB). To implement the same amount of storage with mirroring, you need six 4GB disks.

You can implement hardware RAID solutions or use Windows NT Server's built-in RAID capabilities. In general, hardware solutions are recommended if you can afford them. They tend to have the least amount of overhead. Windows NT Server's capabilities are preferred, however, when compared to having no protection. Configuring Windows NT fault tolerance features is outside the scope of this book; however, you can examine the Disk Administrator program in the Administrative Tools program group for further information on these topics. Hardware RAID configuration depends upon the brand of server you have purchased.

SQL Server Mirroring

SQL Server 6.5 also has mirroring support built in. SQL Server mirroring works at the device level. Following SQL Server mirroring for a device being enabled, each time a change is made to the device, it is written to the original file represented by the device and the mirrored copy of that file. SQL Server mirroring should be used as a last resort. Both hardware mirroring and Windows NT mirroring are preferred, and most likely perform better. Microsoft has also announced that SQL Server mirroring will not be supported in a future release of the product.

However, if you have no other choice, SQL Server mirroring is better than no protection at all. Mirroring databases requires that you mirror all the devices being used by each database. If you decide to implement SQL Server mirroring, you should make sure that you mirror all devices related to each database you want to protect. It's also an exceptionally good idea to mirror the master device, because it contains your master database and all server-wide configuration information.

You can mirror devices at any time, and then unmirror them as well. Mirroring should be done during off-peak hours because mirroring devices is a very I/O intensive process. It doesn't require any special mode for either the server or the databases affected by your mirroring operations. Use the DISK MIRROR statement to mirror a device.

DISK MIRROR
    NAME = `logical_name',
    MIRROR = `physical_name'
    [, WRITES = {SERIAL | NOSERIAL}]

NAME is the logical name of the device you want to mirror.

MIRROR is the physical path and filename of the device SQL Server should mirror the named device onto.

The WRITES parameter is optional. If you don't specify, the SERIAL option is assumed. This means that writes to the primary device are completed before writes to the new mirrored device begin. NOSERIAL means that the writes may occur at the same time. According to the SQL Server Books Online, writes are always SERIAL on SQL Server 6.5.

As with any DISK statement, only the SA can run this command.

When you run this command, a new file is created with the name you specified, and the device mirroring process begins. You get a message to your query window for each allocation unit that is copied. When the entire device is mirrored, control is returned to your query application. The sysdevices system table in master is modified, and the name of the mirrored device appears in the mirrorname column for the device you have mirrored.

From this point forward, every time a write occurs to the device you mirrored, the mirrored file is also modified. For example, to mirror your master device, you could type the following:

DISK MIRROR
    name = `master',
    Mirror = `d:\mssql\data\master.mir'

You should put the mirrored file on a separate disk, but it is not required to run this command.

You can either temporarily or permanently turn off mirroring. You perform either of these operations with the DISK UNMIRROR command.

DISK UNMIRROR
    NAME = `logical_name'
    [, SIDE = {PRIMARY | SECONDARY}]
    [, MODE = {RETAIN | REMOVE}]

NAME is the name of the logical device that was previously mirrored.

SIDE indicates which of the devices you want to retain. PRIMARY means that you don't want to use the original device that you mirrored any longer. SECONDARY means that you no longer want to retain the mirror file. SECONDARY is the default if you don't specify this parameter.

MODE indicates whether you are disabling mirroring temporarily or permanently.

REMOVE means you are permanently removing the pointers to the mirror. If you chose PRIMARY for the SIDE parameter, the mirrored file replaces the original file in the phyname column in the sysdevices system table in master, and the mirrorname column is erased. REMOVE does not delete the physical file that you chose with the SIDE parameter.

RETAIN is the default if you don't specify the MODE parameter. When specified, the reference to the mirror in the mirrorname column in the sysdevices system table is not removed. A status bit is set in the table to indicate that the appropriate file should not be used, based on the SIDE parameter.

Why would you temporarily unmirror a device? One reason SQL Server mirroring is recommended only as a last resort is because it performs poorly when compared to Windows NT or hardware mirroring. If you were to perform a large copy operation or other process that would require significant amounts of I/O, it might be beneficial to turn off mirroring, run the operation, then resume the mirror.

To unmirror your master device, you could run this code:

DISK UNMIRROR
    Name = `master',
    Side = SECONDARY,
    Mode = REMOVE

This permanently removes the mirror and sets you back to using your original master device. This command runs almost instantaneously, because it's merely removing the mirror entry from the sysdevices system table, and not deleting the physical file. To keep the mirror file and remove the original, you would have typed:

DISK UNMIRROR
    Name = `master',
    Side = PRIMARY,
    Mode = REMOVE

This removes your primary master device, and makes your mirrored device the primary master device.


NOTE: Some people use the following technique to move a device from one disk to another:

Mirror the device to the new driver, then unmirror it using the Side = PRIMARY option. You should investigate the sp_movedevice stored procedure if you need to do something like this. sp_movedevice can be found by searching the SQL Server Books Online.


To resume the mirror, run the DISK REMIRROR command.

DISK REMIRROR
    NAME = `logical_name'

NAME is the name of the device. Because you didn't remove the information in the mirrorname column of the sysdevices system table, you don't need to provide additional information here. This reestablishes the mirror file.

As you might expect, SQL Enterprise Manager supports SQL Server mirroring. If it's not already started, open Enterprise Manager and connect to your server. Expand the Database Devices folder, right-click on a device, and select Mirroring from the pop-up menu to access the Mirror Database Device dialog box. Use the master device for this example (see Figure 8.1).

Figure 8.1. The Mirror Database Device dialog box.

By default, Enterprise Manager does something strange--it places your mirrored device in the same drive and directory as the original device. You most likely want to change the drive to another hard drive on your computer. It's recommended you keep the same directory structure, however, so that it's clear why the file is there. Also notice that the default filename extension is .mir. It's entirely up to you whether you maintain this convention, but it's easiest if you take the defaults. Click the Mirror Now button. If you get an error here, it's probably because you ran the previous Transact-SQL examples. Delete the physical file in that directory and then try again. It takes a few seconds, or several minutes, depending on the size of the device. When complete, dismiss the Success message box.

Now, click Mirroring again, and you see the Unmirror Device dialog box (see Figure 8.2).

Figure 8.2. The Unmirror Device dialog box.

Here you see all the options that were available with DISK UNMIRROR, except in English. To break the mirror we set up just before, select the Turn off Mirroring - Remove Mirror Device option, and click Unmirror. You should immediately receive a success message. Click OK, then close the Edit Database Device dialog box. The physical file is not deleted.

Nothing about any of these fault-tolerant features should prevent you from performing backups. RAID and SQL Server mirroring can help prevent data loss and keep your server running, but there are other reasons you might want to have backups.

How Backup Works

SQL Server backs up data dynamically. This means that you do not have to stop using a database to back it up. As always, it's a good idea to run your backups at a time of relative inactivity, as they do take some resources. You look at three kinds of backups here--database backups, transaction log backups, and single-table backups.


TIP: For historical reasons, these are also often referred to as dumps.

A database backup backs up an entire database, including all tables, indexes, system tables, and all other objects (which are contained in those system tables). A database backup does not back up empty pages, nor does it remove any transaction log pages from a database.


NOTE: Transactions are discussed in depth on Day 16, "Programming SQL Server." Today, however, you need at least a rudimentary understanding of what a transaction is. A transaction is a logical set of operations which either all complete successfully or all fail. Each data modification statement you run against a database is a transaction, and is recorded in the transaction log (the syslogs system table) in each database. The information recorded in the transaction log can be used to recover changes to your database to a point in time other than when you have taken a full database backup. Other tasks, such as space allocation, are also written to the transaction log. Over time, the transaction log fills up if you do not back it up or erase it. This is accomplished by backing up transactions with the DUMP TRANSACTION statement.

Transaction log backups back up the pages used by the transaction log for a database. A log backup also removes committed transactions from the transaction log. This prevents your log from filling up, and makes it critical that you occasionally back up your transaction logs (or turn on the Truncate Log on Checkpoint option for a database, which is not recommended for a production database). You can't back up the transaction log separately from a database unless the log is on a device separate from the database. In addition, you cannot back up the log until a full database backup has occurred after the Truncate Log on Checkpoint option has been set.

Single-table backups back up the data in a single table. They do not back up the indexes associated with that table.

Database, transaction log, and single-table backups are stored on backup devices. These devices are also known as dump devices. A backup device can be a physical disk file, a tape, a remote physical disk file, a named pipe, or a NULL device, known as the diskdump device.

A database's SA or DBO can back up and restore a database and a transaction log. As you saw on Day 7, "User Permissions," you can grant others the permissions to back up a database and a transaction log.

Dynamic Backups

Dynamic backups can be taken because when you begin a backup, SQL Server snapshots a database so that it can get a logically consistent view of your data. It then begins to back up the database. If another process should need to modify a page of data after the backup has begun (and before the backup is complete), SQL Server backs the page up before allowing the other process to modify the data page.

Alternate Backup Strategies

Some administrators believe that an alternative way to back up SQL Server is to stop the MSSQLServer service and then back up all files associated with SQL Server (in particular, the files from your MSSQL\DATA directory). However, this is not as fully functional as it sounds; it might not allow you to recover databases individually and won't provide the ability to perform a point-in-time recovery. Therefore, it is strongly recommended that you use SQL Server backups, and not back up the files directly. You should also note that "normal" Windows NT backups do not back up the device files for SQL Server while it is running, because the files are "open" when SQL Server is running.

Backup Considerations

There are many things to consider when performing backups. How often should you back up your databases? Which databases should you back up? To where should you back them up? How long should your backups be kept? Who is responsible for the backups, and how will you verify that they are good backups? Now examine these questions one at a time, starting with the easiest.

Who Is Responsible for the Backups?

When it comes to backups, the saying "if it's not someone's problem, it won't get done" is especially true. Backups are boring--when it comes to glamour, this isn't it. It takes a lot of work to set up backups properly and to test them on a regular basis. Backup responsibility may very well fall either on the database administrator or on the system administrator. It is also entirely possible that the network administrator will want to help perform backups. Ultimately, however, the important thing is that they be someone's responsibility.

How Will You Verify that the Backups Are Good?

You may be able to back up a database even if it becomes corrupt. There will most likely be no particular message about the corruption. There are some checks you can run before the backup begins (you see those shortly), but if you don't run the consistency checks or don't check the results first, you cannot tell that your backups are good until you restore from the backup. Therefore, it's a really good idea to periodically test your backups by restoring one to a test server on an occasional basis. That assures that your backups are good, and also helps you periodically test your restore process.

Which Databases Should You Back Up?

This may sound obvious, but you should back up all your user-defined databases. If it was important enough to create the database and put it on a server, it's important enough to back up. What about system databases? You should back up master and msdb. Having a backup of the model database may be useful, but tempdb doesn't need to be backed up.

To Where Should You Back Them Up?

As mentioned previously, you can back up your databases to disk, tape, a network drive, floppies, or a named pipe. Unless you own stock in a floppy disk producer, that's probably not going to be your preferred backup media. Tape is most people's preferred media, but has some significant limitations, not the least of which is speed. Backups to disk are usually the fastest. An alternative is to back up your database to another server's disk over the network. You should back up your disk backups to tape, whether those disk backups are to a local disk or to another server's disk. A typical scenario might be to back up to a network server over a fast network connection (100MB EtherNet, ATM, and the like), and then use Windows NT's built-in backup program to back up the backup devices. An alternative to all of these is to purchase a third-party backup utility. Most of the major brands include an add-in to back up SQL Server databases.

How Often Should You Back Up Your Databases?

The answer to this question depends on the size of your database. For databases smaller than a few gigabytes, the answer is usually a full database backup daily, with transaction log backups at some point during the day. At a minimum, you should back up your databases weekly, with at least daily transaction log backups. There are special considerations for the system databases, and you will examine them shortly. The differentiation regarding database size is made here because as the databases get significantly larger, you may not have enough time to fully back it up every day.

How Long Should Your Backups Be Kept?

Some backups may not be needed as soon as the next one is taken. It's usually a good idea, however, to have a few around in case one of the previous backups is found to be corrupt. Many sites choose to maintain the last two weeks' worth of backups as an arbitrary retention period. There may also be legal requirements for maintaining data, such as tax data needing several years of backups.

How Long Will It Take to Recover a Backup?

There's nothing worse than having a server crash--with the possible exception of having your boss approach you and ask, "So, how long will it take to recover the server?" and you having absolutely no idea. Therefore, regular testing of recovery of backups helps you avoid embarrassment in a difficult situation and help you better understand your recovery process.

Do You Have a Disaster Recovery Plan?

One of the best things ever done in the mainframe world involved having a disaster recovery plan and occasionally running disaster "drills." If you'd like to test a backup plan and verify that everything you need is being backed up, run one one such drill. It's amazing how many things you forget. Interesting problems may arise: Does your backup site have the same brand/model tape drive available? Did you remember to get the software you need in your backups?

Miscellaneous Tape Considerations

If you choose to back up to tapes, you should be aware of the following: SQL Server writes to tape using non-ANSI tape headers. This means that SQL Server can't share tapes with any other backup program, including Windows NT backups.

SQL Server uses the console program to notify an operator when a tape needs to be changed (if the program is running).

As you might expect, you can store more than one backup on a single tape. Make sure to avoid taking advantage of any particular tape drive's features, such as hardware compression, without first assuring that the same model is available when you need to recover using the tape. This is particularly true if you plan on keeping the tapes around for a long time. Are you sure that you'll have that brand of tape drive available in five years?

Backing Up User Databases

At a minimum, backing up regularly means weekly for full database backups and daily transaction log backups. Daily database backups are preferred, with periodic transaction log backups. Remember that a full database backup does not empty transactions from the database's transaction log.

There are some reasons not to back up a database on a regular basis:

However, you should focus on when you need to back up your databases. If you have a large database (bigger than a few hundred megabytes), you may want to back it up immediately after you create it. It's entirely possible that it's faster to re-create the database with the For Load option mentioned on Day 3, "SQL Server Tools and Utilities," and then restore the database backup.

There is one other time (beyond the obvious scheduled backups) that you should back up your database--after a non-logged operation. Non-logged operation is a bit of a misnomer. It's extremely unlikely that an operation will be truly non-logged. When you've enabled the Select Into/Bulkcopy Database option, operations such as the Select Into statement and a fast bulk copy are allowed. (Those subjects are discussed further in Day 11, "Data Retrieval," and Day 9, "Importing and Exporting Data," respectively.) You can also use certain statements with text or image data types that are not logged. Each of these operations runs faster than other operations of a similar nature by bypassing the transaction log. It was previously stated that normal changes to a database are first written to a transaction log and then to the table or index as appropriate. That logging does not occur with these operations. This means that the database is at least partially unrecoverable. The allocation of space is always recorded in the transaction log, even during an operation that's not logged.

Because the transaction log is no longer useful for recovery, a full database backup is the only way to guarantee recoverability of your database. After you've completed the database backup, you should clear out the transaction log with the DUMP TRANSACTION statement, using either the NO_LOG or TRUNCATE_ONLY option (discussed later in this chapter). In short, these options erase the transaction log without actually backing it up.

You may want to perform backups even in the cases when backups are unnecessary. Sometimes building a test database with a good set of sample data can be very useful to have around. You could back up this "pure" environment and then restore it as needed during a development process. Even if a database can be restored quickly from an alternate source, recovering from a SQL Server database backup may be faster than recovering from that alternate source and reloading all the data.

Backing Up System Databases

Next you examine the system databases, and how often you should back them up.

The master Database

The master database is a special kind of database. It resides exclusively on a single device, and the database and transaction log are stored together. You cannot change this configuration. Therefore, you cannot perform transaction log backups of the master database. Only full database backups can be performed. Because the transaction log can't be backed up anyway you may want to enable the database option to Truncate Log on Checkpoint.

Microsoft recommends that you back up the master database each time you modify it. As a practical matter, however, this is not really possible in most environments. Consider the items found in the master database--database devices, databases, logins, and more. Logins, for instance, include passwords. It's not always practical to back up the master database each time someone changes his or her password. As a practical matter, you should back up the master database daily. Any passwords that were changed after the last backup you've recovered will not be set properly.

It's also critical to back up the master database after any significant changes are made to your server, such as configuration changes, creating, changing, or dropping databases or devices, adding remote servers, enabling replication, or enabling/changing SQL Server mirroring.

The msdb Database

The msdb database contains the support information for the SQLExecutive service, as was discussed on Day 2, "Installing SQL Server 6.5." It also contains support for the SQL Server Web Assistant, which is discussed on Day 21, "SQL Server and the World Wide Web." If you take advantage of the SQLExecutive service, then you need to take regular backups of this database. Because the transaction log is on a separate device (the msdblog device), you can back up the log separately from your database. As a rule of thumb, msdb should be backed up at least weekly, with log backups daily. Just like user databases, more frequent backups may be justified. You may want to synchronize your backups with the master database--if you need to rebuild the master database, your msdb database will be reinitialized.

The model Database

The model database probably won't change often. However, because paranoia in an administrator can be a very good thing, it's good to have a backup. If you should have to rebuild your master database, the model database should also be reset.

The tempdb Database

The tempdb database is reset after each restart of the MSSQLServer service. Because of that, there is no need to back up the tempdb database.

The distribution Database

If you're using replication, the distribution database stores replicated transactions. You should treat the distribution database like any important user database--daily backups and occasional transaction log backups are recommended.

Implementing Backups

Now that you have gone over much of the theory and ideas of database backups, you can focus on the physical implementation of backups.

Creating a Backup Device

The first step in preparing to back up your databases is to create a backup device. There are three such devices on your system when you install SQL Server.

Diskettedumpa
This backup device points to a:sqltable.dat. It backs up to your primary floppy disk
Diskettedumpb
This backup device points to b:sqltable.dat. It backs up to a floppy disk.
DiskDump
This backup device points to nul. It is a "bit bucket," which doesn't store anything backed up to it.

You can view these devices with the sp_helpdevice system stored procedure. You can also view them with SQL Enterprise Manager--except that it hides the diskette backup devices. Remember that backup devices used to be called dump devices and you see that legacy throughout the Books Online and the system as a whole.

You need to create your own backup devices. If you want to add a tape device, you would typically create only a single backup device pointing to the tape drive. You might still create more than one logical device, each one pointing back to the same tape drive. For disk (or network) backup devices, you typically create one for each database. You create the backup device with the sp_addumpdevice system stored procedure.

sp_addumpdevice {`disk' | `diskette' | `tape'}, `logical_name',
`physical_name' [, {{cntrltype [, noskip | skip [, media_capacity]]} |
{@devstatus = {noskip | skip}}}]

The first parameter specifies whether the backup device you want to reference is a disk device, a diskette device, or a tape drive.

The logical name is the name you use internally when referring to this device. It must be a valid SQL Server identifier name.

The physical name is the backup device's path and filename. If the backup device is a disk, this is either a local hard drive and path or a UNC (Universal Naming Convention) path to a file. For a tape, it is the path to the tape device, such as \\.\TAPE0, to reference the first tape drive on your server. Floppy disk names are obvious.

All other parameters from this point are optional.

cntrltype is no longer used with backup devices, but is accepted if you have old scripts that use this command.

skip or noskip determines whether ANSI tape labels should be read when you are using tape. skip specifies that they are ignored, and noskip means the tape labels will be read.

media capacity is another outdated parameter that is no longer used, but won't be treated as a syntax error if received.

@devstatus = skip/noskip specifies, again, whether you should read or ignore the ANSI tape labels.

A typical dump device is added for the master database. For example, the following adds a new disk backup device called master_backup, in the appropriate physical location:


sp_addumpdevice `disk', 'master_backup', 'c:\mssql\backup\master_backup.dat'

To drop a backup device, run the sp_dropdevice system stored procedure. This stored procedure is documented on Day 3.

SQL Enterprise Manager can also be used to create or remove a backup device. In Enterprise Manager's server manager window, connect to your server and expand the Backup Devices folder. You should see only one backup device here--the DiskDump device. This is the null device that was mentioned earlier.

To create a new backup device, right-click on the Backup Devices folder and select New Backup Device from the pop-up menu. You are presented with the New Backup Device dialog box. Just as you did before, you can create a backup device here to hold the master database backups. In the example it's called master_backup. Enterprise Manager enters the Location for you and defaults to the same name as the device's logical name.

Figure 8.3. The New Backup Device dialog box.

After you've filled out the dialog, click on the Create button to finish creating the device. If you change the backup device type to tape, the Skip Headers checkbox becomes available for you. If you wanted to change device to point to a network location, enter the UNC path to that remote server in the Location box. Never use mapped drive letters. SQL Server doesn't understand them.

To remove a backup device with Enterprise Manager, simply right-click on the device in the Backup Devices folder, and select Delete from the pop-up menu.

Verifying Database Consistency

After you have a backup device available, you are almost ready to begin backing up your databases. There's an important step that needs to be accomplished before that can be done, however. Again, a corrupt database backup is usually worthless. Therefore, you need to verify that your backup is good. Microsoft provides the capability to verify your database's integrity, but does not provide a utility to verify the backup itself. Third-party solutions providers have add-ins that verify backups.

Now focus on the consistency-checking capabilities that you have within your SQL Server database. If your database is okay before the backup, it's very likely that your backup itself is also be okay. To verify the consistency of your database, use extensions of the DBCC (DataBase Consistency Checker) utility.

DBCC {
CHECKALLOC [(database_name [, NOINDEX])] |
    CHECKCATALOG [(database_name)] |
    CHECKTABLE (table_name [, NOINDEX | index_id]) |
    CHECKDB [(database_name [, NOINDEX])] |
    CHECKIDENT [(table_name)] |
DBREINDEX ([`database.owner.table_name' [, index_name
[, fillfactor [, {SORTED_DATA | SORTED_DATA_REORG}]]]]) |
DBREPAIR (database_name, DROPDB [, NOINIT]) |
dllname (FREE) |
INPUTBUFFER (spid) |
MEMUSAGE |
NEWALLOC [(database_name [, NOINDEX])] |
OPENTRAN ({database_name} | {database_id})
 [WITH TABLERESULTS] |
OUTPUTBUFFER (spid) |
PERFMON |
PINTABLE (database_id, table_id) |
PROCCACHE |
ROWLOCK (dbid, tableid, set) |
    SHOW_STATISTICS (table_name, index_name) |
    SHOWCONTIG (table_id, [index_id]) |
    SHRINKDB (database_name [, new_size [, `MASTEROVERRIDE']]) |
    SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS
[, CLEAR]} | {THREADS} | {LOGSPACE}) |
    TEXTALL [({database_name | database_id}[, FULL | FAST])] |
    TEXTALLOC [({table_name | table_id}[, FULL | FAST])] |
    TRACEOFF (trace#) |
    TRACEON (trace#) |
    TRACESTATUS (trace# [, trace#...]) |
    UNPINTABLE (database_id, table_id) |
    UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])
}
WITH COUNT ROWS |
    USEROPTIONS}
[WITH NO_INFOMSGS]

Wow! That's a very flexible command, and believe it or not, there are many undocumented options that can be used with this command.

The commands you are concerned with for consistency checking follow:

CHECKCATALOG [(database_name)]

CHECKCATALOG checks on the referential integrity of the system tables in a database. If you don't supply the database_name parameter, the current database context is used and its system catalog is checked. This checks that columns in syscolumns point back to valid tables in sysobjects, and so on.

CHECKTABLE (table_name [, NOINDEX | index_id])

CHECKTABLE requires a table_name as a parameter. CHECKTABLE then verifies that the indexes on that table are properly linked to the data pages, that the indexes are sorted properly, and verifies the internals of each data page, including internal offsets to data rows within a page. CHECKTABLE also corrects information in the sysindexes table about each index (or the table).

If you specify the NOINDEX parameter, only your data pages are checked for integrity. If you specify an index_id, only that particular index is checked. You can get the index_id from the indid column of sysindexes for your table. If neither parameter is used, all indexes are checked.

If you run CHECKTABLE against the syslogs table (your transaction log), it also reports the amount of space used on your transaction log. If the transaction log is on its own device, free space and used space are also reported.

CHECKDB [(database_name [, NOINDEX])]

CHECKDB does the same thing as CHECKTABLE, except that it works on all tables in your database instead of working on a single table at a time. If you don't specify the database_name parameter, it runs in the context of the local database. The NOINDEX option is the same as before, and only actual tables are checked for integrity in the database.

CHECKIDENT [(table_name)]

CHECKINDENT verifies that the proper values are being used for the identity column in a table if it exists. The syntax indicates that the table_name parameter is optional, but testing shows that it is required. If the identity property is found to be invalid, it is reset to the maximum value already used in the table (plus or minus the increment). The identity property is discussed on Day 14, "Data Integrity."

NEWALLOC [(database_name [, NOINDEX])]

NEWALLOC performs the same function as DBCC CHECKALLOC, but won't stop if it finds any errors. Instead, it continues to process the database. It also gets fewer errors due to database activity than DBCC CHECKALLOC. NEWALLOC sometimes encounters errors if the database is busy and it tries to verify the integrity of an extent as it's being allocated. These errors can be safely ignored, but it's annoying to have to rerun the NEWALLOC to verify that the errors were transient in nature. Microsoft continues to enhance NEWALLOC so that it reports fewer of these "false" errors.

As with the other commands here, if you do not supply the database_name parameter, the current database context is used. The NEWALLOC command is quite "chatty," resulting in a significant amount of information being returned from the command. This includes the number of used pages and used extents for the table and each index on each table.

TEXTALL [({database_name | database_id}[, FULL | FAST])]

TEXTALL runs DBCC TEXTALLOC on each table that has a text or image column in the selected database. Note that you can use either the database name, or the database ID number (from the sysdatabases system table in the master database). The FULL option (the default) checks the integrity of the page links for text and image columns, and then outputs a report. The FAST option does the same work but does not print a report.

TEXTALLOC [({table_name | table_id}[, FULL | FAST])]

TEXTALLOC verifies the page chain, the links between 2k pages, that make up the text and image data in your tables. The FULL and FAST options are the same as with TEXTALL. Here you can specify the table name, or the table ID (which can be found in the sysobjects table in each database). Again, it appears that the table_name is optional, but testing shows that it is required.

WITH NO_INFOMSGS

This option does something extremely useful--it eliminates most of the output from your DBCC commands and only returns errors. After you know what to expect from the output of these commands, it can be useful to turn off the clatter of the informational messages and only get error messages.

As a rule of thumb, it is recommended that you run DBCC CHECKDB, NEWALLOC, CHECKCATALOG, and TEXTALL (if you use text or image datatypes) on your database before you begin your backups. If these reports come out without errors, your database is most likely fine and safe to back up. Troubleshooting errors from these commands is outside the scope of this book, but additional information can be found in the SQL Server Books Online or in Microsoft's knowledge base (http://www.microsoft.com/kb).

Backing Up a Database

After you have your backup device(s) and have verified that your databases are not corrupt, it's time to back up each database. The first step you examine here is determining which backup device you want to use, and the way you will use those backup devices.

Backup Device Options

You can back up to a single backup device or multiple devices, or put multiple backups on a single device. Take a look at each of the following options.

Single-Device Backups

A single-device backup is the default, and is most often recommended way to perform a backup. If each database has a corresponding backup device, you can then perform a one-to-one backup strategy. This is recommended if you are backing up to disk backup devices. If you should lose a file for some reason, you would only lose a single backup of a single database, instead of losing multiple backups contained in a single file.

Parallel-Striped Backups

A parallel-striped backup allows you to back up a single database to multiple backup devices. You simply enumerate more than one backup device in the backup command, dump database. SQL Server initiates a thread for each device you choose for use in your parallel-striped backup, with up to 32 devices (and hence, threads) possible. The actual number of threads may be restricted by the backup threads configuration parameter. This is a system-wide configuration parameter, set with the sp_configure system stored procedure. The default value of this setting is 5, which means that up to 5 threads can be used for backup and restore operations. You can set this value as high as 32. After you've configured this parameter, you need to stop and restart the MSSQLServer service.

Tape backups are the big advantage with parallel striped backups. You can attach multiple tape drives to a single server and back up a database to multiple tapes at the same time. If you had a single database that took three hours to back up, and purchased two additional tape drives (for a total of three) you could quite possibly finish that database's backup in one hour. The news gets better--you don't need to have the same number of tape drives available when it comes to restoring from the backup.


WARNING: This is particularly important for disaster recovery. There's no guarantee you'll get the same number of tape drives in your stand by server. You still need all the tapes to recover your database.

Multiple Backups on a Single Device

You could also put multiple backups on a single backup device. If you think about it, this is the default configuration you'd want for a tape backup. It's likely that you'll want to put as many database backups on a single tape as possible. You can also do that with disk devices (and, at least theoretically, diskettes). It's not recommended for disk devices. If the single file becomes corrupt, all database backups on that device are lost.

The dump_database Command

Backing up the database is the next logical step. That's done with the dump database command.

DUMP DATABASE {dbname | @dbname_var}
        TO dump_device [, dump_device2 [..., dump_device32]]
    [WITH options
        [[,] STATS [ = percentage]]]

Where

dump_device =
{dump_device_name | @dump_device_namevar}
| {DISK | TAPE | FLOPPY | PIPE} =
    {`temp_dump_device' | @temp_dump_device_var}}
[VOLUME = {volid | @volid_var}]
options =
[[,] {UNLOAD | NOUNLOAD}]
[[,] {INIT | NOINIT}]
[[,] {SKIP | NOSKIP}]
[[,] {{EXPIREDATE = {date | @date_var}}
    | {RETAINDAYS = {days | @days_var}}]

Unlike most commands in SQL Server, the dump database command accepts either the database name (dbname) as a character string or as a variable.

The dump_device parameter (up to 32) is the backup device used most often. As you can see in the syntax, however, there is much more to it. You can use a variable name to enter the backup device name as well. You can also use "temporary" devices by pointing to a file, a network file, or a tape drive dynamically. This does not create a backup device--it's for one-time use.

The volume parameter specifies the ANSI volume label if you prefer. SQL Server uses a default label if you don't specify.

The WITH clause has a number of parameters. The first parameter in the previous code, UNLOAD versus NOUNLOAD, determines whether a tape used for a backup will be rewound and then ejected after the backup completes.

The INIT command is perhaps the most important option used in the WITH clause. When you specify backing up a database WITH INIT, you are requesting that the backup device be initialized. Because backup devices are not actually created physically when sp_addumpdevice is run, the INIT option must be used the first time you write to a backup device. If the device is a tape, this reinitializes the tape (and erases any data that was previously on the tape). This is also true of disk devices--they are completely erased, and any backups that were on the device before this backup command are permanently deleted. The NOINIT option specifies that this backup should be appended to any backups already within the backup device(s) you specify. You cannot back up to a device with the NOINIT option if a device has not been initialized.

The SKIP and NOSKIP options here are the same as with the sp_addumpdevice system stored procedure, but override options set with that stored procedure.

The EXPIREDATE specifies when the disk or tape device expires and can be overwritten. This option is available only when WITH INIT is specified.

RETAINDAYS specifies how many days to keep the device before it expires and can be overwritten.

The STATS parameter specifies that a notification should be returned to the client program after a certain percentage of pages haa been backed up. The default is to send back a notification as each 10 percent of the backup is complete.

A Backup Command Example

For example, to back up the master database to the master_backup device you created earlier, run the Transact-SQL command.

DUMP DATABASE master to master_backup WITH INIT

This makes a full backup of the master database to the master_backup device, initializing the device in the process.

Backing Up with SQL Enterprise Manager

SQL Enterprise manager has an excellent interface that supports the backup command. To access the Enterprise Manager dialog box, select Tools | Database Backup/Restore from the menu, or right-click on a database in the Databases folder and select Backup/Restore from the pop-up menu that appears. You see the Database Backup/Restore dialog shown in Figure 8.4.

Figure 8.4. The Database Backup/Restore dialog box.

In the Database Backup frame, select the database you want to back up. The selected database here is the master database. The entire database is selected by default. In the Options frame, you need to check the Initialize Device checkbox if you have not previously backed up a database into the backup device. When checked, the expiration options become available. In the Backup Devices window, highlight the backup device you want to use for this backup. When highlighted, the Backup Now and Schedule buttons become available. Click on the Backup Now button to begin the backup. You are prompted to provide a Backup Volume Label (although a default is provided). Click OK to continue with the backup. You see a Backup Progress window, with a progress bar showing how far the backup has gotten. When complete, you should receive a success message like that shown in Figure 8.5. The Schedule option is discussed on Day 18, "SQL Server Automation."

Figure 8.5. The Backup Progress Success dialog box.

Dismiss this message box and close the Database Backup/Restore dialog box. Expand your Backup Devices folder and then expand your master_backup backup device. You should see every backup that is stored on that device represented here: an icon of a disk for a database backup or a page for a transaction log backup. You also see a page count on the device.

Reenter the Database Backup/Restore dialog box and create a backup device for the msdb database, called MSDB_BACKUP, by clicking on the New button in the Backup Devices frame. You are presented with the New Backup Device dialog box you saw before. Complete the dialog box and create the new backup device. Highlight the master_backup device and click on the Info button. The Backup Device Information dialog box makes its appearance (see Figure 8.6).

Figure 8.6. The Backup Device Information window.

You see information about all the backups kept on each device. Dismiss this window and back up the msdb database. Select an Entire Database backup, initialize the device, and select the new msdb_backup device you just created. Click Backup Now to complete the backup. When done, close the Backup/Restore dialog box.

Backing Up Transaction Logs

Backing up the transaction log is your next step. If the transaction log is not on a separate device, you cannot perform a proper transaction log backup. Backing up the transaction log is accomplished with the DUMP TRANSACTION statement. It removes all committed transactions older than the oldest currently running transaction from the log, after it has copied them to your backup device.

DUMP TRANSACTION {dbname | @dbname_var}         [TO dump_device [, dump_device2 [..., dump_device32]]]     [WITH {TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE}
{options}]

The DUMP TRANSACTION statement is virtually identical to the DUMP DATABASE command, so identical syntax options aren't restated. However, there are three options that are very important to understand, and that's where you should focus your attention.

The TRUNCATE_ONLY option is used when you want to remove committed transactions from the log but do not actually need to back up the entries. For instance, after backing up a database that has had "non-logged" operations run against it, the transaction log is no longer useful. You may want to issue a DUMP TRANSACTION with TRUNCATE_ONLY followed by a full database backup. The fact that the log is truncated is, in itself, a logged operation. You should also note that this option does not actually perform a backup, hence referring to it as a dump device is optional, and if specified, it is ignored.

The NO_LOG option is used under circumstances similar to the TRUNCATE_ONLY's option. However, it does not record the fact that the transaction log was truncated. If your transaction log fills up completely, you cannot back up the transaction log. Because a transaction log backup is itself a logged operation, you can't back it up as long as the log is full. Therefore, Microsoft made a special option that truncates the log in this special circumstance. It works just like a TRUNCATE_ONLY when the log is not full, as well as when it is full.

NO_TRUNCATE makes a transaction log backup (just like a dump transaction without any special options). However, it does something that an ordinary backup can't--it can back up the transaction log even if a database is not available. For instance, assume that you have a database for which the data is on one physical disk, and the transaction log is on a separate disk. If the database device is lost for some reason, you can run the DUMP TRANSACTION statement with NO_TRUNCATE to capture all transactions that have occurred since the last transaction log backup. This allows you to recover your database right up to the point of disk failure.

How often should you back up your transaction log? A typical scenario might look something like this (for a database used primarily during business hours):

6:00 a.m. Daily full database backup with INIT
6:05 a.m. Back up the transaction log with INIT
10:00 a.m. Back up the transaction log again
2:00 p.m. Back up the transaction log again
6:00 p.m. Back up the transaction log one more time
8:00 p.m. Back up database and transaction log devices to tape with Windows NT Backup

This allows you to recover your database and not lose transactions (as long as you don't lose the backup device).

Switch to SQL Enterprise Manager and reenter the Database Backup/Restore dialog box. Click on the New button in the Backup Devices window and create a device called msdb_log_backup. Click on the Transaction Log option in the Database Backup frame. Check the Initialize Device option, highlight the msdb_log_backup, and click on Backup Now to back up the transaction log (see Figure 8.7).

Figure 8.7. Transaction Log Backup dialog box.

If you get an error such as that shown in Figure 8.8, it usually indicates that the Truncate Log on Checkpoint option is enabled.

Figure 8.8. Microsoft SQL-DMO Error dialog box.

You can't back up the transaction log of a database with either the Truncate Log on Checkpoint or the Select Into/Bulkcopy option set. If you get this error, turn off the appropriate database option and reissue the transaction log backup.

You get another error. This time it says that a database backup has not been run since you turned off the offending database option. Therefore, take a full database backup again, and then back up the transaction log. Click on the Info button with the msdb_log_backup device highlighted, and view the transaction log backup. You see the Backup Device Information dialog box again. There is one important difference between log backups and database backups that becomes readily apparent when you examine the backup. The Backup Start field doesn't contain the backup's start time. It contains the date and time of the earliest transaction within the transaction log backup. The Backup Finish field contains the actual time that the backup was completed.

Single-Table Backup

The last backup option you examine is the single-table backup, which is new to SQL Server 6.5.

DUMP TABLE [[database.]owner.]table_name
    TO dump_device [, dump_device2 [..., dump_device32]]
[WITH options]

The syntax is very similar to the dump commands. The primary difference is that you specify an individual table name for this command. There are a couple of other differences. First, you only need select permissions to back up a table. Second, a table backup is subject to being blocked by open transactions. Unlike a full database backup, a table backup does not back up any of the transaction log, and therefore must get a valid snapshot of a table in order to back it up.

You should be very cautious taking single-table backups in your database. It's rare that you have tables that can be backed up (and then, presumably restored) that don't have any data related to other tables that you might want to restore. Therefore, you typically back up tables in a referentially complete set.

Exec sp_addumpdevice `disk', `table_backup',
 `c:\mssql\backup\table_backup.dat'
go
DUMP TABLE pubs..authors TO table_backup WITH INIT

This creates a new backup device and backs up the authors table in the pubs database. This is fundamentally the same as the other backups you've run in Enterprise Manager, so it is not explicitly examined, except to show the Backup Device Information dialog for a single table backup. Notice the table icon in the backup window--this shows you that the type of backup is a single table (see Figure 8.9).

Figure 8.9. The Backup Device Information dialog box (single table).

Restoring Databases

Backups are good, but not terribly useful in and of themselves. They're really handy, however, when it comes to having problems with your server. If a disk quits functioning, or a database device file becomes corrupt, you need to restore any databases that were affected by the loss of the device(s).

You can also use the restore process to recover a backup from a previous version of SQL Server. This is exceptionally useful when you have need to examine data from a backup, without the need to reinstall an old version of SQL Server (assuming you even have the old software around). An alternate use of the capability to recover backups from previous releases of SQL Server is to upgrade databases to SQL Server 6.5 one at a time, rather than upgrading an entire server. This can be very useful for testing your databases without committing to upgrading an entire server.

Another reason to restore a database backup is to restore a database to a logically consistent point in time. For example, say your boss deleted all the day's sales at 5 p.m. You could recover from the previous full database backup and apply all your transaction log backups until the point in time immediately before your boss ran the delete command, thus recovering your data and losing a minimal amount of work.

Before you can examine recovery, you need to understand that SQL Server has two different types of recovery--automatic and manual.

Automatic Recovery

Automatic recovery is the process SQL Server goes through each time the MSSQLServer service is restarted. You cannot disable it and don't need to do anything special to make it occur (hence the name automatic).

Each time SQL Server starts, it goes through a particular set of steps that roll forward any committed transactions found in the transaction log of a database that have occurred since the last checkpoint in each database. Rolling forward transactions means that any committed transactions for a database are reapplied to that database. SQL Server then rolls back any transactions in the transaction log that have not been committed. Rolling back means that any transactions that were partially completed, but not committed, are removed from the database. When completed, each database is left in a logically consistent form, and a checkpoint is issued. You examine transactions further on Day 16.

The automatic recovery process guarantees that no matter how or why SQL Server was stopped, it will start in a logically consistent state. Even if your server should crash, you can recover cleanly. All committed transactions have their log pages written to disk immediately. Therefore, any permanent changes to the database will have been successfully copied to disk, and are used during this process.

Automatic recovery processes each database in a particular order. It does this by first locating the master database device, finding its location in the Windows NT Registry.

The master database is loaded after the master device is located. You must load master first because the master database contains the references to the rest of the database devices in the sysdevices system table, and each database in the sysdatabases system table. After it has recovered the master database, SQL Server can begin the process of locating and opening the rest of the database devices, and then begin recovering their databases. Before it does that, it recovers the model database.

Next comes the tempdb database. You need the tempdb database to perform a wide variety of queries or stored procedures in SQL Server. The msdb database is then restored, followed by the distribution database (if it exists), then the pubs database, and finally any user databases are recovered.

Why does SQL Server have to recover model before tempdb? Because tempdb is reinitialized each time the MSSQLServer service is restarted. It is created just as any other database is on SQL Server--by copying the model database and then expanding the database to the appropriate size.

You can examine this entire process by looking at your error log. Start Enterprise Manager if it's not already open, and select Server | Error Log after you've connected to your server. You see the Server Error Log view, which is shown in Figure 8.10.

Start by finding the entry initializing virtual device 0, D:\MSSQL\DATA\MASTER.DAT. This indicates that the master device is being opened. This is followed by Opening Master Database, which indicates that the master database is being opened. This is followed shortly by the entry Recovering Database "master", and then there is usually an entry such as 1 transaction rolled forward.

Figure 8.10. The Server Error Log view in Enterprise Manager.

You see each device activated, then model is recovered, tempdb is cleared (re-created), msdb, pubs, and finally your user databases. You know that recovery was successful by the Recovery Complete entry in the error log.

The most important thing you can do to configure automatic recovery is to configure the Recovery Interval option. This option specifies the maximum amount of time that SQL Server takes to perform automatic recovery on each database. The default value is five minutes, and is probably a good default. This configuration parameter affects how often checkpoints occur. A checkpoint is the process that copies pages from memory onto disk. After a checkpoint is completed, transactions that are committed before the checkpoint no longer need to be rolled forward.

Manual Recovery

Manual recovery is the process of recovering a database or transaction log backup(s) from your backup devices. The details of the recovery process vary, depending upon the reasons for the recovery.

You can recover a database and then apply the transaction logs to fully restore a lost database, or recover to a point in time to undo a change to your database. A single-table restore is also possible, as well as a recovery of a full database backup without applying any transaction logs. There are some restrictions about restoring a database from a backup.

1. The database cannot be in use.

A database is in use if anyone has issued the USE command and pointed to the database, or if anyone is running any query against that database.

2. You must have the proper permissions.

Only database's DBO (or SA) can recover a database--the right to do so can't be given away.

3. You must restore the transaction logs in the sequence in which they were created.

Each transaction log has a sequence number associated with it. You must restore these transaction log backups in the proper order or the recovery fails.

Additionally, if the reason you're trying to recover is because you've lost a hard drive, you must first replace the disk that failed (including reestablishing mirroring or other RAID configurations), then drop any affected databases and devices. You then re-create the devices, then the databases, in the same order as they were originally created.



NOTE: When you create the databases, you may want to use the FOR LOAD option. This option creates the database much more quickly, but leaves it in a state such that the only action that can be performed on the database is a recovery of a backup. The database has the DBO Use Only option set after the recovery is complete, and you need to manually turn this option off.

Full Database Restore

You restore a database with the LOAD DATABASE command.

LOAD DATABASE {dbname | @dbname_var}
        FROM dump_device [, dump_device2 [..., dump_device32]]
    [WITH options
        [[,] STATS [ = percentage]]]
dump_device =
{ dump_device_name | @dump_device_namevar}
| {DISK | TAPE | FLOPPY | PIPE} =
    {`temp_dump_device' | @temp_dump_device_var}}
[VOLUME = {volid | @volid_var}]
options =
[[, ] {UNLOAD | NOUNLOAD}]
[[, ] {SKIP | NOSKIP}]
[[, ] FILE = fileno]
[[, ] STOPAT = {date_time | @date_time_var}]
[[, ] SOURCE = source_name]
[[, ] APPEND]
[[, ] STATS [= percentage]]

Because many of these options are the same as with the DUMP DATABASE statement, they are not reenumerated here.

There is one important option that was not on the dump statement--the FILE parameter. When you recover a database from a backup device, you must indicate which backups stored on the device you want recovered. There can be (as you've seen) many backups on a single device. You should specify which backup you want to load with this parameter.

The logical question that follows the LOAD statement is, "How do I figure out the proper file number to specify with my load statement?" There are several ways, but this chapter examines the easiest.

First, you can simply expand the device in the Backup Devices folder and view the backups on the device. The number in front of each backup is the fileno parameter you use in the LOAD statement (see Figure 8.11).

Figure 8.11. The expanded backup device view.

You could also enter the Database Backup/Restore dialog (by selecting Tools | Database Backup/Restore from the menu with your server highlighted), highlight the appropriate device you want examineed in the Backup Devices window, and then click on the Info button. You are presented with fileno as the output's first column on this screen. You already examined this screen earlier, in Figure 8.6.

The third way to view this information is to use the LOAD HEADERONLY command.

LOAD HEADERONLY
        FROM dump_device

For instance, if you ran the following command, you could examine the output and find the Sequence column. If you examine the output, you notice that it's the same information that's on the Backup Device Information dialog you examined earlier.

Load headeronly from master_backup

To recover a database backup, therefore, run the load headeronly command to examine the file numbers, and then run the load database command to recover the database from the appropriate backup.

Transaction Log Restoration

Run the LOAD TRANSACTION command to recover a transaction log.

LOAD TRANSACTION {dbname | @dbname_var}
        FROM dump_device [, dump_device2 [..., dump_device32]]
    [WITH options]
options =
[[, ] {UNLOAD | NOUNLOAD}]
[[, ] {SKIP | NOSKIP}]
[[, ] FILE = fileno]
[[, ] STOPAT = {date_time | @date_time_var}]
[[, ] SOURCE = source_name]
[[, ] APPEND]
[[, ] STATS [= percentage]]

Again, the options are similar to the LOAD DATABASE statement. The most important option here is the FILE parameter. You must restore the transaction logs in the proper sequence.

The STOPAT parameter specifies that you want to recover your transactions at a previous point in time. This option only applies to recovering a transaction log. The date and time you specify must be within the range of dates and times contained in the transaction log backup.

Single-Table Restore

The other option you have is to restore a single table. That is accomplished with the LOAD TABLE statement.

LOAD TABLE [[database.]owner.]table_name
    FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH options]
options =
[[, ] {UNLOAD | NOUNLOAD}]
[[, ] {SKIP | NOSKIP}]
[[, ] FILE = fileno]
[[, ] STOPAT = {date_time | @date_time_var}]
[[, ] SOURCE = source_name]
[[, ] APPEND]
[[, ] STATS [= percentage]]

Again, the options are similar. For the load table command, the SOURCE and APPEND options apply.

The SOURCE option specifies the name of the original table that was backed up, if you choose to recover the table into a new table.

The APPEND option specifies that the data from the backup will be added to the table, as opposed to a normal load, which replaces any data with the data from the backup device.

Loading a table places an exclusive lock on the table being loaded. This means that it is unavailable for use while the load is running, and the load can be prevented if someone is using the table when you try to perform the load table command.

You must have insert permissions on the table, and the database option select into/bulkcopy must be set. Therefore, your database is in the same condition that applies if a non-logged operation occurs. You must perform a full database backup after restoring a single table to make the database recoverable again.

As implied in the SOURCE option, the table names don't need to match between the backed up table and the table to which you restore. The structure of the tables, however, must be identical.

A load table can be run with either a full database backup or a single table backup. You should be extremely careful when restoring a single table from a backup, as it is not guaranteed to recover in a state consistent with the rest of the database.

The last statement in the syntax box deserves a little more explanation. A transaction was run (for instance, updating everyone's salary on the employee table and giving them a 10 percent raise), but was cancelled. A full database backup was run in the middle of the modification to the table. The full database backup would reflect the partial changes to the table, but would also have the transaction log reflecting that the changes were not yet permanent. When a full database restore is completed, the transaction log entries are used to roll back the changes that were not permanent. However, a single-table restore does not apply any transaction log entries, and some people would probably have the raise reflected in the restore.

SQL Enterprise Manager

One thing that's been missing is any mention of SQL Enterprise Manager. All of these operations can be performed from the Restore tab of the Database Backup/Restore dialog. You can see that tab in Figure 8.12.

Figure 8.12. The Restore tab.

Select the database you want to restore from the Database drop-down box. Notice that when you select a database, all the backups that have been performed on that database are reflected in the Backup History window. This list comes from the sysbackup history table in the msdb database. The proper backups are reflected here most of the time.

To restore from one of the displayed backups, simply click on the backup you want to restore from in the Backup History window.


WARNING: Just because a backup is listed here doesn't mean it's available. For instance, if you were to back up to the same disk device each night with init, and then back up the file to tape, every backup would point to the same physical location--file number 1 in the backup device. However, it would only contain the most recent backup. Therefore, a little caution is in order when using this dialog.

If you are going to restore the most immediate backup and then apply the appropriate transaction logs, the default options in this dialog work well for you. In this view, you can see that the default is to restore the full database backup, then apply the two transaction log backups taken since the database backup was performed (see Figure 8.13).

Figure 8.13. A properly selected Restore dialog.

To complete a single-table restore, click on the Single Table option button. You are presented with a list of the tables contained in the backup (see Figure 8.14). If you get an error, it is most likely because you need to enable the select into/bulkcopy database option to restore a single table. Only full database and table backups can be used for a table restore--transaction logs are not eligible.

Figure 8.14. A single-table restore.

If you want to restore the transaction log until a particular point in time, simply check the Until Time option and select the date and time as appropriate.

After you've selected your restore options, simply click on the Restore Now button. The restore proceeds, and you should get a success message when it's complete.

If you don't want to use the default listed, or want to confirm the contents of the device you want to recover from (rather than depending on the data kept about backup history), you can click on the From Device button to point back to a particular device (see Figure 8.15). Be careful to reset Destination Database, as it sometimes changes when you enter this dialog.

Figure 8.15. The Restore From Device On Server window.

Select the proper device from which you want to recover. The backup device is interrogated to verify the current contents of the device, and then displayed in the Backup Information window. Select the correct file, then click on the Restore Now button to complete the restoration of the database, transaction log, or single table. The Until Time option is also on this screen if you are pointing to a transaction log backup.

It is from this dialog that you can choose, by clicking on the Add File button, to point to a file dynamically rather than to a backup device. You can also add a new backup device here with the Add Device button.

Backup and Recovery Scenarios

You can examine the following scenarios to better understand backing up and restoring data.

Scheduled Backups

The first step in discussing backup scenarios is to discuss the scheduling options available to you. Scheduling of tasks is discussed fully on Day 18, but today you examine the minimum of what you need to know to schedule your database backups.

If you enter the Database Backup/Restore dialog on the Backup tab and configure a database backup, the Schedule button becomes available. It is shown in Figure 8.16.

Figure 8.16. A properly configured full backup.

Click the Schedule button to be presented with the Schedule Backup command. You may get an error message indicating that the backup device already contains a backup (see Figure 8.17).

Figure 8.17. The scheduled backup warning.

Simply click OK to acknowledge the message and continue until you are presented with the Schedule Backup dialog, which is shown in Figure 8.18.

Figure 8.18. The Schedule Backup dialog.

The dialog is filled in with the proper DUMP DATABASE command, and you can change it in the SQL Command window.You can pick the schedule you'd like for the backup below the SQL command. Immediate is the default, and is useful if you don't want to sit and watch a backup. The backup is launched immediately (assuming your SQLExecutive service is running) if you click OK.

The One Time option is fairly straightforward--simply pick the date and time that you want the backup to run. Recurring is the last option option. If you select it, the window below specifies the schedule. The default is to back up the database once a week, on Sunday, at midnight. If you click on the Change button, you are presented with the Task Schedule window (see Figure 8.19).

Figure 8.19. The Task Schedule window.

Here you can change the schedule to whatever you prefer. Scheduling options are discussed in detail on Day 18. After you have selected the schedule, click on OK to return to the Schedule Backup window. Click on the Options button at the bottom of the window to view the TSQL Task Options dialog. You can specify an email operator, as well as options to write to the Windows NT Event Log (see Figure 8.20).

Figure 8.20. The TSQL Task Options dialog.

You should check the On Success checkbox for the Event Log. If it's important enough to schedule as a backup, it's important enough to record its success or failure in your event log. Email operators are discussed with the rest of the scheduling options on Day 18. Click OK, then OK again to finish scheduling the backup.

After closing the Database Backup/Restore dialog, double-click on the SQL Executive icon (or the word SQL Executive) for your server. You are presented with the Manage Scheduled Tasks dialog. You see the new task, Backup - msdb, indicating that it was scheduled properly (see Figure 8.21).

Figure 8.21. The Manage Scheduled Tasks dialog.

You should set up scheduled tasks for all database backups and the transaction log backups. This should include the system databases, as well as your databases. You should plan these backups and think the recovery scenarios through before scheduling your backup tasks.

Recovery After Losing a Disk

Because the most common recovery scenario is to restore after a disk has been lost on the server, examine this scenario. You need to perform the following steps:

1. Verify that you have all the information you need to restore the devices and databases.

2. Capture the transaction log, if it's still available.

3. Drop the affected databases and devices.

4. Replace the failed disk.

5. Re-create, in the proper order, the devices and databases that were lost.

6. Restore the databases and transaction logs as appropriate.

Now explore each of these steps in detail.

Verify the Information

You should have the disk init statements, as well as the create database statements to re-create any devices and databases affected by the lost disk. Microsoft has written a stored procedure called sp_help_revdatabase, which creates scripts for the create database statements, as well as altering the databases properly. To get the disk inits, either have your original copies of the commands, or rebuild them from the sysdevices system table.Following, however, is a stored procedure written to create a script of all disk inits and sp_addumpdevice commands on your server.

use master
go
drop proc sp_help_diskinit
go
create proc sp_help_diskinit
/*********************************************************/
/**  Stored Procedure sp_help_diskinit                   */
/**  Written by Richard Waymire                          */
/**  Copyright Richard Waymire, October 21, 1997         */
/**  This procedure scripts the disk inits and backup    */
/**  devices.                                            */
/*********************************************************/
  as
set nocount on
declare @deviceno tinyint
declare @countpages int
declare @status smallint
declare @cntrltype smallint
declare @logicalname varchar(30)
declare @physicalname varchar(127)
declare @buildstring varchar(20)
declare @tempdevno smallint
declare @proctext varchar(255)
declare devicecursor cursor for
  select convert(tinyint,substring(convert(binary(4),low),1,1))
 as devno,
        (high - low) + 1 as pages, status, name, phyname
  from sysdevices
  where status & 2 = 2 /* it is a physical disk */
    and name <> `master'
order by 1
declare backupdevicecursor cursor for
  select status, cntrltype, name, phyname
  from sysdevices
  where status & 16 = 16 /* it is not physical disk */
    and name not in (`diskdump','diskettedumpa','diskettedumpb')
order by name
PRINT "/*****"
SELECT "--Report generated on " + convert(varchar(30),getdate())
        + " For Server " + @@servername
PRINT " "
/*******************/
/* Get disk inits. */
/*******************/
open devicecursor
create table #result
   (devno smallint not null,
    sequence smallint not null,
    chartext varchar(255) not null
   )
fetch next from devicecursor into @deviceno, @countpages,
      @status, @logicalname, @physicalname
While @@fetch_status = 0
    BEGIN
       INSERT #result SELECT @deviceno, 1, `DISK INIT'
       INSERT #result SELECT @deviceno, 2, "Name = `" 
                + rtrim(@logicalname) + "`,"
       INSERT #result SELECT @deviceno, 3, "Physname = `" 
                + rtrim(@physicalname) + "`,"
       INSERT #result SELECT @deviceno, 4, "VDEVNO = " 
                + rtrim(convert(varchar(15),@deviceno)) + ","
       INSERT #result SELECT @deviceno, 5, "SIZE = " 
                + rtrim(convert(varchar(15),@countpages))
       INSERT #result SELECT @deviceno, 6, "  "
       INSERT #result SELECT @deviceno, 7, "GO"
       INSERT #result SELECT @deviceno, 8, "  "
          If @status & 1 = 1 /* it's a default device */
          BEGIN
           INSERT #result SELECT @deviceno, 9, 
                  "EXEC sp_diskdefault " + @logicalname + ", " + "defaulton"
           INSERT #result SELECT @deviceno, 10, "GO"
           INSERT #result SELECT @deviceno, 11, "  "
         END
        fetch next from devicecursor into @deviceno, 
              @countpages, @status, @logicalname, @physicalname
    END
SELECT chartext as "--DEVICE CREATION SCRIPT"
FROM #result
order by devno, sequence
/********************************/
/** now script backup devices  **/
/********************************/
    open backupdevicecursor
    fetch next from backupdevicecursor into @status, 
              @cntrltype,@logicalname, @physicalname
    Select @tempdevno = 256 /* higher than any device */
    WHILE @@fetch_status = 0
        BEGIN
        select @proctext = "EXEC sp_addumpdevice `"
        If @cntrltype = 2
            Select @buildstring = "disk', "
        Else If @cntrltype in (3,4)
            Select @buildstring = "diskette', "
         Else If    @cntrltype = 5
            Select @buildstring = "tape', "
        select @proctext = @proctext + @buildstring + "`" 
               + rtrim(@logicalname) + "`, " + "`" 
               + rtrim(@physicalname) + "` "
        If @cntrltype = 5 and @status & 8 = 8  
             /* it's a tape, with skip header */
           Select @proctext = @proctext + ", skip "
        insert #result values (@tempdevno,1,@proctext)
        select @tempdevno = @tempdevno + 1
        fetch next from backupdevicecursor into @status, 
                 @cntrltype,@logicalname, @physicalname
      END /* while loop */
    Insert #result values (@tempdevno,1,"GO")
    select chartext as "--DUMP DEVICES"
    from #result
    where devno > 255
      order by devno, sequence
    close backupdevicecursor
    deallocate backupdevicecursor
DROP TABLE #result
Close devicecursor
deallocate devicecursor
RETURN
GO

Capture the Transaction Log If It's Still Available

If you lost the database device, but still have access to the transaction log, back it up with the NO_TRUNCATE option. This captures any transactions that have occurred since the last backup and the time of the failure of the database device, without having to examine your database device(s). You typically back up the log onto your dbname_log_backup device with noinit. Refer to the DUMP TRANSACTION syntax for more information about this option.

Drop the Affected Databases and Devices

You should typically drop the databases that were affected by the device that was lost, and then drop the database devices. When you attempt to drop the database or the affected databases, you may receive one of several errors. Use the system stored procedure sp_dbremove to drop the database.

sp_dbremove database[, dropdev]

The database specified here is the database you want to drop.

The dropdev optional parameter removes any devices used by this database. The devices are not physically deleted from disk (which is handy, because the disk may not be there).

Replace the Failed Disk

Hopefully this step is self-explanatory. You need, however, to reset any RAID configurations before restarting Windows NT. If you are using Windows NT RAID, you need to configure the RAID setup in the Disk Administrator program before the next step. Refer to your Windows NT documentation if you are using Windows NT RAID, otherwise, refer to your hardware documentation.

Re-create the Devices and Databases

You can use the results of the sp_help_diskinit stored procedure to re-create the devices. Use the results of the sp_help_revdatabase stored procedure to re-create the database in the proper sequence.

Restore the Databases and Transaction Logs as Appropriate

Perform a restore of the database(s) and recover the transaction log. At this point you should be ready to run as if you were at the moment the server failed. You may want to run the DBCC commands discussed earlier, just to verify that your database is not corrupt after the recovery.

Recovering the master and msdb Databases

These procedures work fine for ordinary databases, but restoring master requires a special set of steps. These steps are discussed in Appendix A, "Restoring the master Database."

Restoring Backups from Previous Versions of SQL Server

As mentioned previously, you can use the restore process to upgrade a backup from a version 4.2 or 6.0 SQL Server database. In addition, you can use this process to test an upgrade of your databases. Perform the following steps:

1. Re-create the database devices and databases in the proper sequence. The previous stored procedure, sp_help_diskinit, should function on version 4.2x and 6.0 of SQL Server. The sp_help_revdatabase stored procedure works on version 6.0 of SQL Server as well. It may function on version 4.2x, but it has not been tested by the author as of this writing.

2. Restore the backup using the load database command or the Enterprise Manager Restore dialog.

3. Verify that the database is compatible with version 6.5 of SQL Server.

4. Resolve any errors from the verification step.

Step 3 is where all of the interesting things happen. To verify that the database is compatible with version 6.5 of SQL Server, you run the chkupg65.exe utility.

chkupg65.exe [/Usa] [/Ppassword] [/Sservername] /ofilename

The /U parameter is the login ID of the user connecting to SQL Server.

The /P parameter is the password for the login.

The /S parameter is the name of the server to which you want to connect.

The /o parameter is the drive, directory and filename of the report.

The utility checks for three conditions: It verifies that the database is not set to read-only (an upgrade changes the system tables), that reserved words are not used as part of SQL Server objects, and that entries for all SQL Server objects such as views and stored procedures are in the syscomments table.

The key word identity is an example of the reserved word problem. If you named a table identity in SQL Server version 4.x, the table could not be used in version 6.5; "identity" now has a special meaning.

The last problem, entries in the syscomments table, requires explanation. When SQL Server upgrades a database, it drops and re-creates objects in the database by reading the definition of the object as it was captured in the syscomments table when the original stored procedure, view, or some other object was created. In SQL Server version 4.x, some developers deleted the entries in this system table to protect their source code. Therefore, when an upgrade occurs, the objects would no longer exist. In version 6.x, Microsoft introduced encryption technologies that protect source code in this system table. Encryption is discussed further when you examine views and stored procedures on Day 15, "Views, Stored Procedures, and Triggers."

This process is no substitute for properly upgrading your previous releases of SQL Server. However, if you need to recover data that was backed up with a previous release, the availability of this option can save you much time and grief restoring data. You should be aware that the format of backups has changed in release 6.5, however, and versions 4.2x and 6.0 of SQL Server can't restore backups that were made in SQL Server 6.5.

Summary

Backup and recovery is probably the most important part of maintaining your SQL Server system. Backing up a database requires that you first create backup devices, and it's likely that you will want to schedule these backups. You should also regularly test restoring these backups to verify that they work successfully and to keep you comfortable with the restore process. You should also protect your data with RAID on your servers, preferably using either hardware RAID or Windows NT RAID support. As a last resort, you can use SQL Server mirroring.

Q&A

Q Can you back up a database to a new file dynamically?

A
Yes, simply specify the device as a file instead of the name of a backup device.

Q Which command is used to create a backup device?


A
sp_addumpdevice (or Enterprise Manager).

Q Does RAID 0 protect your data when a disk is lost?


A
No, RAID level 0 is just striping of data, and doesn't provide any fault tolerance.

Q What is the command to restore a database from a backup?


A
The LOAD DATABASE command.

Q How would I schedule a task to back up a database?


A
Use the Schedule button in the Database Backup/Restore dialog.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you've learned. Try to understand the quiz and exercise answers before continuing on to the next day's lesson. Answers are provided in Appendix B.

Quiz

1. If you receive an error when restoring a transaction log backup, what's the first thing you should check?

2. Which RAID option is preferred for holding transaction logs?

3. Can you back up to a device whose physical location is \\myserver\sqlbackups\master_backup.dat?

4. The transaction log backup option is unavailable in the Database Backup/Restore dialog. Can you explain why?

5. If you rebuild the master database, what other database must be recovered?

Exercises

1. Create backup devices for each of your databases, and set up automated weekly backups for those databases.

2. Create a test database, back it up, and then test restoring it. Try this for a full database backup, and then also test recovering transaction logs. Make changes to your database, such as creating a table, between each transaction log backup. When you restore, apply each transaction log and verify that only the appropriate changes are recovered with each restore.

3. Create a new test database.

4. Create a new backup device for the database and for the transaction log.

5. Back up the database (with init).

6. Create a table in the database.

7. Back up the transaction log for the database (with init).

8. Create a second table in the database.

9. Back up the transaction log again for the database (with noinit).

10. Restore the database.

11. Verify that there are no tables in the database.

12. Restore the first transaction log.

13. Verify that your first table has been restored.

14. Restore the second transaction log.

15. Verify that your second table has been restored.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.