Database Backup and Recovery

Updated : July 19, 2001

On This Page

Creating a Backup and Recovery Plan
Selecting Backup Devices and Media
Using Backup Strategies
Creating a Backup Device
Performing Backups
Restoring a Database
Restoring the Master Database

Information is the fuel that drives the enterprise, and the most critical information is often stored in databases. Databases are where you'll find an organization's customer account information, partner directories, product knowledge base, and other important data. To protect an organization's data and to ensure the availability of its databases, you need a solid database backup and recovery plan.

Backing up databases can protect against accidental loss of data, database corruption, hardware failures, and even natural disasters. It's your job as a database administrator to perform backups and store them in a safe and secure location.

Creating a Backup and Recovery Plan

Creating and implementing a backup and recovery plan is one of your most important duties as a database administrator. Think of database backup as an insurance plan for the future—and for your job. Important data is accidentally deleted all the time. Mission-critical data can become corrupt. Natural disasters can leave your office in ruins. With a solid backup and recovery plan you can recover from any of these situations. Without one, you're left with nothing to fall back on.

Initial Backup and Recovery Planning

Creating and implementing a backup and recovery plan takes time. You'll need to figure out which databases need to be backed up, how often the databases should be backed up, and more. To help you create a plan, consider the following:

  • What type of database are you backing up? System and user databases often have different backup and recovery needs. For example, the master database is essential for all Microsoft SQL Server operations. If the master database fails or becomes corrupt, it takes the whole server down with it. But you don't need to back up master every hour or every half hour—as you might have to do with a critical user database that handles real-time customer transactions. You need to back up master only after you create a database, change configuration values, configure SQL logons, or perform similar activities that make changes to the database.

  • How important is the data in the database? How you judge the data's importance can go a long way toward determining when and how you should back it up. While you may back up a development database weekly, you would probably back up a production database at least daily. The data's importance also drives your decision about the type of backup. With that development database, you'd probably do a full backup once a week. With an in-house customer order database that's updated throughout the weekday, you'd probably perform full backups twice a week and supplement this with daily differential backups and hourly backups for the transaction logs. You may even want to set named log marks that allow recovery up to a specific point of work.

  • How often are changes made to the database? The frequency of change can drive your decision about how often the database should be backed up. Because a read-only database doesn't ordinarily change, it doesn't need to be backed up regularly. On the other hand, a database that's updated nightly should be backed up after the nightly changes are posted. A database that's updated around the clock should be backed up continually.

  • How quickly do you need to recover the data? It's important to consider time when you create a backup plan. For mission-critical databases, you may need to get the database back online swiftly; to do this, you may need to alter your backup plan. Instead of backing up to tape, you may want to back up to disk drives or use multiple backup devices. Both options are much faster than restoring from a single tape device.

  • Do you have the equipment to perform backup? You need backup hardware to perform backups. If you don't have the hardware, you can't perform backups. To perform timely backups, you may need several backup devices and several sets of backup media. Backup hardware includes a tape drive, optical drives, removable disk drives, and plain old disk drives. Generally, tape drives are less expensive but slower than other types of drives.

  • What's the best time to schedule backups? You'll want to schedule backups when database usage is as low as possible. This will speed the backup process. However, in the real world you can't always schedule backups for off-peak hours. So you'll need to carefully plan when key databases are backed up.

  • Do you need to store backups off-site? Storing copies of backup tapes off-site is essential to the recovery of your systems in the case of a natural disaster. In your off-site storage location, you should also include copies of the software you may need to install in order to restore operations on a new system.

Backing up a database is a bit different than backing up a server or a workstation. This is primarily because you'll often need to combine all (or nearly all) of the available techniques to ensure that you can recover a database completely. The basic types of backups you can perform include

  • Complete database backups Perform a full backup of the database, including all objects, system tables, and data. When the backup starts, SQL Server copies everything in the database and also includes any needed portions of the transaction log as the backup is in progress. Because of this, you can use a complete backup to recover the complete state of the data in the database at the time the backup operation finishes.

  • Differential backups Designed to back up data that has changed since the last complete backup. Because you store only the changes, the backup is faster and you can perform it more often. As with complete backups, differential backups include needed portions of the transaction logs, which allow you to restore the database to the time when the backup operation finishes.

    Tip You can use differential backups only in conjunction with complete backups, and you can't perform differential backups on the master database. Don't confuse differential backups with incremental backups. Differential backups record all changes since the last full backup (which means the size of the incremental backup grows over time). Incremental backups record changes since the most recent full or incremental backup (which means the size of the incremental backup is usually much smaller than a full backup).

  • Transaction log backups Transaction logs are serial records of all database modifications and are used during recovery operations to commit completed transactions and to roll back uncompleted transactions. When you back up a transaction log, the backup stores the changes that have occurred since the last transaction log backup and then truncates the log, which clears out transactions that have been committed or aborted. Unlike complete and differential backups, transaction log backups record the state of the transaction log at the time the backup operation starts (not when it ends).

  • File and filegroup backups Allow you to back up database files and filegroups rather than the entire database. This is useful with large databases where, to save time, you want to back up individual files rather than the entire database. Many factors affect file and filegroup backups. When you use file and filegroup backups, you must back up the transaction log as well. Because of this dependency, you can't use this backup technique if Truncate Log On Checkpoint is enabled. Furthermore, if objects in the database span multiple files or filegroups, you must back up all the related files and filegroups at the same time.

SQL Server 2000 uses recovery models to help you plan backups. The types of databases you are backing up and the types of backups you perform drive the choices for recovery models. The three recovery models shown on the following page are available:

  • Simple The simple recovery model is designed for databases that need to be recovered to the point of the last backup. The backup strategy with this model should consist of full and differential backups. You cannot perform transaction log backups when the simple recovery model is enabled. SQL Server 2000 turns on the Truncate Log On Checkpoint option, which clears out inactive entries in the transaction log on checkpoint. Because this model clears out transaction logs, it is ideal for most system databases.

  • Full The full recovery model is designed for databases that need to be recovered to the point of failure or to a specific point in time. With this model all operations are logged, including bulk operations and bulk loading of data. The backup strategy with this model should include full, differential, and transaction log backups, or full and transaction log backups only.

  • Bulk-logged The bulk-logged recovery model reduces the log space usage yet retains most of the flexibility of the full recovery model. With this model, bulk operations and bulk loads are minimally logged and cannot be controlled on a per operation basis. You'll need to manually redo bulk operations and bulk loads if the database fails before you perform a full or differential backup. The backup strategy with this model should include full, differential, and transaction log backups, or full and transaction log backups only.

Each database can have a different recovery model. By default, master, msdb, and tempdb use the simple recovery model, and the model database uses the full recovery model. Model is the template database for all new databases, so if you change the default setting all new databases for the database server instance use the new default model. You set the recovery model by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with.

  2. If plan to switch from bulk-logged recovery to simple recovery, perform a transaction log backup prior to making the change and then change your backup strategy so that you no longer perform transaction log backups.

  3. Click the plus sign (+) next to the server you want to work with again and then click the plus sign (+) next to the Databases folder.

  4. Right-click the database you want to work with and then, from the shortcut menu, choose Properties. This displays the database's Properties dialog box.

  5. Use the Model selection list in the Options tab to change the recovery model and then click OK.

  6. If you switched from simple recovery to full or bulk-logged recovery, add transaction log backups to your backup strategy for the database.

Planning for Backups of Replicated Databases

Databases that are replicated present a special problem for backup and restore. This is primarily because the traditional database architecture is extended to include three server roles:

  • Publisher Servers that make data available for replication, track changes to data, and maintain other information about source databases.

  • Distributor Servers that distribute replicated data and store the distribution database.

  • Subscriber The destination servers for replication. They store the replicated data, receive updates, and in some cases can also make changes to data.

Because of these roles, you'll need to use additional strategies for backing up and restoring replicated databases. For complete details see the section of the SQL Server Books Online entitled "Backing Up and Restoring Replication Databases." (Click Start, then Programs, then Microsoft SQL Server 2000, and then choose Books Online.)

Planning for Backups of Very Large Databases

When backing up and restoring very large databases, you may want to take advantage of parallel backup and restore. Parallel backup and restore allow SQL Server 2000 to use multiple threads to read and write data. This means SQL Server can read data from, and write data to, multiple data sources. Backup and restore use parallel input/output (I/O) in different ways:

  • Backup uses one thread per disk device to read data from the database when a database has files on several disk devices.

  • Restore uses one thread per disk device while it initializes a database that it's creating for the restore process, provided the database is defined with files on several disks.

  • Both backup and restore use one thread per backup device when a backup set is stored on multiple backup devices.

As you can see from the previous list, to take advantage of parallel I/O you must implement your backup strategy so that databases use

  • Multiple disk drives for storing data

  • Multiple backup devices for backing up and restoring data

Once you determine the backup operations you'll use on each database and how often you'll back up each database, you can select backup devices and media that meet these requirements. The next section covers backup devices and media.

Selecting Backup Devices and Media

Many different solutions are available for backing up data. Some are fast and expensive. Others are slow but very reliable. The backup solution that's right for your organization depends on many factors, including

  • Capacity The amount of data that you need to back up on a routine basis. Can the backup hardware support the required load given your time and resource constraints?

  • Reliability The reliability of the backup hardware and media. Can you afford to sacrifice reliability to meet budget or time needs?

  • Extensibility The extensibility of the backup solution. Will this solution meet your needs as your organization grows?

  • Speed The speed with which data can be backed up and recovered. Can you afford to sacrifice speed to reduce costs?

  • Cost The cost of the backup solution. Does the solution fit within your budget?

Capacity, reliability, extensibility, speed, and cost are the issues that will influence your choice of a backup plan. If you determine the relative value of these issues to your organization, you'll be on the right track to selecting an appropriate backup solution. Some of the most commonly used backup solutions include

  • Tape drives Tape drives are the most common backup devices. They use magnetic tape cartridges to store data. Magnetic tapes are relatively inexpensive but aren't highly reliable. Tapes can break or stretch. They can also lose information over time. The average capacity of tape cartridges ranges from 100 MB to 2 GB. Compared with other backup solutions, tape drives are fairly slow. Still, the key selling point is the low cost of tape drives and magnetic tapes.

  • DAT drives DAT (digital audio tape) drives are quickly replacing standard tape drives as the preferred backup devices. DAT drives use 4-mm tapes and 8-mm tapes to store data. DAT drives and tapes are more expensive than standard tape drives and tapes, but they offer higher speed and more capacity. DAT drives that use 4-mm tapes typically can record over 30 MB per minute and have capacities of up to 16 GB. DAT drives that use 8-mm tapes typically can record more than 10 MB per minute and have capacities of up to 10 GB (with compression).

    Tip To perform faster backup and recovery operations, you can use multiple backup devices with SQL Server. For example, if it normally takes four hours to perform a full backup or restoration of the database, you can cut the backup and restoration time in half using two backup devices; with four backup devices you could fully back up or restore the database in an hour.

  • Autoloader tape systems Autoloader tape systems use a magazine of tapes that create extended backup volumes capable of meeting the high capacity needs of the enterprise. With an autoloader system, tapes within the magazine are automatically changed as needed during the backup or recovery process. Most autoloader tape systems use DAT tapes. The typical system uses magazines with between 4 and 12 tapes. The key drawback to these systems is the high cost.

  • Magnetic optical drives Magnetic optical drives combine magnetic tape technology with optical lasers to create a more reliable backup solution than DAT. Magnetic optical drives use 3.5-inch disks and 5.25-inch disks that look similar to floppies but are much thicker. Typically, magnetic optical disks have capacities of between 1 GB and 4 GB.

  • Tape jukeboxes Tape jukeboxes are similar to autoloader tape systems. Jukeboxes use magnetic optical disks rather than DAT tapes to offer high- capacity solutions for the enterprise. These systems load and unload disks stored internally for backup and recovery operations. The key drawback to tape jukeboxes is the high cost.

  • Removable disks Removable disks, such as Iomega Jaz, are increasingly being used as backup devices. Removable disks offer good speed and ease of use for a single drive or single system backup. However, the disk drives and the removable disks tend to be more expensive than standard tape or DAT drive solutions.

  • Disk drives Disk drives provide the fastest way to back up and restore databases. With disk drives, you can often accomplish in minutes what a tape drive takes hours to do. When your needs mandate a speedy recovery, nothing beats a disk drive. The drawbacks to disk drives, however, are high cost and low extensibility.

Although backup device selection is an important step in implementing a backup and recovery plan, it isn't the only step. You also need to purchase the tapes or the disks, or both, that will allow you to implement your backup and recovery plan. The number of tapes, disks, or drives you need depends on

  • How much data you'll be backing up

  • How often you'll be backing up the data

  • How long you'll need to keep additional data sets

The typical way to implement backups is to use a rotation schedule whereby you rotate through two or more sets of tapes, disks, or files. The idea is that you can increase media longevity by reducing media usage and at the same time reduce the number of actual tapes, disks, or files you need to ensure that you have data on hand when necessary.

Best Practice For important databases, I recommend using four media sets. Use two sets for regular rotations. Use the third set for the first rotation cycle at the beginning of each month. Use the fourth set for the first rotation cycle of each quarter. This technique allows you to recover the database in a wide variety of situations.

Using Backup Strategies

Table 11-1 lists backup strategies you may want to use. As you can see, these backup strategies are based on the type of database as well as the type of data. Two key things to keep in mind when planning a backup strategy are shown on the following page.

  • The master database stores important information about the structure of other databases, including the database size. Any time database information or structure changes, master can get updated without your knowing about it. For example, the size of most databases changes automatically, and when this happens master is updated. Because of this, often the best backup strategy for master is to schedule backups every other day and to rotate through several backup sets so that you can go back to several different versions of master if necessary.

  • You can use transaction logs to recover databases up to the point of failure and up to a point of work. To recover to a point of work you must insert named log marks into the transaction log using BEGIN TRANSACTION WITH MARK. You can then recover to a mark in the log using RESTORE LOG WITH STOPATMARK or RESTORE LOG WITH STOPBEFOREMARK.

Table 11-1 Backup Strategies for System and User Databases

Database Type

Details

Strategy

User

Recovery up to the minute

Run complete backups twice a week, if possible. Use nightly differential backups and back up the recovery transaction log every 10 minutes during business hours. Don't use Truncate Log On Checkpoint, as this will make it impossible to recover some transactions. To improve backup restore speed, use multiple backup devices whenever possible.

 

Recovery up to a point of work

Run complete backups twice a week, if possible. Use nightly differential backups and back up the recovery transaction log every 10 minutes during business hours. Don't use Truncate Log On Checkpoint. Use named transactions to insert named marks into the transaction logs. To improve backup/restore speed, use multiple backup devices whenever possible. Recovery up to the hour Run complete backups twice a week, if possible. Use nightly differential backups and back up the recovery transaction log every 30 minutes during business hours. Don't use Truncate Log On Checkpoint. To improve backup/restore speed, use multiple backup devices whenever possible.

 

Recovery of daily changes

Run complete backups at least once a week. Use daily nightly differential back- ups and back up the changes transaction log every four hours during business hours. Don't use Truncate Log On Checkpoint.

 

Read-only

Schedule a complete backup of the database every 30 days and supplement this with an additional complete backup whenever the database is modified.

System

distribution

Available when you configure replication and the server is acting as a distributor. Schedule complete backups after snap- shots. With transactional replication, schedule regular log backups.

 

master

Run complete backups immediately after creating or removing databases, changing the size of a database, adding or removing logons, or modifying server configuration settings. Don't forget to maintain several backup sets for master.

 

msdb

If you schedule jobs through the SQL Server Agent, back up this database regularly because this is where the job schedule and history is maintained and backup history is stored.

 

model

Treat like a read-only database.

 

tempdb

Normally doesn't need to be backed up. This database is recreated each time you start SQL Server.

Creating a Backup Device

Previous versions of SQL Server required you to configure backup devices before you could back up databases. With SQL Server 2000, this is no longer the case, and you don't need to explicitly define backup devices. Nevertheless, backup devices do provide an easy way to ensure that you create backups that have the same filename and location time after time. With consistent names and locations, you can more easily manage the backup and recovery process.

To create a backup device using Enterprise Manager, complete the following steps:

  1. Start Enterprise Manager and then access the database server instance you want to work with.

  2. Click the plus sign (+) next to the server's Management folder.

  3. Right-click Backup and then, from the shortcut menu, choose New Backup Device. This opens the dialog box shown in Figure 11-1.

  4. In the Name field, type the name of the logical backup device. Use a short but descriptive name, such as Customer Device or Master Device, to make the device easier to work with.

  5. If you've installed a tape drive and want to back up to the tape drive, select the Tape Drive Name field and then use the related drop-down list box to select the target drive.

  6. If you're backing up to a file, select the File Name option button and then enter the full path to the backup file you want to associate with this device, such as E:\MSSQL\BACKUP\CUSTOMER.BAK.

  7. Click OK.

With Transact-SQL, you create backup devices using sp_addumpdevice. The syntax and usage for this command is shown in Sample 11-1. This command uses many different arguments, including device_type, logical_name, physical_name, controller_type, and device_status. The device_type is the type of device you're using—disk, tape, or pipe. The logical_name is the name of the backup device. The physical_name is the full path to the backup file. The controller_type is 2 for a disk, 5 for a tape, and 6 for a named pipe. The device_status is either noskip, to read ANSI tape headers, or skip, to skip ANSI tape headers.

Cc917637.ppc1101(en-us,TechNet.10).gif

Figure 11-1: Create a backup device to make it easier to manage backups.

Sample 11-1 sp_addumpdevice Syntax and Usage

Syntax

sp_addumpdevice [@devtype =] 'device_type',
   [@logicalname =] 'logical_name',
   [@physicalname =] 'physical_name'
   [, {
      [@cntrltype =] controller_type |
      [@devstatus =] 'device_status'
   }
   ]

Usage

EXEC sp_addumpdevice 'disk', 'Customer',    'c:\mssql\backup\cust.bak'
EXEC sp_addumpdevice 'disk', 'Customer on Backup Server',    '\\omega\backups\cust.bak'
EXEC sp_addumpdevice 'tape', 'Customer on Tape', '\\.\tape0'

Performing Backups

Backups are an essential part of database administration. They're so important that SQL Server provides multiple backup procedures and several backup-related wizards—all designed to help you better manage database backup and recovery. In this section I'll look at standard backup procedures, the Backup Wizard, and the Transact-SQL backup process. The final ingredient for backups involves database maintenance plans, which you'll learn about in Chapter 12, "Database Automation and Maintenance."

Creating Backups in Enterprise Manager

In Enterprise Manager you can start the backup process using any of these techniques:

  • Select the server you want to work with and then, from the Tools menu, choose Backup Database. This opens the SQL Server Backup dialog box.

  • Select the server you want to work with and then, from the Tools menu, choose Wizards. This opens the Wizards dialog box. Click the plus sign (+) next to Management and then double-click Backup Wizard.

  • Right-click the database you want to back up, point to All Tasks, and then select Backup Database.

Regardless of which technique you use, the steps you follow are similar. Rather than covering the same information twice, I'll focus on the SQL Server Backup dialog box and how you can use it to perform backups in these situations:

  • Creating a new backup set

  • Adding to an existing backup set

You can use these techniques with the Backup Wizard as well. The key difference is that you'll need to go through a series of dialog boxes.

Creating a New Backup Set

Whenever you back up a database for the first time or start a new rotation on an existing backup set, follow these steps to create the backup:

  1. Select the server you want to work with and then, from the Tools menu, choose Backup Database. This opens the SQL Server Backup dialog box shown in Figure 11-2.

  2. Use the Database drop-down list box to select the database you want to back up.

    Cc917637.ppc1102(en-us,TechNet.10).gif

    Figure 11-2: Use the SQL Server Backup dialog box to start or schedule a backup. If this is the first time you're running Backup, some of the backup types may be unavailable.

  3. In the Name field, type a name for the backup set you're creating. This is an ordinary nontechnical name that'll help you tell at a glance what the backup contains. For example, name the first backup set for the customer database Customer Backup Set 1. Then you can add the complete, differential, and transaction log backups for this rotation to the set.

  4. In the Description field type a description of the backup, such as "Set 1 contains the weekly complete, daily differential, and hourly transaction log backups."

  5. In the Backup area, use the option buttons to select the type of backup. Because this is your first time running a backup on the database, some of the options may be unavailable. Don't worry—once you run a complete or differential backup, the other options should become available.

    Note: The only available backup option for the master database is Database – Complete. That's because you can run only complete backups on master.

  6. If a backup set exists and is listed in the Destination area, select it and click Remove.

  7. Click Add to display the Select Backup Destination dialog box shown in Figure 11-3. To use a file as the backup destination, select the File Name option button and enter the full path to the backup file, such as E:\DATA\ BACKUPS\CUST.BAK or \\OMEGA\BACKUPS\CUST.BAK. To use a backup device, select the Backup Device option button and then choose the backup destination using the drop-down list box. Click OK when you're ready to continue.

    Cc917637.ppc1103(en-us,TechNet.10).gif

    Figure 11-3: Use the Select Backup Destination dialog box to select a file or backup device to use as the backup destination.

  8. To schedule the backup, click Schedule and then configure the backup schedule as discussed in Chapter 12, "Database Automation and Maintenance."

    To set additional options for the backup, click the Options tab (see Figure 11-2). You use the available options as follows:

    • Verify Backup Upon Completion Reads the entire backup and checks for errors.

    • Eject Tape After Backup Set to eject the tape after the backup (only valid with tape devices).

    • Remove Inactive Entries From The Transaction Log Cleans out entries that are no longer needed after the backup. These entries are for transactions that have been committed or rolled back. (Set by default for transaction log backups.)

      Tip You'll usually want to perform one last log backup before you try to restore a corrupt database. When you do, you'll want to clear the option and perform the log backup without truncation. This option is the same as running BACKUP LOG NO_TRUNCATE.

    • Check Media Set Name And Backup Set Expiration Ensures that you're writing to the correct tape set and that the tape expiration date has not been reached.

    • Backup Set Will Expire On When backing up to a tape device, you can check this option and then set an expiration date. This option allows the backup to overwrite the media after a specified date or period.

    • Initialize And Label Media When backing up to tape devices, you can use this option to erase the previous contents of the media and then add a new label and description.

  9. Click OK to start the backup or confirm that you want to schedule the backup. For an immediate backup, you'll see the Backup Progress dialog box, which displays a progress bar as the backup proceeds. If you opted to verify the data, the verification process starts immediately after the backup ends.

Adding to an Existing Backup Set

When you want to add to an existing backup set, complete the following steps:

  1. Select the server you want to work with and then, from the Tools menu, choose Backup Database. This opens the SQL Server Backup dialog box shown previously in Figure 11-2.

  2. Use the Database drop-down list box to select the database to be backed up.

    In the Backup area, use the option buttons to select the type of backup you want to perform:

    • Database – Complete

    • Database – Differential

    • Transaction Log

    • File And Filegroup

  3. A backup set should be listed in the Destination area. If one isn't, click Add to display the Choose Backup Destination and then enter the location of the existing backup. Click OK when you're ready to continue.

  4. If you want to add data to the existing backup set, select the Append To Media option button.

    Real World Whether you back up to a tape or disk drive, you should use the tape rotation philosophy. Create multiple tape sets and then write to these sets on a rotating basis. With a disk drive, for example, you could create these backup files on different network drives and use them as follows:

    • //omega/data1drive/backups/cust_set1.bak Used on week 1, 3, 5, and so on for complete and differential backups of the customer database.

    • //omega/data2drive/backups/cust_set2.bak Used on week 2, 4, 6, and so on for complete and differential backups of the customer database.

    • //omega/data3drive/backups/cust_set3.bak Used on the first week of the month for complete backups of the customer database.

    • //omega/data4drive/backups/cust_set4.bak Used on the first week of the quarter for complete backups of the customer database.

    Don't forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups on week 1. Then, when starting the next rotation on week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.

  5. Click the Options tab to double-check the backup options. For transaction log backups, you'll usually want to select the Remove Inactive Entries From Transaction Log check box. This ensures that inactive entries are cleared out of the transaction log after a backup.

  6. To schedule the backup, click Schedule and then configure the backup schedule as discussed in Chapter 12, "Database Automation and Maintenance."

  7. Click OK to start the backup or to confirm that you want to schedule the backup. For an immediate backup, you'll see the Backup Progress dialog box, which displays a progress bar as the backup proceeds. If you opted to verify the data, the verify process starts immediately after the backup ends.

Using Striped Backups with Multiple Devices

Through a process called parallel striped backups, SQL Server can perform backups to multiple backup devices simultaneously. As you can imagine, writing multiple backup files at the same time can dramatically speed backup operations. The key to this speed, however, lies in having physically separate devices, such as three different tape devices or three different drives that you're using for the backup. You can't write parallel backups to a single tape device and you can't write parallel backups to the same drive.

Multiple devices used in a backup operation are referred to as a media set. SQL Server allows you to use from 2 to 32 devices to form the media set. These devices must be of the same type. For example, you can't create a striped backup with one backup tape device and one backup drive device.

The two main operations you'll perform are

  • Creating a new media set

  • Adding to an existing media set

Creating a New Media Set

To create a new media set using multiple devices, complete the following steps:

  1. Select the server you want to work with and then create each of the backup devices you need in the media set as described in the section of this chapter entitled "Creating a Backup Device."

  2. Open the SQL Server Backup dialog box by going to the Tools menu and choosing Backup Database. (You can't create a multiple device backup with the Backup Wizard.)

  3. Follow the steps outlined in the section of this chapter entitled "Creating a New Backup Set." Repeat step 7 for each backup device you want to use in the media set.

Adding to an Existing Media Set

To add to an existing media set, complete the following steps:

  1. Open the SQL Server Backup dialog box by going to the Tools menu and choosing Backup Database. (You can't create a multiple backup with the Backup Wizard.)

  2. Follow the steps outlined in the "Adding to an Existing Backup Set" section of this chapter. The only change is that in step 4 you should see a list of all backup devices used in the media set. If you don't, you'll need to add them one by one using the Add button and the related Choose Backup Destination dialog box.

Using Transact-SQL Backup

An alternative to the backup procedures in Enterprise Manager is to use the T-SQL BACKUP statement. You'll use BACKUP DATABASE to back up databases and BACKUP LOG to back up transaction logs.

Tip If you back up databases using Transact-SQL, you lose one of the biggest benefits of SQL Server—the automated recovery process. With automated recovery, you don't have to worry about which backup to apply when, which command flags to use when, and more. Furthermore, because you can schedule automated and unattended backups, you don't really need to run backups manually through SQL as often as you used to. So I recommend using the Enterprise Manager backup and restore process whenever possible.

BACKUP DATABASE has dual syntax. Sample 11-2 shows the syntax and usage for complete and differential backups. A complete backup is the default operation.

Sample 11-2 BACKUP DATABASE Syntax and Usage for Complete and Differential Backups

Syntax

BACKUP DATABASE {database_name | @database_name_var}
TO <backup_device> [,...n]
[WITH
   [BLOCKSIZE = {blocksize | @blocksize_variable}]
   [[,] DESCRIPTION = {text | @text_variable}]
   [[,] DIFFERENTIAL]
   [[,] EXPIREDATE = {date | @date_var}
      | RETAINDAYS = {days | @days_var}]
   [[,] PASSWORD = {password | @password_variable}]
   [[,] FORMAT | NOFORMAT]
   [[,] {INIT | NOINIT}]
   [[,] MEDIADESCRIPTION = {text | @text_variable}]
   [[,] MEDIANAME = {media_name | @media_name_variable}]
   [[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
   [[,] [NAME = {backup_set_name | @backup_set_name_var}]
   [[,] {NOSKIP | SKIP}]
   [[,] {NOREWIND | REWIND}]
   [[,] {NOUNLOAD | UNLOAD}]
   [[,] [RESTART]
   [[,] STATS [= percentage]]
]

Usage

USE master
EXEC sp_addumpdevice 'disk', 'Customer Backup Set 1',
   'f:\data\backup\Cust2.dat'
BACKUP DATABASE 'Customer' TO 'Customer Backup Set 1'

Sample 11-3 shows the BACKUP DATABASE syntax for file and filegroup backups.

Sample 11-3 BACKUP DATABASE Syntax and Usage for File or Filegroup Backups

Syntax

BACKUP DATABASE {database_name | @database_name_var}
   <file_or_filegroup> [,...n]
TO <backup_device> [,...n]
[WITH
   [BLOCKSIZE = {blocksize | @blocksize_variable}]
   [[,] DESCRIPTION = {text | @text_variable}]
   [[,] EXPIREDATE = {date | @date_var}
      | RETAINDAYS = {days | @days_var}]
   [[,] PASSWORD = {password | @password_variable}]
   [[,] FORMAT | NOFORMAT]
   [[,] {INIT | NOINIT}]
   [[,] MEDIADESCRIPTION = {text | @text_variable}]
   [[,] MEDIANAME = {media_name | @media_name_variable}]
   [[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
   [[,] [NAME = {backup_set_name | @backup_set_name_var}]
   [[,] {NOSKIP | SKIP}]
   [[,] {NOREWIND | REWIND}]
   [[,] {NOUNLOAD | UNLOAD}]
   [[,] [RESTART]
   [[,] STATS [= percentage]]
]

Usage

USE master
EXEC sp_addumpdevice 'disk', 'Customer Backup Set 1',
   'f:\data\backup\Cust2.dat'
BACKUP DATABASE Customer
   FILE = 'Customer_data',
   FILEGROUP = 'Primary',
   FILE = 'Customer_data2',
   FILEGROUP = 'Secondary'
   TO 'Customer Backup Set 1'

Sample 11-4 shows the syntax for BACKUP LOG. By default, this command truncates the log after the backup.

Sample 11-4 BACKUP LOG Syntax and Usage

Syntax

BACKUP LOG {database_name | @database_name_var}
{
   [WITH
      { NO_LOG | TRUNCATE_ONLY }]
}
TO <backup_device> [,...n]
   [WITH
      [BLOCKSIZE = {blocksize | @blocksize_variable}]
      [[,] DESCRIPTION = {text | @text_variable}]
      [[,] EXPIREDATE = {date | @date_var}
         | RETAINDAYS = {days | @days_var}]
      [[,] PASSWORD = {password | @password_variable}]
[[,] FORMAT | NOFORMAT]
   [[,] {INIT | NOINIT}]
   [[,] MEDIADESCRIPTION = {text | @text_variable}]
   [[,] MEDIANAME = {media_name | @media_name_variable}]
   [[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
   [[,] [NAME = {backup_set_name | @backup_set_name_var}]
   [[,] NO_TRUNCATE]
   [[,] {NOSKIP | SKIP}]
   [[,] {NOREWIND | REWIND}]
   [[,] {NOUNLOAD | UNLOAD}]
   [[,] [RESTART]
   [[,] STATS [= percentage]]
]

Usage

USE master
EXEC sp_addumpdevice 'disk', 'Customer_log1',
   'f:\data\backup\Cust_log.dat'
BACKUP LOG Customer
   TO Customer_log1

Performing Transaction Log Backups

Transaction logs are essential to the timely recovery of SQL Server databases. Unlike database backups, which can be complete or differential, transaction log backups are usually incremental. This means that each transaction log backup has a record of transactions only within a certain time frame. Transaction logs are always applied in sequence—with the completion time of the last complete or differential backup marking the beginning of a transaction log sequence.

Consequently, in order to restore the database you must apply each transaction logon sequence up to the point of failure. For example, if you run a complete backup at 1 P.M. and the database fails at 1:46 P.M., you would restore the last complete backup and then apply each transaction log created after that time, such as the backups at 1:15 P.M., 1:30 P.M., and 1:45 P.M. As you can see, without the incremental transaction log backups you'd lose all the transactions that took place after the 1 P.M. complete backup.

You can perform transaction log backups like any other backup. Still, there are a few details that you should know beforehand, and the following sections cover these details.

Options and Commands that Invalidate Log Sequences

Although the normal backup process for transaction logs is fairly straightforward, SQL Server throws a few curveballs by providing option flags that you can set for the backup or the database, or both. The following database options prevent you from using a transaction log sequence to recover a database:

  • Truncate Log On Checkpoint A database option that clears out inactive entries in the transaction log on checkpoint

  • Using Non-Logged Operations Commands that bypass the log invalidate a log backup sequence

  • ALTER DATABASE Adding or deleting files with ALTER DATABASE invalidates a backup sequence

Tip As I stated earlier, the completion time of the last complete or differential backup marks the beginning of a transaction log sequence. If you use any of the previous commands and invalidate a log sequence, perform a complete or differential backup to start a new sequence.

Log Truncation Options

When you back up transaction logs, you have several options that determine how the backups are made. With SQL Server Backup in Enterprise Manager, you can use the Remove Inactive Entries From The Log option. Setting this option clears committed transactions out of the log after a log backup. The BACKUP LOG command normally clears out committed or aborted transactions after a log backup as well. However, you can override this behavior with these options:

  • TRUNCATE_ONLY Removes inactive entries from the log without creating a backup. This invalidates the log sequence.

  • NO_LOG Same as TRUNCATE_ONLY but doesn't log the BACKUP LOG command in the transaction log. This option is designed for a situation where the transaction log or its home drive is full and you must truncate the log without writing to the log device.

  • NO_TRUNCATE Writes all the transaction log entries from the last backup to the point of failure. Use this option when the database is corrupt and you're about to restore it.

Tip After you use TRUNCATE_ONLY or NO_LOG, always perform a complete or differential backup. This revalidates the log sequence. Additionally, because you can grow logs automatically, you should rarely encounter a situation where you need to truncate the log without logging. The log can run out of space only if you set a maximum size or the drive(s) that the log uses runs out of space.

Restoring a Database

Occasional database corruption, hardware failure, and natural disasters are facts of life, and as a database administrator you need to be able to restore the database if any of these mishaps occur. Even if you're an old pro at backup and restore, keep in mind that restoring a database is a bit different from restoring an operating system or recovering other types of applications. The mix of complete, differential, and transaction log backups ensures that you can get up-to-the-minute recovery of a database, but it complicates the recovery process.

In the section that follows you'll find tips and advice on troubleshooting database corruption. In the sections after that you'll find step-by-step procedures for restoring a database in various situations, including

  • Restoring a database using backups created in Enterprise Manager

  • Restoring a file or filegroup

  • Restoring a database from a device

  • Restoring a database to a different location

  • Restoring a database using Transact-SQL

Database Corruption and Problem Resolution

All your database administration know-how comes into play in one defining moment. That's the moment when you attempt to restore a database. The techniques you use to restore a database depend on the backup options you've used and the state of the database. As you know, the backup techniques available are complete, differential, transaction log, and file/filegroups. What you may not know is how to restore a database when these techniques are combined.

Table 11-2 lists some suggested recovery strategies. These strategies show how to recover a database with various combinations of the available backup operations. If you use Enterprise Manager for backup and restore, these procedures are done for you automatically in most cases. The actual step-by-step process is covered later in this chapter.

Table 11-2 Recovery Strategies for Databases

Backup Type

Restore Process

Complete backups only

Restore the database using the last complete backup.

Complete and differential backups

Restore the last complete backup with NORECOVERY. Then restore the last differential backup with RECOVERY.

Complete and transaction log backups

Back up the current transaction log with NO_TRUNCATE. Restore the last complete backup with NORECOVERY. Apply log backups from that time forward in sequence and using NORECOVERY. Apply the last differential backup with RECOVERY option.

Complete, differential, and transaction log backups

Back up the current transaction log with NO_TRUNCATE. Restore the last complete backup with NORECOVERY and transaction log and then the last differential backup with NORECOVERY. Apply log backups from that time forward in sequence and using NORECOVERY. Apply the last backup using the RECOVERY option.

All right, now you know in theory how to restore a database. But before you pull out all the stops, you should make sure the database is really corrupt and can't be recovered by other means. To troubleshoot database problems and potential corruption, complete the following steps:

  1. Start with the error logs. See what types of error messages are in the logs, paying particular attention to errors that occur during database startup. Also take a look at user-related errors. If you find errors, you can look up the error numbers in the SQL Server Books Online or the Microsoft Online Support Web site (https://search.support.microsoft.com). You access the server logs through the Management folder in Enterprise Manager.

    Check the state of the database. Every time you start SQL Server, it goes through a recovery process on each database. If the recovery process has problems, the mode or state of the database may be abnormal. To check mode or state, use these properties of the databaseproperty function:

    • IsShutDown If set to 1, the database is shut down because of problems during startup.

    • IsEmergencyMode If set to 1, the database is in emergency mode, which allows a suspect database to be used.

    • IsSuspect If set to 1, the database is suspect, which means there is possible corruption.

    • IsInLoad If set to 1, the database is going through the loading process.

    • IsInRecovery If set to 1, the database is going through the recovery process.

  2. If possible, try to use the DBCC command to further troubleshoot or repair the database. DBCC is covered in Chapter 12, "Database Automation and Maintenance."

  3. If these procedures indicate a corrupt database that can't be repaired, restore the database from backup.

You can use the databaseproperty function as shown in Sample 11-5.

Sample 11-5 The databaseproperty Function Syntax and Usage

Syntax

databaseproperty('database','property')

Usage

select databaseproperty('Customer','IsEmergencyMode')

Restoring a Database from a Normal Backup

Enterprise Manager tracks all the backups you create for each database; when you need to restore a database, Enterprise Manager automatically configures the restore. You can restore a database using these default settings or fine-tune the restore operation as necessary.

To restore a database, complete the following steps:

  1. If you're using transaction logs and the database is still running, you should back up the current transaction log with NO_TRUNCATE. When you're using the SQL Server Backup dialog box, this means you should cancel the option labeled Remove Inactive Entries From Transaction Log before running the backup.

  2. You must have exclusive access to the database. Close all active connections to the database or have users log off, or both.

  3. In Enterprise Manager, select the server you want to work with and then, from the Tools menu, choose the Restore Database option. This opens the Restore Database dialog box.

  4. Use the Restore As Database drop-down list box to specify which database to restore and then select the Database option button.

  5. Select the database again using the Show Backups Of Database option. This option is provided to allow you to restore a database to a different location, as described in the section of this chapter entitled "Restoring a Database to a Different Location."

  6. Use the First Backup To Restore option to select which backup you want to apply first. Only complete backups are available in this drop-down list box. By default, the last complete backup should be selected.

    Real World Normally, you'll want to start with the last complete backup. However, if you know that the last backup is bad or contains transactions that you don't want to apply, such as a massive table deletion, go back to a previous complete backup by selecting a different backup as the starting point.

  7. If multiple backups are available, you may be able to select a point in time for the restore. For example, if you know that GOTEAM accidentally deleted the Accounts table at 12:16 P.M., you could restore the database to a point just prior to this transaction, such as 12:15 P.M. To use the point in time option, select the Point In Time Restore check box. This opens the Point In Time Restore dialog box shown in Figure 11-4. Select a date and time using the fields provided and then click OK.

    The lower portion of the Restore Database dialog box provides a backup history for the selected database. You can use the information in the history as follows:

    • Restore Allows you to select which backup sets to restore. Default selections are based on the first backup to restore and go forward in time through differential and transaction log backups. You should rarely change the default selections.

    • Type Icons in this field are just about the only indicators of the type of backup. A backup device with a yellow database symbol behind it indicates a complete backup. A backup device with a bluish-green database symbol behind it indicates a differential backup. A backup device with a notebook symbol behind it indicates a transaction log.

    • Backup Set Date Shows a date and time stamp for the backup entry.

    • Size Shows the size of the backup.

    • Restore From Shows the location of the related backup file.

    • Backup Set Name Shows the name of the backup set.

    Tip Select an entry and then click Properties to see a more detailed summary of the backup.

    Cc917637.ppc1104(en-us,TechNet.10).gif

    Figure 11-4: Optionally, use the Point In Time Restore dialog box to select a point in time for the database restore operation.

    Click the Options tab to configure options for the restore operation. The Options tab is shown in Figure 11-5. You use the available options as follows:

    • Eject Tapes (If Any) After Restoring Each Backup Automatically ejects tapes from the tape drive when a restore completes. This is a time-saver if you have to deal with lots of tapes.

    • Prompt Before Restoring Each Backup Automatically prompts after completing a successful restore and before starting the next restore. The prompt includes a Cancel button, which is useful to cancel the restore operation after a particular backup is restored.

    • Force Restore Over Existing Database Automatically overwrites existing database files.

    • Restore Database Files As Allows you to change the restore location for database files.

    • Leave Database Operational Completes the entire restore process and applies all the selected backups, which can include a complete backup, a differential backup, and multiple transaction log backups. All completed transactions are applied, and any uncompleted transactions are rolled back. When the restore process is completed, the database is returned to ready status and you can use it for normal operations.

      Cc917637.ppc1105(en-us,TechNet.10).gif

      Figure 11-5: Use the Options tab to configure options for the restore operation, as necessary.

    • Leave Database Nonoperational Essentially a manual restore that allows you to go step-by-step through the backups. SQL Server completes the entire restore process and applies all the selected backups, which can include a complete backup, a differential backup, and multiple transaction log backups. When the restore is completed, the database isn't returned to ready status and you can't use it for normal operations. All transactions haven't been processed, and the database is waiting for you to apply additional transaction logs. Apply these transaction logs using this mode, and then for the last transaction log set the mode to Leave Database Operational. All completed transactions are then applied, and any uncompleted transactions are rolled back.

    • Leave Database Read-Only Essentially the same as Leave Database Nonoperational, with some exceptions. When the restore process ends, the database is in Read-Only mode and ready for additional transaction logs to be applied. In Read-Only mode, you can check the data and test the database. If necessary, apply additional transaction logs. For the last transaction log, set the mode to Leave Database Operational. All completed transactions are then applied, and any uncompleted transactions are rolled back.

    Tip With Leave Database Read-Only, SQL Server also creates an Undo file, which you can use to undo the restore operation. To commit the restore operations and the final transactions, if possible, without restoring another transaction log, you could use

    • RESTORE DATABASE Customer

    • WITH RECOVERY

    This commits final transactions (if possible), deletes the Undo file, and puts the database back in operations mode. Although you may want to use WITH RECOVERY at this stage, you probably don't want to use WITH NORECOVERY because you'll undo all the changes from the restore and may end up with an empty database.

  8. When you're ready to start the restore operation, click OK. Restore Database shows the progress of each backup. Stop the restore at any time by clicking Stop. If an error occurs, you'll see a prompt with an error message. The most common error is Database In Use. This error occurs when you don't have exclusive access to the database, which means that someone is still connected to the database, and you can't proceed until the connection is closed.

Restoring Files and Filegroups

You can restore files and filegroups from database backups or file backups either individually, in combination with each other, or all together. If any changes were made to the files or filegroups, you must also restore all transaction log backups that were created after the files or filegroups were backed up.

Although you can usually recover individual files or filegroups, there are exceptions. If tables and indexes are created that span multiple filegroups, all the related filegroups must be restored together. Don't worry, SQL Server generates an error prior to starting the restore if a needed filegroup is missing. Further, if the entire database is corrupted, you must restore all files and filegroups in the database. In both cases you must also apply transaction log backups created after the file or filegroup backups you're restoring.

To restore files or filegroups, complete the following steps:

  1. You must have exclusive access to the database. Close all active connections to the database or have users log off, or both.

  2. In Enterprise Manager, select the server you want to work with and then, from the Tools menu, choose the Restore Database option.

  3. Use the Restore As Database drop-down list box to specify which database to restore and then select the Filegroups Or Files option button. This changes the Restore Database dialog box, as shown in Figure 11-6.

    Cc917637.ppc1106(en-us,TechNet.10).gif

    Figure 11-6: When you restore from files or filegroups, you have different options than with a normal backup.

  4. Select the database you're restoring again using the Show Backups Of Database drop-down list box. This option is provided to allow you to restore a database to a different location, as described in the section of this chapter entitled "Restoring a Database to a Different Location."

    If you want to specify criteria for determining which files to use in the backup, choose Select A Subset Of Backup Sets and then click Selection Criteria. This opens the Filter Backup Sets dialog box, shown in Figure 11-7. The options of this dialog box determine which of the available backups are selected, and you use them as follows:

    • Only The Backup Sets Of The Data Files On Drive Choose this option to restore only data files that are on a specific drive, such as F.

    • Only The Backup Sets Completed After Choose this option to filter backup sets by creation date and time.

    • Only Backup Sets Of The Following Filegroups And Files Choose this option to filter restore by filegroup and file.

    The lower portion of the Restore Database dialog box provides a backup history for the files and filegroups in the selected database. By setting selection criteria, you remove options from the history and narrow the choices based on your filters. You can use the information in the history as follows:

    • Restore Allows you to select which backup files to restore. No default selections are made and you must choose the files manually.

      Cc917637.ppc1107(en-us,TechNet.10).gif

      Figure 11-7: Use the Filter Backup Sets dialog box to filter the available backup sets by drive, creation date, or filename.

    • Type Icons in this field indicate the type of backup. A backup device with a folder symbol behind it indicates a file backup. A backup device with a notebook symbol behind it indicates a transaction log.

    • Backup Date Shows a date and time stamp for the backup entry.

    • Size Shows the size of the backup.

    • File Logical Name Shows the logical name of the file.

    • File Physical Name Shows the complete file path for the backup entry.

    Tip Select an entry and then click Properties to see a more detailed summary of the backup.

  5. Select the backup files you want to restore. When you do this, Restore Database automatically selects related transaction logs that must be restored with this file.

  6. Click the Options tab to configure options for the restore operation. The available options are the same as those discussed in the section of this chapter entitled "Restoring a Database from a Normal Backup."

  7. When you're ready to start the restore operation, click OK. Restore Database shows the progress of each backup. You can stop the restore at any time by clicking Stop.

Restoring a Database from a Device

Restoring a database from a tape device or other backup device is different from a normal backup. This is primarily because you have to work with backup media (tapes) that may contain multiple backups, as well as multiple back- up media sets (tape sets). Although you can use this procedure to restore a disk backup device, the normal backup procedure is especially designed for this purpose and is much easier to use.

To restore a database from a device, complete the following steps:

  1. You must have exclusive access to the database. Close all active connections to the database or have users log off, or both.

  2. In Enterprise Manager, select the server you want to work with and then, from the Tools menu, choose the Restore Database option.

  3. Use the Restore As Database drop-down list box to specify which database to restore and then select the From Device option button. This changes the Restore Database dialog box, as shown in Figure 11-8.

    Cc917637.ppc1108(en-us,TechNet.10).gif

    Figure 11-8: When you restore a database from a device, you have additional options for selecting devices and accessing media.

  4. Click Select Devices to display the Choose Restore Devices dialog box, which is shown in Figure 11-9.

  5. To restore from disk, select the Disk option button. Otherwise select the Tape option button.

  6. Click Add to choose the file or backup device for the restore operation. With multiple backup devices, repeat this step to select additional files or backup devices.

  7. When restoring from tape, you can ensure that you're backing up from the right tapes by selecting Only Restore From Media With The Following Name and then typing the name of the backup set in the Media Name field.

  8. In the Backup Set field, type the number of the backup set you're restoring. If you're restoring the first backup set on the tape, type 1. For the second backup set type 2, and so on. To make sure that you've set the right backup, click View Contents and double-check the information provided.

    To restore the backup set, select the Restore Backup Set option button, and then specify the type of backup you're restoring using these option buttons:

    • Database – Complete

    • Database – Differential

    • Transaction Log

    • File Or Filegroup

    Cc917637.ppc1109(en-us,TechNet.10).gif

    Figure 11-9: In the Choose Restore Devices dialog box, choose the restore devices and specify media criteria.

  9. To read the backup information and add to backup history rather than perform a restore, select Read Backup Set Information And Add To Backup History.

  10. Click the Options tab to configure options for the restore operation. The available options are almost the same as those discussed in the "Restoring a Database from a Normal Backup" section of this chapter. You also have the option of reading from the tape to ensure that you have the right backup set. To do this, click Read From Media.

  11. When you're ready to start the restore operation, click OK. Restore Database shows the progress of each backup. You can stop the restore at any time by clicking Stop.

Restoring a Database to a Different Location

When you restore a database to a different location, you're essentially copying the database from the backups. If you use this procedure to copy a database to a new location on the same computer, you create a copy of the database that can have separate files and a different database name. Restoring a database to a different location is similar to the discussion in the previous section. The key differences are as follows:

  1. In the Restore As field, type a new name for the database. For example, if you're restoring the Customer database to a new location, name the copy Customer 2 or CustomerCopy.

  2. When you access the Options tab, you override the default destination paths and enter new destination paths for all of the files you're restoring. Simply click in the Restore As or Move To Physical File Name field associated with a backup and then enter a new file path.

If you use this procedure to copy a database to a different computer, you can create a working copy of the database on another server. You don't need to create a new database or perform any preliminary work. The exception is that if you want to use backup devices on the destination server, you should set these up beforehand. Also, before you begin the restore, you should ensure that the destination computer is using the same code page, sort order, Unicode collation, and Unicode locale as the source server. If these configuration settings aren't identical, you won't be able to run the database on the destination server.

Recovering Missing Data

If you suspect part of the database is missing or corrupted, you can perform a partial restore to a new location so that you can recover the missing or corrupted data. To do this, you use the PARTIAL option with the RESTORE DATABASE statement. You can restore partial databases only at the filegroup level. The primary file and filegroup are always restored along with the files that you specify and their corresponding filegroups. Files and filegroups that aren't restored are marked as offline and you can't access them.

To carry out the restore and recovery process, complete the following steps:

  1. Perform a partial database restore. Give the database a new name and location in the RESTORE DATABASE statement and use MOVE/TO to move the original database source files to new locations, such as:

RESTORE DATABASE new_custdb_partial FILEGROUP = 'Customers2' FROM DISK='g:\cust.dmp' WITH FILE=1,NORECOVERY,PARTIAL, MOVE 'cust' TO 'g:\cu2.pri', MOVE 'cust_log' TO 'g:\cu2.log', MOVE 'cust_data_2' TO 'g:\cu2.dat2' GO

  1. Extract any needed data from the partial restore and insert it into the database from which it was deleted.

Creating Standby Servers

The notion of restoring a backup to a different computer can be extended to create a standby backup server that you can bring online if the primary server fails. When you create a standby you have two options. You can

  • Create a cold standby that you synchronize manually

  • Create a warm standby that SQL Server synchronizes automatically

Creating a Cold Standby

To create a standby that you synchronize manually, complete the following steps:

  1. Install SQL Server on a new server system using an identical configuration. This means that the destination server should use the same code page, sort order, Unicode collation, and Unicode locale as the source server.

  2. Copy all of the databases on the primary server to this new system by specifying a different restore location in the Restore Database dialog box.

  3. Maintain the copies of the databases by periodically applying the transaction log backups from the primary to the standby.

  4. You may want to leave the standby server in Standby mode so that the database is read-only. This allows users to access the database but not make changes.

If one or more databases on the primary server fails for any reason, you can make the corresponding databases on the standby available to users. However, before you do this you should synchronize the primary and the standby by completing the following steps:

  1. On the standby server, apply any transaction log backups created on the primary server that haven't been applied yet. You must apply these backups in the proper time sequence.

  2. Create a backup of the active transaction log on the primary server and apply this backup to the database on the standby server. This ensures up-to-the-minute synchronization. Be sure to recover the database or specify that the database should be put in operational mode after this backup is applied.

    Tip If you need to make the standby appear to be the primary, you may need to take the primary off the network and rename it. Then rename the standby so that it appears to be the primary.

  3. After you restore the primary server to working condition, any changes to the standby's databases need to be restored to the primary server. Otherwise those changes are lost when you start using the primary server again.

Note: Standby servers aren't the same as a SQL Server failover cluster, which is created using the SQL Server Failover Cluster Wizard and Windows 2000 Microsoft Cluster Service. Standby servers store a second copy of databases on their hard disk drives. Virtual servers use a single copy of databases that is accessed from a shared storage device.

Creating a Warm Standby

SQL Server 2000 Enterprise Edition includes a feature called log shipping. You can use log shipping to create a standby server that's automatically synchronized with the primary server. To do this, follow these steps:

  1. Install SQL Server on a new server system using an identical configuration. This means that the destination server should use the same code page, sort order, Unicode collation, and Unicode locale as the source server.

  2. Copy all of the databases on the primary server to this new system by specifying a different restore location in the Restore Database dialog box.

  3. On the primary server, create a database maintenance plan for log shipping as described in the section of Chapter 12 entitled "Creating Maintenance Plans for Log Shipping."

The primary server is referred to as the source server. The servers receiving the logs are referred to as destination servers. After configuring log shipping, you should check the status of log shipping on the source and destination servers periodically.

If one or more databases on the primary server fail for any reason, you can make the corresponding databases on the standby available to users. To do that, follow these steps:

  1. Make sure that the most recent logs have been applied by checking the status of log shipping on the destination server.

  2. Take the primary off the network and rename it.

  3. Rename the standby so that it appears to be the primary.

  4. Check connections to the new primary server.

After you restore the primary server to working condition, any changes to the standby's databases need to be restored to the primary server. Otherwise those changes are lost when you start using the primary server again.

Using Transact-SQL Restore

You can also restore databases using Transact-SQL. The commands you'll use are RESTORE DATABASE and RESTORE LOG. You can use RESTORE DATABASE to restore an entire database, specific files and filegroups, or part of a corrupted database. Sample 11-6 shows the syntax and usage for a complete restore. The option WITH RECOVERY is the default mode.

Sample 11-6 RESTORE DATABASE Syntax and Usage for a Complete Restore

Syntax

RESTORE DATABASE {database_name | @database_name_var}
[FROM <backup_device> [,...n]]
[WITH
   [RESTRICTED_USER]
   [[,] FILE = file_number]
   [[,] PASSWORD = {password | @password_variable}]
   [[,] MEDIANAME = {media_name | @media_name_variable}]
   [[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
   [[,] MOVE 'logical_file_name' TO 'operating_system_file_name']
      [,...n]
   [[,] KEEP_REPLICATION]
   [[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
   [[,] {NOREWIND | REWIND}]
   [[,] {NOUNLOAD | UNLOAD}]
   [[,] REPLACE]
   [[,] RESTART]
   [[,] STATS [= percentage]]
]

Usage

RESTORE DATABASE Customer
   FROM Customer_1
   WITH NORECOVERY
RESTORE DATABASE Customer
   FROM Customer_1
   WITH FILE = 2

Usage

RESTORE DATABASE Customer
   FROM TAPE = '\\.\tape0'

Usage

RESTORE DATABASE Customer
   FROM Customer_1
   WITH NORECOVERY,
      MOVE 'CustomerData1' TO 'F:\mssql7\data\NewCust.mdf',
      MOVE 'CustomerLog1' TO 'F:\mssql7\data\NewCust.ldf'
RESTORE LOG Customer
   FROM CustomerLog1
   WITH RECOVERY

With RESTORE DATABASE you can also restore files and filegroups. The related syntax and usage is shown in Sample 11-7.

Sample 11-7 RESTORE DATABASE Syntax and Usage for File and Filegroup Restore

Syntax

RESTORE DATABASE {database_name | @database_name_var}
   <file_or_filegroup> [,...n]
[FROM <backup_device> [,...n]]
[WITH
   [RESTRICTED_USER]
   [[,] FILE = file_number]
   [[,] PASSWORD = {password | @password_variable}]
   [[,] MEDIANAME = {media_name | @media_name_variable}]
   [[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
   [[,] NORECOVERY]
   [[,] {NOREWIND | REWIND}]
   [[,] {NOUNLOAD | UNLOAD}]
   [[,] REPLACE]
   [[,] RESTART]
   [[,] STATS [= percentage]]
]

Usage

RESTORE DATABASE Customer
   FILE = 'Customerdata_1',
   FILE = 'Customerdata_2',
   FILEGROUP = 'Primary
   FROM Customer_1
   WITH NORECOVERY
RESTORE LOG Customer
   FROM CustomerLog1

Sample 11-8 shows the syntax for performing a partial restore. This command creates a new database that's based on a partial copy of the backup data. With this procedure, the database_name represents the new name for the database and MOVE/TO is used to move the original database source files to new locations.

Sample 11-8 RESTORE DATABASE Syntax and Usage for Partial Restore

Syntax

RESTORE DATABASE { database_name | @database_name_var }
   < file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
   [ RESTRICTED_USER ]
   { [ , ] PARTIAL }
   [ [ , ] FILE = file_number ]
   [ [ , ] PASSWORD = { password | @password_variable } ]
   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
   [ [ , ] MEDIAPASSWORD =       { mediapassword | @mediapassword_variable } ]
   [ [ , ] MOVE 'logical_file_name' TO operating_system_file_name' ]
      [ ,...n ]
   [ [ , ] NORECOVERY ]
   [ [ , ] { NOREWIND | REWIND } ]
   [ [ , ] { NOUNLOAD | UNLOAD } ]
   [ [ , ] REPLACE ]
   [ [ , ] RESTART ]
   [ [ , ] STATS [= percentage ] ]
]

Usage

RESTORE DATABASE cust_part
   FILEGROUP = 'Customers2'
   FROM DISK='g:\cust.dmp'
   WITH FILE=1,NORECOVERY,PARTIAL,
   MOVE 'cust' TO 'g:\cu2.pri',
   MOVE 'cust_log' TO 'g:\cu2.log',
   MOVE 'cust_data_2' TO 'g:\cu2.dat2'
GO
RESTORE LOG cust_part
   FROM DISK = 'g:\cust.dmp'
   WITH FILE = 2,RECOVERY
GO

Sample 11-9 shows how you can use RESTORE LOG.

Sample 11-9 RESTORE LOG Syntax and Usage

Syntax

RESTORE LOG {database_name | @database_name_var}
[FROM <backup_device> [,...n]]
[WITH
   [RESTRICTED_USER]
   [[,] FILE = file_number]
   [[,] PASSWORD = {password | @password_variable}]
   [[,] MEDIANAME = {media_name | @media_name_variable}]
   [[,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
   [[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
   [[,] {NOREWIND | REWIND}]
   [[,] {NOUNLOAD | UNLOAD}]
   [[,] RESTART]
   [[,] STATS [= percentage]]
   [[,] STOPAT = {date_time | @date_time_var}
      | [[,] STOPATMARK = 'mark_name' AFTER date_time]
      | [[,] STOPBEFOREMARK = 'mark_name' AFTER date_time]
]

Usage

RESTORE DATABASE Customer
   FROM Customer_1, Customer_2
   WITH NORECOVERY
RESTORE LOG Customer
   FROM CustomerLog1
   WITH NORECOVERY
RESTORE LOG Customer
   FROM CustomerLog2
   WITH RECOVERY, STOPAT = 'Dec 11, 1999 3:30 PM'

Restoring the Master Database

The master database is the most important database on SQL Server. This database stores information about all the databases on the server, server configuration, server logons, and other important information. If master gets corrupted, operations on the server may grind to a halt, and you'll have to recover master using one of two techniques.

If you can start SQL Server, you can restore master from backup much like you would any other database. To do this, complete the following steps:

  1. You can only back up master using a complete backup. As a result, no differential or transaction log backups will be available. This means you may not be able to restore master exactly as it was before the failure, and that you should normally use the Recovery Complete state of Leave Database Operation.

  2. When you finish restoring the master database, you may need to manually apply any changes made since the last complete backup.

  3. After you check the server and verify that everything is okay, make a complete backup of master.

If you can't start SQL Server and you know master is the cause of the problem, you can restore master by completing the following steps:

  1. Run the Rebuild Master utility discussed in Chapter 2.

  2. Once you rebuild master and get SQL Server back online, you can restore the last backup of master in order to return the server to its most current state.

  3. Because Rebuild Master rebuilds the msdb and model databases, you may need to restore these databases from backup as well.

  4. Recreate any backup devices, if necessary.

  5. Reenter logons and other security settings, if necessary.

  6. Restore distribution, if necessary.

  7. Restore or attach user databases, if necessary.

  8. Restore other server configuration settings, if necessary.

As you can see from the step-by-step procedure, restoring master can take a lot of time and work, which is why regularly backing up master is so important. When you finish recovering the server, be sure to make a complete backup of the master database.

Link
Click to order