Chapter 3 - Backing Up and Restoring Databases

The backup and restore component of Microsoft SQL Server provides an important safeguard for protecting critical data stored in SQL Server databases. Backing up and restoring a database allows for the complete restoration of data over a wide range of potential system problems:

• Media failure 

  If one or more of the disk drives holding a database fail, you are faced with a complete loss of data unless you can restore an earlier copy of the data. 

• User errors 

  If a user or application either unintentionally or maliciously makes a large number of invalid modifications to data, the best way to deal with the problem may be to restore the data to a point in time before the modifications were made. 

• Permanent loss of a server 

  If a server is disabled permanently, or a site is lost to a natural disaster, you may need to activate a warm standby server or restore a copy of a database to another server. 

Additionally, backing up and restoring databases is useful for nonsystem problems, such as moving or copying a database from one server to another. By backing up a database from one computer, and restoring the database to another, a copy of a database can be made quickly and easily.

Backing Up a Database 

Backing up a database makes a copy of a database, which can be used to restore the database if it is lost. Backing up a database copies everything in the database, including any needed portions of the transaction log.

The transaction log is a serial record of all the modifications that have occurred in a database, and which transaction performed each modification. The transaction log is used during recovery operations to roll forward completed transactions, and roll back (undo) uncompleted transactions. For more information, see "Transaction Logs" in Microsoft SQL Server Database Developer's Companion.

Backing up a transaction log backs up only the changes that have occurred in the transaction log since the transaction log was last backed up.

A backup operates like a fuzzy snapshot taken of a database or transaction log:

• A database backup records the complete state of the data in the database at the time the backup operation completes. 

• A transaction log backup records the state of the transaction log at the time the backup operation starts. 

Restoring a Database 

Restoring a database backup returns the database to the same state it was in when the backup was created. Any incomplete transactions in the database backup, (transactions that were not complete when the backup operation completed originally), are rolled back to ensure the database remains consistent.

Restoring a transaction log backup reapplies all completed transactions that are in the transaction log to the database. When applying a transaction log backup, SQL Server reads forward through the transaction log, rolling forward all the transactions on the transaction log. When SQL Server reaches the end of the transaction log, it has re-created the exact state of the database at the time the backup operation started. The restore operation then rolls back all transactions that were incomplete when the backup operation started.

Note Backing up a database does not back up full-text index data in full-text catalogs. However, if full-text indexes have been defined for tables, the metadata for the full-text index definitions are stored in the system tables in the database containing the full-text indexes. Therefore, the metadata for the full-text indexes are backed up when a database backup is created. After a database backup is restored, the full-text index catalogs can be re-created and repopulated. For more information, see "Full-text Indexes" in Microsoft SQL Server Database Developer's Companion.

See Also 

In This Volume 

Copying Databases to Other Servers

In Other Volumes 

"Backup/Restore Architecture" in Microsoft SQL Server Introduction 

"Databases" in Microsoft SQL Server Database Developer's Companion 

"Transactions" in Microsoft SQL Server Database Developer's Companion 

Planning to Back Up and Restore Databases

Backup and restore operations must be planned together. The procedure you implement will be driven mostly by your requirements for data availability. When you identify your data availability requirements, you can assess what backup procedures will give you the types of backups you must have to support the needed restore operations.

When planning backup and restore procedures:

• Think of the types of problems that might possibly affect your system. 

• Plan a restore procedure for each problem. 

  These procedures should take into account the importance of the data. For critical data, you need to recover to the point of failure. For other data, you may be able to rely on restoring the last database backup. If you must get the database back online in a short time interval, you may need to have more frequent backups available, or consider using a warm standby server. 

• Plan backup procedures that support all of these restores. 

  You may have to modify the restore requirements if testing shows that making the needed backups would take more time than is available in production. 

• Run tests to verify that the procedures work within the resource and time limits available. 

  The type of restore procedures chosen is influenced by the amount of time the data can be unavailable and by the number of transactions that can be tolerated to be lost. If there are strict service level agreements (SLA) or guidelines that specify how long data can be unavailable, the restore process must operate well within the time limits. 

Running tests to verify that the procedures work is perhaps the most important step. It is difficult to plan a restore procedure in the tense times that accompany a server outage. Also, when a server fails, your recovery options are limited by the number and types of backups available. If you have not planned ahead and tested your procedures, the types of backups needed to do the restore options may not be available. Additionally, testing the restore procedures should verify that the data availability constraints are met (amount of time data can be unavailable), and the number of permissible lost transactions is not exceeded.

When analyzing backup procedures, remember that your backup is used to facilitate recovery, and a common recovery goal is to restore the database to a consistent state with no data loss, as quickly as possible. The time taken to recover a database and minimize the amount of lost data is largely dependent on the amount of time between backups. The optimal amount of time between backups is the time interval that results in the best balance of two opposing requirements:

• Minimizing the time needed to recover the database. 

• Implementing backup procedures that can run within the time and resource constraints and prevent undue effects on production work. 

It is recommended that your backup plan include:

• A tracking and management plan for storing and recycling backup sets. 

• A schedule for overwriting backup media. 

• A decision to use either a centralized or distributed backup approach in a multiserver environment.

• A set of conditions for using attended or unattended backups. 

• A decision to store backup sets on or offsite.

• A standard and disaster recovery plan to restore your database to a production-ready state. 

The Database Maintenance Plan Wizard can help you create and automate a backup plan.

See Also 

In Other Volumes 

"Database Maintenance Plan Wizard" in Microsoft SQL Server Database Developer's Companion 

Analyzing Restore Requirements

After answering these questions, you should be able to develop a restore plan and a set of procedures to address both optimal system operations and extraordinary disaster situations.

When designing a restore plan, consider the following:

• Should restore operations occur at the end of the day, on a weekend, or over a long holiday? How will this decision affect the recovery time or the number of transactions that could be lost due to failure? 

• Should backup media be stored onsite or offsite? How will this decision affect the time taken to recover from failure? 

• The effect of losing a backup set or backup media, such as a tape? Are other recovery procedures in place to eliminate or minimize the effects of this happening? 

• How do you recover from data loss without damaging your enterprise financially? 

• How long does it take to restore the full database and apply any applicable transaction logs? 

• What will happen to your enterprise if users cannot access data for one hour, one day, a week, or longer? 

• Does a system administrator need to be onsite during the restore operations? 

• Are your system operators trained to perform the restore operations? 

• Who makes the decisions about the type of restore operation to use? 

Analyzing Backup Requirements

When developing your backup plan, answer the following questions. Your answers should provide guidance to the best Microsoft SQL Server backup methods, backup device, and backup media to use in your environment.

• How large is each database? 

• How often does the data change in the database? 

• Are some tables modified more often than others? 

• If data is lost, how vital is your database to the daily operation of the enterprise? 

• How important is it to never lose a change? 

• How easy would it be to re-create lost data? 

• What are your critical database production periods?

• When does the database experience heavy use, resulting in frequent inserts and updates? 

• Is your database used heavily during payroll processing, one time quarterly, on specific days, or during certain hours of the day? 

• Will users access the database during backup operations? 

• How long can you go between transaction log truncations? 

• Do you need to keep a rotating series of backups (for example, backup, backup+1, backup+2)?

  This allows you to restore some data if a backup is lost, or lets you restore further back in time if a problem is not detected for some time after it occurs. 

• Is your SQL Server in a cluster? 

• Is your SQL Server system in a multiserver environment with centralized administration? 

The time it takes to run backup and restore operations depends partly on the speed of the physical devices (tape or disk) used to store the backups. Backup device vendors can supply performance information to help calculate the approximate time required to back up an entire database. However, the best method for determining the time it takes to run backup and restore operations is to perform realistic tests on the production, or a duplicate, system. For example:

• Create a database backup of the real system (or a copy of the real system) and time the operation. 

• Create a transaction log backup, after typical transactions that occur in production have been performed, and time the operation. 

• Restore a database backup and time the operation. 

• Apply the transaction log backup and time the operation. 

See Also 

In This Volume 

Disk, Tape, and Named Pipe Devices

Choosing a Backup and Restore Strategy

For each database, you must choose one overall backup and restore strategy: backing up the database only, or backing up the database and the transaction log. Additionally, each backup strategy can be augmented by the use of differential database backups to increase the speed of the restore process.

Choosing whether to back up the database only, or the database and the transaction log, is an important decision because:

• If you choose to back up the database only, you will lose all committed transactions that have occurred after your most recent database backup. 

  The primary advantage of using only database backups is simplicity. Backing up is a single operation, usually scheduled at regular intervals. Should a restore be necessary, it can be accomplished easily in one step. 

• If you choose to back up the database and the transaction log, you will be able to recover all committed transactions up to the point of failure; only uncommitted work will be lost, unless the active transaction log is also lost, for example due to hardware failure on the disk containing the transaction log files. To prevent loss of the active transaction log, place the transaction log files on mirrored disks. 

  Particularly for production databases, it is important not to lose work in the event of a failure. Transaction log backups provide the information necessary to redo changes made after a database backup was performed. Transaction log backups also make possible optimizations and features important to larger, production databases, such as reducing the time to back up and recover the database and allowing the database to be returned to its state at an earlier point in time. Transaction log backups are usually scheduled more frequently than database backups. 

Database Backups

When creating database backups, but no separate transaction log backups, the only recovery procedure necessary is to restore the last backup of the database. This re-creates the database as it was when the backup operation completed. However, there is no way to recover any modifications made to the database after the most recent database backup completed.

Some of the characteristics of a system for which you would consider using database backups only include:

• The importance of the data is low enough that losing any modifications made after the last backup can be tolerated because it is more efficient to endure the loss and possibly re-create the data manually rather than use transaction log backups. For example, a test database used in the development of a database application can tolerate lost data because the data is not vital and can be re-created easily. 

• You can re-create the data, for example, from batch loads that take place nightly and replace the contents of most of the database. 

• You need to implement simple maintenance procedures, for example, because you do not have a database administrator.

  Maintenance procedures can be simplified further using the Database Maintenance Plan Wizard. 

• Database changes are infrequent, such as a read-only database. 

Backing up a database backs up all the data in the database regardless of whether it changed after the last database backup was created. This means that the entire database backup is self-contained and does not rely on any other backup medium to be restored. It also means that the database backup will use more storage space per backup, and consequently need more time to complete the backup operation, compared to using transaction logs and differential database backups.

Note Simple database backups are not an appropriate choice for production systems where loss of recent changes is unacceptable.

Important When creating database backups only, it is recommended that the transaction log is set to be truncated automatically every time a checkpoint occurs in the database by setting the trunc. log on chkpt. database option to TRUE. This helps prevent that transaction log from becoming full, requiring the transaction log to be truncated manually.

See Also 

In This Volume 

Creating and Restoring a Database Backup

In Other Volumes 

"Database Maintenance Plan Wizard" in Microsoft SQL Server Database Developer's Companion 

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion 

"Databases" in Microsoft SQL Server Database Developer's Companion 

Transaction Log Backups

By creating both database and transaction log backups, it is possible to restore a database to the exact point of failure, and minimize, or completely eliminate, loss of data due to the failure.

Some of the characteristics of a system for which you would consider using transaction log backups include:

• Any loss of changes after the last database backup is unacceptable, such as an OLTP financial trading database system.

  It is not acceptable to lose any transactions. 

• The resources required to perform database backups only are limited, such as lack of backup storage space or time available to perform the backup operation.

  For example, a 10-terabyte database would require a lot of disk space and time to back up regularly in its entirety. 

• Returning the database to a point before a failure occurred is desirable.

  For example, you want to return the database to the point in time 10 minutes before a failure occurred, rather than recovering all or nothing. 

• Database changes are frequent.

  A large number of changes to the database occur over a relatively short period of time resulting in the last database backup becoming out of date quickly. Backing up the entire database frequently because of these rapid changes may not be possible due to reasons listed earlier. 

Because transaction log backups generally use fewer resources than database backups, they can be created more frequently than database backups. This reduces the window of time in which a failure could occur, such as the entire server failing, and the last backup operation being performed. This decreases the amount of data that could be potentially lost. Additionally, by applying transaction log backups, you can recover the database to a specific point in time prior to when a problem occurred, if necessary.

Note There can be rare situations where the transaction log backup is larger than a database backup. For example, a database has a high transaction rate or transactions that affect a large proportion of the database causing the transaction log to grow quickly, or the transaction log is backed up infrequently. In these situations, create transaction log backups more frequently.

A recommended process for backing up a database and any transaction log is:

1. Back up the database regularly, such as every night. 

2. Back up the transaction log frequently, such as hourly or more for highly active systems. 

The process for restoring the database and applying the transaction log backups is:

1. Back up the current transaction log if a failure occurs (unless, for example, the disk containing the transaction log files has failed). 

2. Restore the most recent database backup. 

3. Apply all of the transaction log backups in the same sequence in which they were created after the database backup. 

4. Apply the final transaction log backup created in Step 1 to restore the database to the point of failure. 

Therefore, although the use of transaction log backups increases recoverability, creating and applying them is also more complex than simply using database backups. Restoring a database using both database and transaction log backups works only if you have an unbroken sequence of transaction log backups after the last database or differential database backup. If anything happens to break the sequence of transaction log backups, such as nonlogged operations, you should create a database or differential database backup and start backing up the transaction logs again. Any previous transaction log backups are not necessary and can be overwritten unless you want to return the database to a point prior in time included in these transaction log backups. For more information about the conditions required to create a sequence of transaction log backups, see "Creating and Applying Transaction Log Backups" in this volume.

See Also 

In Other Volumes 

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion 

"Transaction Logs" in Microsoft SQL Server Database Developer's Companion 

Differential Database Backups

You can increase the speed of your backup operations (hence reducing the time to create backups) by supplementing the use of database backups with making occasional differential database backups. A differential database backup records only those data changes made to the database after the last full database backup. A differential database backup is smaller and takes less time to complete than a database backup. By creating differential database backups more frequently than database backups, you can decrease the amount of data you risk losing.

Unlike transaction log backups, differential database backups do not allow a database to be restored to the exact point of failure; only to the point in time that the differential database backup was created. Therefore, differential database backups are often supplemented by creating multiple transaction log backups after each differential database backup is created. By using a combination of database, differential database, and transaction log backups, recovery time and the amount of potential data loss due to failure can be minimized.

Some of the characteristics of a system for which you would consider using differential database backups include:

• The importance of the data is low enough that losing any modifications made after the last differential database backup can be tolerated because it is more efficient to endure the loss and possibly re-create the data manually rather than use transaction log backups. 

• You need to implement simpler maintenance procedures than using transaction logs, for example, because you do not have a database administrator. 

• The resources required to perform only database backups are limited, such as lack of backup storage space or time available to perform the backup operation.

  For example, a 10-terabyte database would require a lot of disk space and time to back up regularly in its entirety. 

• Minimizing recovery time and loss of any transactions due to failure by using differential database backups in combination with database and transaction log backups. 

A recommended process for backing up a database and any differential database backups is:

1. Back up the database regularly, such as every night. 

2. Create a differential database backup periodically, such as every four hours or more for highly active systems. 

3. Optionally, create transaction log backups between each differential database backups, such as every 30 minutes. 

The process for restoring the database and differential database backups is:

1. Restore the most recent database backup. 

2. Restore the last differential database backup. 

3. Optionally, apply all transaction log backups created after the last differential database backup was created. 

See Also 

In This Volume 

Creating and Restoring Differential Database Backups

Using Differential Database Backups with Transaction Log Backups

Disaster Recovery Planning

Disaster recovery is the process by which information systems are recovered in the event of a catastrophe: a natural disaster such as a fire, or technical disaster such as a two-disk failure in a RAID-5 array. Disaster recovery planning is the work devoted to preparing all the actions that will occur in response to a catastrophic event. Disaster recovery assessment is the simulation of a catastrophic event and/or the evaluation of the disaster recovery plan's capability to deliver the specified recovery needs. Disaster recovery planning should be considered in light of your own environment, modified accordingly, specified, and verified.

For example, suppose a fire occurs and wipes out your 24-hour data center. Are you certain you can recover? How long will it take you to recover and have your system available? How much data loss can your users tolerate? These should be some of the key concerns of every system administrator and database administrator charged with maintaining invaluable system data.

Ideally, the disaster recovery plan should state how long recovery should take, and the final database state the users can expect. For example, "After the acquisition of specified hardware, recovery should be completed in 48 hours and data will be guaranteed only up to the end of the previous week." It is typically important that management be kept clearly informed of these specifications. Disaster recovery assessment should be able to substantiate the specification.

A disaster recovery plan can be structured many different ways and can contain many types of information (how to get hardware, who is to communicate what, who are the people to be contacted in the event of a disaster, how are they to be contacted, who owns the administration of the plan, and so on).

Preparing for a Disaster 

To prepare for disaster, it is recommended that you perform these steps periodically:

1. Back up all system and user databases. 

2. Maintain system logs in a secure fashion. Keep records of all service packs installed for both Microsoft Windows NT Server and Microsoft SQL Server. Keep records of network libraries used, the security mode, and the sa password. 

3. Maintain a base functionality script for assessing minimal capability quickly. 

   Note A base functionality script is a batch of code that can be used to demonstrate the successful functioning of the database from a specific application's perspective. Most commonly this is an .sql file with batched SQL statements run into the server from osql. For other applications, a .bat file is more appropriate because it can contain bcp and osql commands. This base functionality script is very application-specific, and can take many different forms. For example, on a decision support/reporting system, the script may merely be a copy of a couple of your key reporting queries; for an online transaction processing (OLTP) application it may be the execution of a batch of stored procedures to execute INSERT, UPDATE, and DELETE statements. The goal is to confirm that everything is working as intended. The base functionality script provides a dependable tool for the system administrator or database administrator to be able to see that the database is back in a viable state, without depending on end users for verification. 

4. To minimize the amount of data lost, perform regular database and transaction log backups. Performing a backup operation has minimal effect on running transactions, so backup operations can be run during normal operations. 

5. Assess the following steps in "Recovering from a Disaster" ahead of time on another server, and amend the steps as necessary to suit your environment. 

Recovering from a Disaster 

To recover from a disaster, perform these steps after acquiring suitable replacement hardware:

1. Install Windows NT Server and load the appropriate service pack. Verify that appropriate domain functionality exists. 

   For example, verify that file sharing works properly. 

2. Install SQL Server and load the appropriate service pack. Restore the master and msdb database backups. It will be necessary to shut down the server after restoring the master database. Reconfigure the server for the appropriate network libraries and security mode. 

3. Confirm that SQL Server is running properly by checking the SQL Server Service Manager and the Windows NT application log. If the Windows NT Server name was changed, use sp_dropserver and sp_addserver to match the Windows NT Server name with the SQL Server computer name. 

4. Verify the availability of the system. Run a functionality script to ensure correct operation. 

   Optionally, run DBCC CHECKDB on each database. 

5. Allow users to resume normal usage. 

See Also 

In This Volume 

Planning to Back Up and Restore Databases

Managing Permissions

In Other Volumes 

"sqlservr Application" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Backing Up and Restoring User Databases

Generally, depending on the level of recoverability, speed, size, and simplicity required, the methods most likely to be used for backing up and restoring user databases are:

• Using database backups. 

• Using database and transaction log backups. 

• Using database and differential database backups. 

Backups are created on backup devices, such as disk or tape media. Microsoft SQL Server allows you to determine how you want to create your backups on backup devices, such as overwriting existing backups, or appending new backups to existing backups (thereby creating a history of backups). These options help you implement your backup and restore strategy.

See Also 

In This Volume 

Choosing a Backup and Restore Strategy

Disk, Tape, and Named Pipe Devices

Microsoft SQL Server backs up databases, transaction logs, and files to backup* *devices. Backup devices include disk, tape, and named pipe devices.

Disk Devices 

Disk backup devices are files on hard disks, or other disk storage media, and are the same as regular operating system files. Referring to a disk backup device is the same as referring to any other operating system file. Disk backup devices can be defined on a server's local disk or on a remote disk on a shared network resource, and can be as large or as small as needed. The maximum file size is equivalent to the free disk space available on the disk.

If the backup is to be performed over the network to a disk on a remote computer, use the universal naming convention name (UNC) in the form \\Servername\Sharename\Path\File to specify the location of the file. As with writing files to the local hard disk, the appropriate permissions needed to read or write to the file on the remote disk must be granted to the user account used by SQL Server.

Because backing up data over a network can be subject to error, verify the backup operation after completion.

Important Backing up to a file on the same physical disk as the database is not recommended; if the disk device containing the database fails, there is no way to recover the database because the backup is located on the same failed disk.

Tape Devices 

Tape backup devices are used in the same way as disk devices, with the exception that:

• The tape device must be connected physically to the computer running SQL Server.

  Backing up to remote tape devices is not supported. 

• If a tape backup device is filled during the backup operation, but more data still needs to be written, SQL Server prompts for a new tape and continues the backup operation. 

To back up SQL Server (or Microsoft Windows NT) data to tape, use a tape backup device or tape drive supported by Windows NT. Additionally, use only the recommended tapes for the specific tape drive (as suggested by the drive manufacturer). For more information about installing a tape drive, see your Windows NT documentation.

Named Pipe Devices 

Named pipe backup devices allow third-party vendors a flexible and powerful way to connect their own software and provide specialized backup and restoration capabilities. To use a named pipe backup device, you provide as part of a BACKUP or RESTORE Transact-SQL statement the name of the named pipe that will be used by a client application.

Physical and Logical Devices 

SQL Server identifies backup devices using either a physical or logical device name.

A physical backup device is the name used by the operating system to identify the backup device, for example, C:\Backups\Accounting\Full.bak.

A logical backup device is an alias, or common name, used to identify the physical backup device. The logical device name is stored permanently in the system tables within SQL Server. The advantage of using a logical backup device is that it can be simpler to refer to than a physical device name. For example, a logical device name could be Accounting_Backup, but the physical device would be C:\Backups\Accounting\Full.bak.

When backing up or restoring a database, you can use either physical or logical backup device names interchangeably.

For example, execute the BACKUP statement with either the logical or physical device name:

-- Specify the logical backup device.
BACKUP DATABASE accounting 
TO Accounting_Backup
-- Or, specify the physical backup device.
BACKUP DATABASE accounting 
TO DISK = 'C:\Backups\Accounting\Full.Bak'

See Also 

In This Volume 

SQL Server File Permissions

Using Multiple Media or Devices

In Other Volumes 

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Backup Devices" in Microsoft SQL Server Introduction 

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Backup Media

The backup media is the actual physical storage used by the backup device to store the database, transaction log, or file backup. Backup media can be either disk or tape.

For example, a backup device might be the file C:\Backups\Accounting\Full.bak, whereas the backup medium is the disk containing the file. Similarly for tape, a backup device might be the \\.\TAPE0 tape device on the local computer, whereas the backup media are the physical tapes used to store the backup.

When creating a backup, Microsoft SQL Server can do one of the following:

• Use the media (disk or tape) used by the backup device for the first time. 

• Append the backup to the end of an existing backup on the media. 

• Overwrite existing backups on previously used media. 

See Also 

In This Volume 

Understanding Media Sets and Families

Managing Backups

In Other Volumes 

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Using Media for the First Time

When creating a backup on a tape backup device for the first time, Microsoft SQL Server needs to initialize the backup media before the backup can be created. Initializing (or formatting) the media causes a media header (containing information about the media) to be written and deletes any existing media header, effectively deleting the previous contents of the tape. When initialized, previous information on the tape cannot be retrieved.

Initializing disk media involves only the backup device file(s) specified by the backup operation. Other files on the disk are unaffected. When using backup devices for the first time, SQL Server automatically creates the file(s) needed by the backup device(s) for the backup operation. Reinitializing disk backup devices overwrites the contents of the files used by the backup devices and writes a new media header.

Overwriting Media

By overwriting backups on media, the existing contents of the backup set are overwritten with the new backup, and therefore no longer available. For disk backup media, only the files used by the backup device(s) specified in the backup operation are overwritten; other files on the disk are unaffected. When overwriting backups, the existing media header can be preserved, and the new backup is created as the first backup on the backup device. If there is no existing media header, a valid media header with an associated media name and media description is written automatically. If the existing media header is invalid, the backup operation terminates.

By default, Microsoft SQL Server performs some checks to prevent backup media from being overwritten accidentally (even when you have specified that the media should be initialized). Backup media is not overwritten if either of the following conditions is met: 

• The expiration dates for the existing backups on the media have not expired. 

  The expiration date specifies the date the backup expires and can be overwritten by another backup. You can specify the expiration date when a backup is created. By default, the expiration date is determined by the media retention option set with sp_configure. 

• The media name, if provided, does not match the name on the backup media. 

  The media name is a descriptive name given to identify the media easily.

However, these checks can be explicitly skipped if you are sure you want to overwrite the existing media, for example if you know that the backups on the tape are no longer needed.

Note If the backup media is password protected, SQL Server does not write to the media. To overwrite media that is password protected, you need to reinitialize the media.

See Also 

In Other Volumes 

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Appending Backup Sets to Existing Media

Backups from the same or different databases, performed at different times, can be stored on the same media. Additionally, data other than Microsoft SQL Server data can be stored on the same media, such as Microsoft Windows NT file backups. By appending a new backup set to existing media, the previous contents of the media remain intact, and the new backup is written after the end of the last backup on the media.

Appending can occur only at the end of the media. For example, if a media contains five backup sets, it is not possible to skip the first three backup sets to overwrite the fourth backup set with a new backup set.

By default, SQL Server always appends new backups to media.

Identifying the Backup Set to Restore

Each backup set on media, including foreign backup sets such as Microsoft Windows NT file backups, is numbered. This allows the backup set you want to restore to be referenced easily. For example, the following media contains four backup sets: two Microsoft SQL Server backups and two foreign backup sets (Windows NT files).

To restore a specific backup set, specify the position number of the backup set you want to restore. For example, to restore the second SQL Server backup set, the fourth backup set on the media, specify 4 as the backup set to restore.

See Also 

In This Volume 

Viewing Information About Backups

In Other Volumes 

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Creating and Restoring a Database Backup

When creating a database backup, the backup operation copies only the data in the database to the backup file; it does not copy out unused space in the database. Because the database backup contains only the actual data in the database, not any empty space, the database backup is likely to be smaller than the database itself. An estimate of the size of the database backup can be determined using the sp_spaceused system stored procedure; the reserved value indicates the estimated size.

Important Microsoft SQL Server does not truncate the transaction log when backing up the database. Therefore, when creating database backups only, it is recommended that the transaction log is set to be truncated automatically every time a checkpoint occurs in the database by setting the trunc. log on chkpt. database option to TRUE. This prevents that transaction log from becoming full, requiring the transaction log to be truncated manually.

If you are producing database backups only, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data. Databases that do not contain critical data and have few modifications can be backed up on a weekly or biweekly basis. Data that is more critical or more volatile may need to be backed up daily, or even more frequently. Some databases that are usually read-only may need to be backed up only after a periodic refresh with new data.

It is also prudent to have more than one backup of the database. Have a rotating series of backup media, such that you have two or more versions of the database you can restore. This allows you to address situations where a user may make some incorrect modifications that are not detected for some time, or fall back to an earlier backup if the backup media is damaged.

Note If any files are added or removed from a database, a database backup should be created immediately.

Backup Restrictions 

Using SQL Server, backup operations can occur while the database is online and in use. However, some operations are not allowed during a database backup operation:

• Creating or deleting database files. 

• Creating indexes. 

• Performing nonlogged operations. 

• Shrinking either the database (automatically or manually) or the database files. For more information, see "Shrinking the Database" in Microsoft SQL Server Database Developer's Companion.

If a backup operation is started when one of these operations is in progress, the backup operation terminates. If a backup operation is in progress and one of these operations is attempted, the operation fails and the backup operation continues.

Restoring a Database Backup 

Restoring a database backup returns the database to the same state it was in when the backup was created. When restoring a database, SQL Server re-creates the database and all of its associated files automatically by performing these steps:

1. All of the data from the backup is copied into the database; the rest of the database is created as empty space. 

2. Any incomplete transactions in the database backup, (transactions that were not complete when the backup operation completed originally), are rolled back (undone) to ensure the database remains consistent. 

This process ensures that the restored database is a copy of the database as it existed when the backup operation completed, except that all incomplete transactions have been rolled back. This is required to restore the integrity of the database.

Additionally, to prevent overwriting a database unintentionally, the restore operation can perform a safety check automatically. The restore operation fails if:

• The database named in the restore operation already exists on the server and the database name does not match the database name recorded in the backup set. 

• The database named in the restore operation already exists on the server but it is not the same database as the database contained in the database backup. For example, the database names are the same but each database was created differently. 

• One or more files need to be created automatically by the restore operation (regardless of whether the database already exists) but files with the same file name as those that need to be created already exist. 

These safety checks can be disabled if the intention is to overwrite another database.

Interrupted Backup and Restore Operations 

If a backup or restore operation is interrupted (for example, due to a power loss on the server), it is possible to restart the backup or restore operation from the point it was interrupted. This can be useful if large databases are restored onto other servers as an automated process. If the automated process fails near the end of the restore operation, for example if the power fails, a system administrator can restart the restore operation from where it left off, rather than restoring the whole database from the beginning.

Note Restarting a backup or restore operation can be used only with removable media, such as tapes.

See Also 

In This Volume 

Setting Database Options

In Other Volumes 

"Backing Up Nonlogged Operations" in Microsoft SQL Server Database Developer's Companion 

"Troubleshooting Orphaned Users" in Microsoft SQL Server Diagnostics 

"sp_spaceused" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Creating and Applying Transaction Log Backups

Backing up the transaction log periodically to create a sequence of transaction log backups offers users the most flexibility in restoring databases. By creating transaction log backups, a database can be restored to any point in time contained within the sequence of transaction logs, right up to the point of failure.

When creating a transaction log backup, the starting point of the backup is where:

• The previous transaction log backup ended (if a transaction log backup has been created). 

• The transaction log backed up as part of the most recent database or differential database backup ended, if no previous transaction log backup was created. (A database or differential database backup contains a copy of the active space of the transaction log.) 

Note Creating a backup of all the files and filegroups in a database together with a spanning set of transaction log backups is equivalent to creating a database backup. Therefore, whenever a database backup is required when creating or applying a transaction log backup, a complete set of file or filegroup backups can be used instead. For more information, see "Using File or Filegroup Backups" in this volume.

Truncating the Transaction Log 

When Microsoft SQL Server finishes backing up the transaction log, it truncates the inactive portion of the transaction log. SQL Server then reuses this truncated, unused space in the transaction log rather than the transaction log continuing to grow and use more space. The inactive portion of the transaction log is that part of the transaction log that is no longer used during the recovery process that the database goes through when SQL Server starts, because all transactions in that part of the log are complete. Conversely, the active portion of the transaction log contains transactions that are still running and have not completed yet.

The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of these events:

• The most recent checkpoint.

  This represents the earliest point at which SQL Server would have to roll forward transactions during a restore operation.

• The start of the oldest active transaction; a transaction that has not yet been committed or rolled back.

  This represents the earliest point to which SQL Server would have to roll back transactions during recovery.

• The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.

  This represents the earliest point that SQL Server still has to replicate. 

• The start of the oldest CREATE INDEX operation whose completion log record has not been backed up yet. 

Conditions for Backing Up the Transaction Log 

The transaction log should not be backed up:

• If the trunc. log on chkpt database option is set to TRUE; create a database or differential database backup instead. 

• If any nonlogged operations have occurred in the database since the last database backup was created. Create a database or differential database backup instead. 

• Until a database backup has been created because the transaction log contains the changes made to the database after the last backup was created. 

• If the transaction log has been truncated, unless a database or differential database backup is created after the transaction log truncation occurs.

• If any files are added or deleted from the database; a database backup should be created immediately instead. 

Important When using transaction log backups, do not set the trunc. log on chkpt. database option to TRUE. Setting this option to TRUE causes the transaction log to be truncated, without backing up the truncated part of the transaction log, every time a checkpoint occurs in the database, preventing more transaction log backups from being created.

Using SQL Server, backup operations can occur while the database is online and in use. However, some operations are not allowed when the transaction log is being backed up:

• Creating or deleting database files. 

• Performing nonlogged operations. 

• Shrinking either the database (automatically or manually) or the database files. For more information, see "Shrinking the Database" in Microsoft SQL Server Database Developer's Companion. 

If a backup operation is started when one of these operations is in progress, the backup operation terminates. If a backup operation is in progress and one of these operations is attempted, the operation fails and the backup operation continues.

Backing Up the Active Transaction Log 

It is possible to back up the currently active transaction log even if the database is inaccessible, for example due to media failure, providing both the primary data file and the transaction log files are accessible.

Note By placing the primary data file and transaction log files on different physical disks than the database files containing the user objects, any media failure on the disks containing the user database files affects only those files. Additionally, placing the primary data file and transaction log files on mirrored or RAID 5 disks can further protect the files from isolated media failure. For more information about primary data files, see "Using Files and Filegroups" in Microsoft SQL Server Database Developer's Companion.

This transaction log backup can then be used with the database backup and previous transaction log backups to restore the database to the exact point of failure, although any uncommitted transactions will be rolled back.

Creating a Sequence of Transaction Log Backups 

To create a sequence of transaction log backups, you typically make a database backup at periodic intervals, such as daily, and transaction log backups at shorter intervals, such as hourly. The interval between backups varies with the criticality of the data and the workload of the server. Some sites may only need to back up the database weekly and back up the transaction log daily.

The sequence of transaction log backups is independent of the database backups. You make one sequence of transaction log backups, and then make periodic database backups that are used to start a restore operation. For example, assume the following sequence of events:

8:00 A.M.	Back up database
Noon	Back up transaction log
4:00 P.M.	Back up transaction log
6:00 P.M.	Back up database
8:00 P.M.	Back up transaction log
10:00 P.M.	Failure occurs

The transaction log backup created at 8:00 P.M. contains transaction log records from 4:00 P.M. through 8:00 P.M., spanning the time when the database backup was created at 6:00 P.M. The sequence of transaction log backups is continuous from the initial database backup created at 8:00 A.M. to the last transaction log backup created at 8:00 P.M. Two procedures can be used to restore the database to its state at 10:00 P.M. (point of failure):

The second option points out the redundant security offered by a chain of transaction log backups that can be used to restore a database even if a database backup is lost. You can restore an earlier database backup, and then restore all of the transaction log backups created after the database backup was created.

The only time database or differential database backups must be synchronized with transaction log backups is when starting a sequence of transaction log backups. Every sequence of transaction log backups must be started by a database or differential database backup.

Applying Transaction Log Backups 

It is not possible to apply a transaction log backup:

• Unless the database or differential database backup preceding the transaction log backup is restored first.

• Unless all preceding transaction logs created since the database or differential database was backed up are applied first.

  If a previous transaction log backup is lost or damaged, you can restore only transaction logs up to the last backup before the missing transaction log. 

• If the database has already recovered and all outstanding transactions have been either rolled back or rolled forward.

  When applying transaction log backups, the database must not be recovered until the final transaction log has been applied. If you allow recovery to take place when applying one of the intermediate transaction log backups, you cannot restore past that point without restarting the entire restore operation, starting with the database backup. 

Recovery and Transaction Logs 

When restoring a database backup or applying a transaction log backup, the default is to roll back all uncompleted transactions at the end of the restore operation. This is required to restore the integrity of the database.

Note The operation of rolling back all uncompleted transactions is also called recovering the database.

After this has been done, no more transaction log backups can be applied to the database. For example, a series of transaction log backups contain a long-running transaction where the start of the transaction is recorded in the first transaction log backup, but the end of the transaction is recorded in the second transaction log backup. Because there is no record of a commit or rollback operation in the first transaction log backup, if recovery runs when the first transaction log backup is applied, the long-running transaction would be treated as incomplete, and data modifications recorded in the first transaction log backup for the transaction would be rolled back. SQL Server then does not allow the second transaction log backup to be applied after recovery has run.

Therefore, when applying transaction log backups, the database must not be recovered until the final transaction log has been applied. This prevents any transactions from being partially rolled back. The only time outstanding transactions need to be rolled back is at the end of the last restore operation.

See Also 

In This Volume 

Restoring a Database to a Prior State

Using File or Filegroup Backups

In Other Volumes 

"Transactions" in Microsoft SQL Server Database Developer's Companion 

"Transaction Logs" in Microsoft SQL Server Database Developer's Companion 

Backing Up Nonlogged Operations

Nonlogged operations are generally used to prevent the transaction log from filling rapidly and running out of disk space. However, because not all of the details of these operations are logged in the transaction log, if the transaction log needs to be applied, these operations would not be rolled forward because the transaction log does not have all the data needed to roll them forward.

Using differential database backups is a good way for backing up nonlogged operations because differential database backups record all the changes made to the database since the database backup was created, including nonlogged operations. Therefore, a recommended process for backing up a database after nonlogged operations have occurred is:

1. Back up the database regularly, such as every night. 

2. Create a differential database backup periodically and after every nonlogged operation, such as every four hours or more for highly active systems. 

3. Create transaction log backups between each differential database backup, such as every 30 minutes. 

The process for restoring the database where nonlogged operations have occurred is:

1. Restore the most recent database backup. 

2. Restore the last differential database backup. 

3. Apply all transaction log backups created after the last differential database backup was created. 

Another strategy for handling nonlogged operations is to ensure that all nonlogged operations occur in a separate database, isolating the nonlogged operations from the database containing the potentially more valuable data. For example, a user can create a table named pictures with large image data (for which nonlogged operations are often used to insert the image data) in a database separate from the main production database. The transaction logs in the main production database can be backed up without regard for any nonlogged operations that occur using the pictures table.

See Also 

In This Volume 

Logged and Nonlogged Bulk Copy Operations 

In Other Volumes 

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"WRITETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"UPDATETEXT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Nonlogged Operations" in Microsoft SQL Server Database Developer's Companion 

"Setting Database Options" in Microsoft SQL Server Database Developer's Companion 

Creating and Restoring Differential Database Backups

You can supplement database backups by using differential database backups that record only the changes made to the database after the last database backup. For example, you can create a database backup nightly, and create differential database backups at shorter intervals, such as every four hours.

Each differential database backup records all modifications made to a database after the last database backup, not just the changes made after the last differential database backup. If you create a database backup, followed by multiple differential database backups, you do not have to restore the database backup followed by all of the differential database backups; you only have to restore the database backup and the last differential database backup created.

For example, assume you have the following database and differential database backups:

Midnight (Tuesday)	Database backup
6:00 A.M. (Wednesday)	Differential database backup
Noon	Differential database backup
6:00 P.M.	Differential database backup
Midnight (Wednesday)	Database backup
6:00 A.M. (Thursday)	Differential database backup
Noon	Differential database backup

The differential database backup created at 6:00 P.M. on Wednesday contains all changes made to the database after the database was backed up at midnight on Tuesday. The differential database backup created at noon on Thursday contains all changes made to the database after the database was backed up at midnight on Wednesday. To restore the database to its state at noon on Thursday, perform these steps in order:

1. Restore the database backup created at midnight on Wednesday. 

2. Restore the differential database backup created at noon on Thursday. 

Any changes made after noon on Thursday are lost unless transaction log backups are also used to restore the database.

In this example, if the database backup from midnight on Wednesday is lost or damaged, it is possible to restore the database only to its state at 6:00 P.M. on Wednesday. This is because the differential database backups created on Thursday apply only to the previous database backup (in this example, the lost database backup created at midnight on Wednesday).

Note Because transaction log backups cannot be created after nonlogged operations occur in a database, differential database backups can be created instead of creating a full database backup.

Restoring a differential database backup is similar to restoring a database backup; it leaves the database in the same state it was in when the differential database backup completed, although incomplete transactions will have been rolled back.

Differential database backups are often supplemented by creating multiple transaction log backups after each differential database backup is created. By using a combination of database, differential database, and transaction log backups, recovery time and the amount of potential data loss due to failure can be minimized. For more information, see "Using Differential Database Backups with Transaction Log Backups" in this volume.

Backup Restrictions 

Using Microsoft SQL Server, backup operations can occur while the database is online and in use. However, some operations are not allowed during a differential database backup operation:

• Creating or deleting database files 

• Creating indexes 

• Performing nonlogged operations 

• Shrinking either the database (automatically or manually) or the database files. For more information, see "Shrinking the Database" in Microsoft SQL Server Database Developer's Companion.

If a backup operation is started when one of these operations is in progress, the backup operation terminates. If a backup operation is already in progress and one of these operations is attempted, the operation fails and the backup operation continues.

Recovering a Database Without Restoring

The operation of rolling back all uncompleted transactions and rolling forward all completed transactions is called recovery and is required to restore the integrity of the database. Restoring a database backup or applying a transaction log backup recovers the database automatically at the end of the restore operation. Additionally, recovery occurs in a database each time the database is opened, for example when Microsoft SQL Server starts.

You may need to recover a database manually when:

• A standby server has been kept up-to-date with a production server by loading a series of transaction log backups from the production server.

  To bring the standby online, recover the database. 

• You attempt to recover a suspect database. 

See Also 

In This Volume 

Using Standby Servers

Restoring a Database to a Prior State

By using a database backup and a sequence of transaction log backups, it is possible to restore a database to any point in time encompassed within those transaction logs.

Restoring to the Point of Failure 

If, for example, a failure requires that the database needs to be restored, you can restore a database to the state it was in at the point of failure if the current transaction log file for the database is available, and all the conditions for backing up a transaction log are satisfied. To restore the database to the point of failure:

• Back up the currently active transaction log. For more information, see "Creating and Applying Transaction Log Backups" in this volume. 

• Restore the database backup without recovering the database. 

• Apply each transaction log backup in the same sequence in which they were created without recovering the database. 

• Apply the backup of the current transaction log file as the last backup in the chain, and recover the database. 

Restoring to a Prior State 

You may want to restore the database to an earlier point in time than to the point of failure. For example, an earlier transaction within a database changed some data incorrectly, but the transaction was committed, and several transaction log backups have been created afterward. Therefore, you will need to restore the database backup and only those transaction logs that contain the changes prior to the transaction you do not want applied.

Note It is not possible to omit a transaction selectively from being applied (applying transactions that occur after the omitted transaction) because this would compromise the integrity of the data in the database. Any transactions that occur after the transaction you want to undo might depend on the data modified by the undone transaction.

If you do not want to attempt to restore any modifications made to the database after a specific transaction log backup was created:

• Restore the last database backup without recovering the database.

• Apply each transaction log backup in the same sequence in which they were created. 

  Do not recover the database until the last transaction log you want to recover is applied. 

This process can also be used to restore a database and any transaction logs if some transaction log backups created after a point in time are missing or damaged or the conditions for backing up a transaction log are not satisfied (for example, nonlogged operations have occurred).

Restoring to a Point within a Transaction Log Backup 

You can also instruct recovery to apply only transactions that occurred before a specific point in time within a transaction log backup, rather than applying the entire backup. By viewing the header information of each transaction log backup, you can examine each transaction log backup to identify quickly which backup contains the time to which you want to restore and then apply only those transaction log backups up to and including the identified backup.

See Also 

In This Volume 

Using Standby Servers

In Other Volumes 

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"RESTORE HEADERONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Backing Up and Restoring the System Databases

The system databases need to be backed up just as user databases are backed up. This allows the system to be rebuilt in the event of system or database failure, for example, if a hard disk fails. It is important to have regular backups of the following system databases:

• master 

• msdb 

• distribution (when the server is configured as a replication Distributor) 

• model (if modified) 

Note It is not necessary to back up the tempdb system database because tempdb is rebuilt each time Microsoft SQL Server is started. When SQL Server is shut down, any data in tempdb is deleted permanently.

Backing Up the master Database

The master database is backed up in the same way as user databases. If master is damaged in some way, for example because of media failure, Microsoft SQL Server may not be able to start. In this event, it is necessary to rebuild master, and then restore the database from a backup.

Consider backing up master after any statement or system procedure is executed that changes information in master, for example, changing a server-wide configuration option. If master is not backed up after it changes, any changes since the last backup are lost if the backup is restored. For example, if a user database is created after master is backed up, tables and data added to the database, and then master is restored because of a hard disk failure, the user database will not be known to SQL Server because there are no entries in the restored master database for this new user database. In this case, if all database files comprising the user database still exist on the disk(s), the user database can be created by attaching the database files. For more information, see "Attaching and Detaching Databases" in Microsoft SQL Server Database Developer's Companion. 

Note It is recommended that user objects not be created in master, otherwise master needs to be backed up more frequently. Additionally, user objects compete with the system objects for space.

The types of operations that cause master to be updated, and that require a backup to take place include:

• Creating or deleting a user database.

  If a user database grows automatically, by virtue of the autogrow feature, to accommodate new data, this does not affect master. Adding and deleting files and filegroups does not affect master. 

• Adding logins or other login security-related operations.

  Database security operations, such as adding a user to a database, do not affect master. 

• Changing any server-wide or database configuration options. 

• Creating or removing backup devices. 

• Using SQL Server Enterprise Manager to perform any of these operations. 

• Configuring the server for distributed queries and remote procedure calls, such as adding linked servers, or remote logins. 

Note Only full database backups of master can be created.

See Also 

In This Volume 

Restoring the master Database

Restoring the master Database

If master is damaged in some way, for example due to media failure, Microsoft SQL Server may not be able to start if the damage is severe. There are two methods to return master to a usable state:

• Restore from a current backup. 

• Rebuild completely using the Rebuild Master utility. 

Important Keep a current backup of master; rebuilding master using the Rebuild Master utility causes all data stored previously in master to be lost permanently.

If SQL Server can be started because master is accessible, and at least partly usable, it is possible to restore master from a backup like any other database. However, if SQL Server cannot be started because of severe damage to master, it is not possible to restore a backup of master immediately because SQL Server needs to be running to restore any database. The master database first needs to be rebuilt using the Rebuild Master utility, and the current backup can be restored as normal.

Restoring the master Database from a Current Backup

If there have been any changes to master after the database backup was created, those changes are lost when the backup is restored. Therefore, it is necessary to re-create those changes manually after restoring master from a backup by executing the statements necessary to re-create the missing changes. For example, if any Microsoft SQL Server logins have been created after the backup was performed, those are lost when master is restored. Re-create the logins using SQL Server Enterprise Manager or the original scripts used to create the logins.

Note Any database users previously associated with logins that need to be re-created are orphaned because the login is lost. For information about associating an existing database user to a new SQL Server login, see "sp_addlogin" in Microsoft SQL Server Transact-SQL and Utilities Reference. For information about associating an existing database user with a Windows NT user, see "sp_grantlogin" in Microsoft SQL Server Transact-SQL and Utilities Reference. 

If any user databases have been created after master was backed up, those databases cannot be accessed unless:

• The databases are reattached to SQL Server. 

  Or 

• The databases are restored from backups. 

Attaching the database to SQL Server re-creates the system table entries needed and makes the database available in the same state it was before the master database was restored. It is not necessary to re-create the database first; the files can be attached without knowing how the database was created, as long as all the files comprising the database are attached.

It is necessary to restore a backup of the database only if the data and transaction log files of the database no longer exist or are unusable or damaged in some other way due to a media failure.

If any databases are restored or attached after master is rebuilt, it is necessary to specify the same sort order, code page, and Unicode collation when rebuilding master as used by those databases. If the same sort order, code page, and Unicode collation are not used, it will not be possible to restore or attach those databases.

If any objects, logins, or databases, for example, have been deleted after master was backed up, those objects, logins, and databases should be deleted from master.

Important If any databases no longer exist, but are referenced in a backup of master that is restored, SQL Server may report errors when it starts because it cannot find those databases any longer. Those databases should be dropped after the backup is restored.

When restoring master, SQL Server needs to be started in single-user mode to prevent any additional users from using SQL Server. All SQL Server services (except SQL Server itself) and utilities, such as the SQL Server Agent, can be stopped because they may try to access SQL Server.

When master has been restored and any changes have been reapplied, back up master immediately.

See Also 

In This Volume 

Copying Databases Between Different Code Pages and Sort Orders

Starting SQL Server in Single-User Mode

In Other Volumes 

"Attaching and Detaching Databases" in Microsoft SQL Server Database Developer's Companion 

"System Tables" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Rebuilding the master Database

If a current backup of master is not available, or the backup cannot be restored because Microsoft SQL Server cannot start due to severe damage to master, master can be rebuilt using the Rebuild Master utility. When master has been rebuilt, a current backup of master can be restored, or the user databases, backup devices, SQL Server logins, and so on, can be re-created using SQL Server Enterprise Manager, or the original scripts used to create those entries.

Important The Rebuild Master utility rebuilds master completely. Because the msdb and model system databases are rebuilt as well, it may be necessary to restore backups of those databases. If any databases are restored or attached after master is rebuilt, it is necessary to specify the same sort order, code page, and Unicode collation when rebuilding master as used by those databases. If the same sort order, code page, and Unicode collation are not used, then it will not be possible to restore or attach those databases.

The general steps required to rebuild master completely if no backup is available are:

• Run the Rebuild Master utility to rebuild the system databases using the same sort order, code page, and Unicode collation used by the user databases.

  Important The compact disc or shared network directory containing the SQL Server installation software is required to rebuild the master database. 

• Re-create any necessary backup devices. 

• Reimplement security operations. 

• Restore msdb if necessary. 

• Restore model if necessary. 

• Restore distribution if necessary. 

• Restore or attach user databases if necessary. 

When master has been re-created and any changes have been reapplied, back up master immediately.

See Also 

In This Volume 

Copying Databases Between Different Code Pages and Sort Orders

In Other Volumes 

"Attaching and Detaching Databases" in Microsoft SQL Server Database Developer's Companion 

"System Tables" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Backing Up the model, msdb, and distribution Databases

The model database is a template, used by Microsoft SQL Server when creating other databases, such as tempdb or user databases. When a database is created, the entire contents of the model database are copied to the new database. The msdb database is used by SQL Server, SQL Server Enterprise Manager, and SQL Server Agent to store data, including scheduling information, and backup and restore history information. The distribution database is used by the replication components of SQL Server, such as the Distribution Agent, to store data including transactions, snapshot jobs, synchronization status, and replication history information. A server configured to participate either as a remote distribution server or as a combined Publisher/Distributor has a distribution database.

The model, msdb, and distribution databases are backed up in the same way as user databases and should be backed up regularly if they are changed.

It is important to back up model, msdb, or distribution after any operation that updates the database. If model is damaged in some way due to media failure, and there is no current backup available, any user-specific template information added to model is lost and needs to be re-created manually. If msdb is damaged then any scheduling information used by the SQL Server Agent is lost and needs to be re-created manually by using SQL Server Enterprise Manager. Similarly, if distribution is damaged, and there is no current backup available, any replication information used by the SQL Server replication utilities is lost and needs to be re-created manually.

The model database is modified only by specific user changes.

The msdb database is altered automatically by:

• Scheduling tasks. 

• Storing DTS packages created with the DTS Import and DTS Export wizards to SQL Server. 

• Maintaining online backup and restore history. 

• Replication. 

The distribution database is altered automatically by:

• The Replication Log Reader Agent utility. 

• The Replication Distribution Agent utility. 

• The Replication Snapshot Agent utility. 

• The Replication Merge Agent utility. 

As with master, it is recommended that user objects are not created in msdb or distribution; otherwise msdb and distribution need to be backed up more frequently. Additionally, user objects compete with the system objects for space.

SQL Server maintains a complete online backup and restore history automatically in msdb. This information includes who performed the backup, at what time, and on which devices or files it is stored. This information is used by SQL Server Enterprise Manager to propose a plan automatically for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL-DMO objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows NT application log, and SQL Server error log.

All types of backup operations are supported for model, msdb and distribution. Because model and msdb are unlikely to become large, it may be easier to back up the entire database on a regular basis, rather than manage transaction log backups. Similarly, if distribution is unlikely to become large, it may be easier to back up the entire database on a regular basis, rather than manage transaction log backups.

See Also 

In This Volume 

Backing Up and Restoring Databases

Configuring the SQLServerAgent Service

In Other Volumes 

"Overview of Replication" in Microsoft SQL Server Distributed Data Operations and Replication 

"System Tables" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Restoring the model, msdb, and distribution Databases

The model, msdb, or distribution database may need to be restored from a backup when:

• The master database has been rebuilt using the Rebuild master command prompt utility. 

• The model, msdb, or distribution database has been damaged, for example, due to media failure. 

If model has been modified, it is necessary to restore model from a backup when you rebuild master because the Rebuild Master utility deletes and re-creates model. 

If msdb contains scheduling or other data used by the system, it is necessary to restore msdb from a backup when you rebuild master because the utility deletes and re-creates msdb, which results in a loss of all scheduling information. If msdb is not restored, and is not accessible, SQL Server Agent cannot access or initiate any previously scheduled tasks. For example, if database backup operations are scheduled to run automatically using SQL Server Enterprise Manager and SQL Server Agent, a damaged msdb will prevent those backup operations from occurring.

The distribution database is not rebuilt automatically when the Rebuild Master utility is used to rebuild master; therefore it is not necessary to restore distribution after rebuilding master. If the distribution database is still intact, distribution can be re-created automatically by attaching the database to Microsoft SQL Server. Alternatively, a backup of distribution can be restored instead.

However, if distribution is not re-created by restoring a backup or attaching the database, the SQL Server replication utilities will not run, preventing data replication. If the distribution database is used for replication by many Publishers, this can affect many systems.

You cannot restore a database that is being accessed by users. Therefore, when restoring msdb, SQL Server Agent should be stopped. If SQL Server Agent is running, it may access msdb. Similarly, when restoring distribution, the SQL Server replication utilities should be stopped. If the SQL Server replication utilities are running, they may access distribution.

Replication utilities that must be stopped are:

• The Replication Log Reader Agent utility. 

• The Replication Distribution Agent utility. 

• The Replication Snapshot Agent utility. 

• The Replication Merge Agent utility. 

See Also 

In This Volume 

Copying Databases Between Different Code Pages and Sort Orders

Configuring the SQLServerAgent Service

In Other Volumes 

"Attaching and Detaching Databases" in Microsoft SQL Server Database Developer's Companion 

"Overview of Replication" in Microsoft SQL Server Distributed Data Operations and Replication 

Managing Backups

It is important to manage your backups carefully to ensure that you can restore your system when needed. Each backup contains the descriptive text you provided when you created the backup, as well as expiration information. This information can be used to:

• Identify a backup. 

• Determine when the backup can be overwritten, preventing the backup from being reused accidentally.

• Identify all the backups on a backup medium, such as a tape, to determine which backup needs to be restored. 

Additionally, the msdb database contains a complete history of all backup and restore operations on the server. SQL Server Enterprise Manager uses this information to suggest and execute a restore plan that can be used if a database needs to be restored. For example, if a database backup for a user database is created every night, and transaction log backups are created every hour during the day, this backup history information is stored in the msdb database. If the user database needs to be restored, SQL Server Enterprise Manager can apply all the transaction log backups that relate to a specific database backup, when the database backup is restored, by using this history information stored in msdb.

Note If the msdb database needs to be restored, any backup history information saved since the last backup of msdb was created is lost.

When working with backups:

• Maintain backups in a secure place, preferably at a site different from the site where the data resides. 

• Keep older backups for a designated amount of time in case the most recent backup is damaged, destroyed, or lost. 

• Establish a system for overwriting backups, reusing the oldest backups first. 

• Use expiration dates on backups to prevent premature overwriting. 

• Label backup media to prevent overwriting critical backups, for easy identification of the data stored on the backup media or the specific backup set. For example, if a tape contains only backups of the sales database for January, label the tape medium to state this fact. Alternatively, if a tape contains backups of three different databases, label each backup set on the tape to identify the contents of each backup set clearly. 

See Also 

In This Volume 

Backup Media

Understanding Media Sets and Families

Backup Format

All media, such as disk, tape, or named pipes, used for a backup or restore operation uses a standard backup format called Microsoft Tape Format (MTF), regardless of whether the backups are written to disk, named pipes, or tape. MTF enables Microsoft SQL Server backups to coexist on the same media as backups that are not SQL Server backups (foreign backup sets), provided that the backups use MTF. For example, both SQL Server backups and Microsoft Windows NT backups can exist on the same media.

Integrating any backups supporting MTF onto a single tape reduces backup media storage requirements, costs, and administrative overhead because the same tape media can be used to store different backups from different applications. For example, both Windows NT and SQL Server backups can be written to the same tape rather than to separate tapes.

All media begins with a media header describing the media. The media header is usually written one time and remains intact for the life of the media. This allows each piece of media to be tracked. The media header can contain a media* *name. A media name is the name given to the particular media and is assigned by the first person using the media. Consistent use of media names helps identify the media and prevent errors, for example, Sales Database Backups.

SQL Server cannot read, or write over, any foreign backup set or media that is encrypted or protected with a password. Therefore, SQL Server must overwrite the media containing a media password or encryption before using the media for the first time. By overwriting backups on media, the existing contents of the media are completely overwritten with the new backup, and therefore no longer available.

Note Although MTF allows media to be password protected, SQL Server does not currently support media passwords.

See Also 

In This Volume 

Backup Media

In Other Volumes 

"RESTORE HEADERONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"RESTORE LABELONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Viewing Information About Backups

After backups are created, you may need to view information about the backups, such as:

• A list of the database and transaction log files contained in a specific backup set. 

• The backup header information for all backups on a particular backup media. 

• The media header information for a particular backup medium. 

Listing Database and Transaction Log Files 

Information displayed when listing the database and transaction log files in a backup includes the logical name, physical name, file type (database or log), filegroup membership, backup-set size (in bytes), the maximum allowed file size, and the predefined file growth size (in bytes). This information is useful to determine the names of the files in a database backup before restoring the database backup when:

• You have lost a disk drive containing one or more of the files for a database.

  You can list the files in the database backup to determine which files were affected, and then restore those files onto a different drive when restoring the entire database, or restore just those files and apply any transaction log backups created since the database was backed up. 

• You are restoring a database from one server onto another server but the directory structure and drive mapping does not exist on the server. 

  Listing the files in the backup allows you to determine which files are affected. For example, the backup contains a file that it needs to restore to the E:\ drive, but the destination server does not have an E:\ drive.; the file needs to be relocated to another location, such as the C:\ drive, when the file is restored. 

Header Information 

Viewing the backup header displays information about all Microsoft SQL Server and foreign backup sets on the media. Information displayed includes the types of backup devices used, the backup types (database, transaction, file, or differential database), and backup start and stop date/time information. This information is useful when you need to determine which backup set on the tape to restore, or the backups that are contained on the media.

Note Viewing backup header information can take a long time for high-capacity tapes because the entire media needs to be scanned to display information about each backup on the media.

Viewing the media header displays information about the media itself, rather than the backups on the media. Viewing the media header is quick because only the media header is read, located one time at the beginning of the media. Media header information displayed includes the media name, description, name of the software that created the media header, and the date the media header was written. For more information about a detailed list of the header information displayed, see "RESTORE LABELONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference.

For example, restoring the backup header information for the following tape media containing two SQL Server backups and two foreign (Windows NT) backups retrieves information for all backup sets on the media, requires that the entire tape be scanned. However, restoring the media header requires only information from the single media header written at the beginning of the tape to be retrieved.

Verifying Backups

Verifying a backup involves checking the backup set to ensure that all files have been written and that the files in the backup are readable. Although not required, verifying a backup checks that the backup is intact physically, and that you can rely on your backup in the event you need to use it. However, verifying a backup does not check that the structure of the data contained within the backup set is correct. For example, although the backup set may have been written correctly, it may be possible for some type of database integrity problem to be present within the database files that comprise the backup set. To verify the structure of the data before creating a backup, database consistency checks can be performed. For more information about running database consistency checks, see "DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference or "Database Maintenance Plan Wizard" in Microsoft SQL Server Database Developer's Companion. 

Minimizing Backup and Recovery Times in Mission-Critical Environments

Mission-critical environments often require that the database be available continuously, or for extended periods of time with minimal down-time available for maintenance tasks. Therefore, the duration of unexpected situations, such as a hardware failure, that require the database to be restored need to be kept as short as possible to make the database available again. Additionally, mission-critical databases are often large, requiring longer periods of time to back up and restore. Microsoft SQL Server offers several methods for increasing the speed of backup and restore operations, thereby minimizing the effect on users during both operations:

• Using multiple backup devices simultaneously allows backups to be written to all devices at the same time. Similarly, the backup can be restored from multiple devices at the same time. 

• Using a combination of database, differential database, and transaction log backups to minimize the number of backups that need to be applied to bring the database to the point of failure. 

• Using file and filegroup backups and transaction log backups, allowing only those files that contain the relevant data to be backed up or restored, rather than backing up the entire database. 

Using Multiple Media or Devices

Multiple backup devices can be used for backup and restore operations. This allows Microsoft SQL Server to use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from at the same time as other backup devices. For example, if it takes four hours to back up a database to a single tape drive, the backup speed when two tape drives are used is likely to be two hours. For enterprises with large databases, using many backup devices can decrease the time taken for backup and restore operations.

However, all backup devices used in a single backup (and consequently restore) operation must be of the same type (disk, tape, or named pipe). For example, to back up the sales_db database daily using database and differential database backups to tape, only multiple tape drives can be used.

Note Tape backup devices must be attached to the server physically. It is not possible to use tape backup devices on remote computers.

Creating and restoring backups using multiple backup devices is the same as creating and restoring backups using a single device; the only difference is that all backup devices involved in the operation, not just one, are specified. For example, if a database backup is to be created using three tape backup devices such as \\.\TAPE0, \\.\TAPE1, and \\.\TAPE2, each of the tape devices needs to be specified as part of the backup operation, although fewer tape backup devices can be used when restoring the backup later.

When creating a backup using multiple backup devices on removable media, each backup media does not need to be the same size, have the same amount of storage available, or operate at the same speed. If one backup media used by a backup device runs out of space while a backup is being created, SQL Server stops writing to the backup device and prompts for new media to continue writing to that backup device. While waiting for new media to be inserted into the backup device, the backup operation continues writing data to any other backup devices involved in the backup operation, as long as the backup media used by these devices has space available.

For example, three tape backup devices of equal speed are used to store a database backup. The first two tape media are 10 GB in size, but the third is only 5 GB is size. If the sales database, which is 20 GB in size, is backed up to all three tape backup devices simultaneously, the backup operation will stop writing to the third backup device and prompt for a new tape when 5 GB has been written to the tape. However, the backup operation continues writing data to the other two backup devices. When the tape media on the third backup device is replaced with a new tape, the backup operation continues writing data to the third backup device.

Several internal synchronization points occur when a database backup is written to multiple backup devices. The most important synchronization point occurs when all the data in the database has been backed up, and the transaction log is about to be backed up. All backup devices used in the backup operation must not be blocked, during these synchronization points otherwise the entire backup operation is blocked until all backup media is available. For example, three tape backup devices are used to store a database backup, and the second tape backup device is blocked, waiting for the existing tape to be replaced because the space on the tape has been exhausted. If a synchronization point occurs, the entire backup operation will stop until the tape in the second backup device is replaced.

Important When using multiple backup devices to perform backup operations, the backup media involved can be used only for SQL Server backup operations.

See Also 

In This Volume 

Backup Media

In Other Volumes 

"Optimizing Backup and Restore Performance" in Microsoft SQL Server Diagnostics 

Understanding Media Sets and Families

Microsoft SQL Server introduces the concepts of a media set and media family when using multiple backup media and devices.

A media set can contain one or more backup sets and describes all of the media used by those backup sets, regardless of the number of media or backup devices involved. For example, if four tape backup devices are used when creating a database backup, and five tapes per tape backup device are used to store the backup, the media set contains 20 tapes.

A media family describes all the media used by a single backup device for a single backup set. In the example earlier, there are four media families with each set of five tapes used by each tape backup device comprising one media family.

The initial media is the first media in a media family. If the initial media becomes full during the backup operation, more media is used until the backup operation is complete. All media, except the initial media, in a media family is described as continuation media.

Note Only tape backup devices use continuation media, allowing SQL Server to continue writing the backup after the initial tape is full.

To distinguish between each physical medium used within a media family, each medium is tagged with a sequence number to specify the order in which the media were used. The initial media is tagged with 1, and second medium (the first continuation media) with 2, and so on. These sequence numbers are used when the backup set is restored to ensure that the operator restoring the backup mounts the correct media in the correct order. Additionally, media families within a media set are numbered sequentially.

When appending a backup set to a media set containing multiple media families, the sequence numbers are used to confirm that the last medium in each media family is mounted before the backup is written. If the last media in a media family is not mounted, SQL Server scans forward until the correct sequence number is found, requiring media to be changed until the last media in the family is mounted correctly.

Each SQL Server backup is stored on a media set, regardless of the number of backup devices used by the individual backup operation. Examples of media sets include:

• A single disk file. 

• A single tape. 

• A set of tapes written by one backup device. This set of tapes consists of a single media family (an initial media and one or more continuation media). 

• A set of tapes written by four backup devices. Each set of tapes written by one backup device is the media family. Each media family contains an initial media and possibly one or more continuation media. 

• A set of three disk files, used by one or more backup operations with each backup operation using three backup devices. 

When using multiple backup devices:

• The entire media set created by a backup operation must be used by all subsequent backup operations.For example, if a media set was created using two tape backup devices, all subsequent backup operations involving the same media set must use two backup devices. 

• It is not necessary to use the same backup devices used by the media set when restoring the backup. For example, restoring using fewer backup devices may be necessary when moving a database to another server, because the server may have fewer physical backup devices, such as only one tape device. In this situation, the initial media families are processed first. The backup device(s) then prompt for additional media families, until all of the media families have been processed. 

See Also 

In This Volume 

Backup Media

Using Differential Database Backups with Transaction Log Backups

Using database, differential database, and transaction log backups together can reduce the amount of time it takes to restore a database back to any point in time after the database backup was created. Additionally, creating both differential database and transaction log backups can increase the robustness of backup procedures in the event that either a transaction log backup or differential database backup becomes unavailable, for example, due to media failure.

Typical backup procedures using database, differential database, and transaction log backups are to create database backups at longer intervals, differential database backups at medium intervals, and transaction log backups at shorter intervals. For example, create database backups weekly, differential database backups daily, and transaction log backups hourly.

If a database needs to be recovered to the point of failure, for example, due to a system failure:

1. Back up the currently active transaction log if the transaction log files for the database are accessible. 

2. Restore the last database backup created. 

3. Restore the last differential backup created since the database backup was created. 

4. Apply all transaction log backups, in sequence, created after the last differential backup was created, finishing with the transaction log backup created in Step 1. 

Note If the active transaction log is not accessible, or the conditions for backing up the active transaction log are not satisfied, it is possible to restore the database only to the point when the last transaction log backup was created.

By using differential database and transaction log backups together to restore a database to the point of failure, the time taken to restore a database is reduced because only the transaction log backups created since the last differential database backup was created need to be applied. If a differential database backup was not created, then all the transaction log backups created since the database was backed up need to be applied.

For example, a mission-critical database system requires that a database backup is created each night at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. If the database needs to be restored to its state at 5:19 A.M. on Wednesday:

1. Restore the database backup created on Tuesday night. 

2. Restore the differential database backup created at 5:00 A.M. on Wednesday. 

3. Apply the transaction log backup created at 5:10 A.M. on Wednesday. 

4. Apply the transaction log backup created at 5:20 A.M. on Wednesday, specifying that the recovery process only applies transactions that occurred before 5:19 A.M. 

Alternatively, if the database needs to be restored to its state at 3:04 A.M. on Thursday, but the differential database backup created at 3:00 A.M. on Thursday is unavailable:

1. Restore the database backup created on Wednesday night. 

2. Restore the differential database backup created at 2:00 A.M. on Thursday. 

3. Apply all the transaction log backups created from 2:10 A.M. to 3:00 A.M. on Thursday. 

4. Apply the transaction log backup created at 3:10 A.M. on Thursday, specifying that the recovery process only applies transactions that occurred before 3:04 A.M. 

See Also 

In This Volume 

Creating and Applying Transaction Log Backups

Creating and Restoring a Database Backup

Creating and Restoring Differential Database Backups

Using File or Filegroup Backups

The files or filegroups, or combinations of files and filegroups, in a database can be backed up and restored individually. Individual files and filegroups can be restored from database backups, allowing recovery from media failure, which affects only a subset of the files in a database.

A file or filegroup backup is usually created only if there is not enough time to create a backup of the entire database. For example, if a database backup takes three hours to create, but only two hours per night are available to create backups, half of the files can be backed up one night and the other half of the files can be backed up the next night.

Using file or filegroup backups can increase the speed of recovery by restoring only the damaged files or filegroups. If the parts of a database that need to be restored are fully contained within a subset of the files or filegroups in the database, only those files or filegroups need to be restored. This is used primarily for large databases in time-critical environments.

This can reduce the time required to restore a database in cases where only a known portion of the database needs to be restored. For example, if a database is comprised of several files with each file physically located on a different disk and one disk fails, only the file on the failed disk needs to be recovered without having to restore the rest of the database.

However, when restoring a subset of the database, it is also necessary to apply transaction log backups created since the file or filegroup backups were created to bring the database to a consistent state. For example, after restoring the individual file in the previous example, each transaction log backup created since the file was backed up must be restored, otherwise the database will not be consistent (unless no changes have occurred in the database since the file was backed up).

Therefore, to make use of file and filegroup backups, transaction log backups must be created as well.

Individual files or filegroups can be restored from either a database backup or a filegroup backup. This means that you can use database and transaction log backups as your backup procedure, and still be able to restore individual files or filegroups from the database backups.

See Also 

In Other Volumes 

"Placing Indexes on Filegroups" in Microsoft SQL Server Diagnostics 

"Files and Filegroups" in Microsoft SQL Server Database Developer's Companion 

"Placing Tables on Filegroups" in Microsoft SQL Server Diagnostics 

Creating File or Filegroup Backups

A file or filegroup backup copies one or more files of a specified database, allowing a database to be backed up in smaller units: at the file or filegroup level.

When creating file and filegroup backups, the transaction log is not captured by the backup operation. Therefore, transaction log backups must be created after a file or filegroup backup is created, otherwise any changes to the database files, backed up in the file or filegroup backup, would be lost if the file or filegroup backup is later restored.

For example, if a database is comprised of filegroups filegroup_a and filegroup_b, but there is only time during the week to back up half of the filegroups per evening, a backup procedure could be:

1. Create a filegroup backup for filegroup_a on Monday, Wednesday, and Friday. 

2. Create a transaction log backup after creating the filegroup backup. 

3. Create a filegroup backup for filegroup_b on Tuesday, Thursday, and Saturday. 

4. Create a transaction log backup after creating the filegroup backup. 

If any tables and indexes are created that span multiple filegroups, all filegroups affected by the creation of the index must be backed up together, followed by creating a transaction log backup, otherwise only parts of a table or index would be backed up, preventing the creation of the table or index from being recovered if later restored. Microsoft SQL Server generates an error if any of the filegroups that the table and index spans are missing when a filegroup backup is created. For example, if a table is created on one filegroup, and a nonclustered index for the table is created on a separate filegroup, then both filegroups must be backed up as a single unit.

Therefore, when designing databases with tables and indexes arranged on multiple filegroups, the requirements to create filegroup backups involving these tables and indexes need to be considered.

Restoring File or Filegroup Backups

A file or filegroup can be restored from either a database backup or a file backup.

After restoring files or filegroups, a transaction log backup, created after the files or filegroups were backed up, needs to be applied to recover the database to a consistent state. Applying a transaction log backup is not necessary if Microsoft SQL Server can determine that the files or filegroups have not been modified after the file or filegroup backup was created.

For example, a database is comprised of filegroups filegroup_a and filegroup_b. A database backup is created every Sunday night at midnight; filegroup_a is backed up on Monday, Wednesday, and Friday at midnight, and filegroup_b is backed up on Tuesday, Thursday, and Saturday at midnight. Additionally, a backup of the transaction log is created every hour. If filegroup_b needs to be restored on Saturday morning because a table within filegroup_b was damaged due to media failure:

1. Restore the filegroup_b backup from Thursday night. 

2. Apply all transaction log backups that have been created since filegroup_b was backed up on Thursday night. 

If the entire database is lost, the following is required:

• Backups of every file or filegroup in the database. 

• An unbroken transaction log backup chain from the time of the earliest file or filegroup backup to the end of the latest file or filegroup backup. The start of the transaction log backup chain must contain the start of the transaction for the oldest transaction outstanding at the time the earliest file backup was created. 

Important If one of these two conditions is not met, it is not possible to restore the database.

If any tables and indexes are created that span multiple filegroups, all filegroups affected by the creation of the index must be restored together, followed by applying the subsequent transaction log backup, otherwise only parts of a table or index would be restored, preventing the creation of the index from being recovered. SQL Server generates an error if any of the filegroups that the table and index spans are missing when a filegroup backup is restored. For example, if a table is created on one filegroup, and a nonclustered index for the table is created on a separate filegroup, both filegroups must be restored as a single unit.

Copying Databases to Other Servers

Creating database backups allows you to copy data from one computer to another. The copied database can be used for testing, consistency checking, software development, and offline processing, such as running reports, or possibly remote branch operations. By copying a database from one computer to another, it is possible to reduce resource contention by offloading processing to other computers running Microsoft SQL Server. Copied databases restored onto separate computers are often used for read-only operations.

A database can also be copied to another computer to act as a standby server or warm backup server. The database and the transaction logs are copied to another computer periodically, which can be brought online if the primary computer fails for some reason. The level of synchronization between the primary computer and the standby server is determined by how often regular backups are taken of the primary computer and applied to the standby server. For more information, see "Using Standby Servers" in this volume.

It is possible to back up a database running on one computer and restore the backup to a separate computer if the following criteria are satisfied:

• The code page/character set used by SQL Server on both computers is the same. 

• The sort order used by SQL Server on both computers is the same. 

• The Unicode collation and locale used by SQL Server on both computers is the same. 

• The database backup is not from SQL Server version 6.5 or earlier. 

It is no longer necessary for the processor architectures of the computers running SQL Server to be the same.

Note It is possible to back up and restore databases between computers running SQL Server on Microsoft Windows NT and Microsoft Windows 95/98 if the earlier criteria are satisfied.

Other methods for copying data from one computer running SQL Server to another include using:

• The Data Transformation Services Import and Export wizards to copy and modify data between any ODBC, OLE DB, or text data source and SQL Server. 

• The bcp utility to copy data between SQL Server and a data file, using native, character, or Unicode mode. 

• The INSERT statement, which uses a distributed query as the select list to extract data from another data source. 

See Also 

In This Volume 

Using Standby Servers

In Other Volumes 

"Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication 

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Copying Databases

The general steps required to copy a database to another computer are:

1. Ensure that both computers running Microsoft SQL Server are using the same code page, sort order, Unicode collation, and Unicode locale. 

2. Back up the database from the source computer running SQL Server. 

3. Create backup devices, if desired, at the destination computer running SQL Server. 

4. Restore the database backup to the destination computer. It is not necessary to create the files or the database before restoring the backup. 

Re-creating Database Files 

Restoring a database automatically creates the files needed by the database backup to restore the backup into. The database files (hence the database) do not need to be created before restoring a backup. By default, the files created by SQL Server during the restoration process use the same name and path as the backup files from the original database on the source computer. Therefore, it is useful to know in advance the files that are created automatically by the restore operation, because:

• The file names may already exist on the computer, causing an error. 

• The directory structure or drive mapping may not exist on the computer.

  For example, the backup contains a file that it needs to restore to drive E, but the destination computer does not have a drive E. 

• If the database files are allowed to be replaced, any existing database and files with the same names as those in the backup are overwritten, unless those files belong to a different database. 

Moving the Database Files 

If the files within the database backup cannot be restored onto the destination computer because of the reasons mentioned earlier, it is necessary to move the files to a new location as they are being restored. For example:

• It may be necessary to restore some of the database files in the backup to a different drive because of capacity considerations. This is likely to be a common occurrence because most computers within an organization do not have the same number and size of disk drives or identical software configurations. 

• Creating a copy of an existing database on the same computer for testing purposes may be necessary. In this case, the database files for the original database already exist, so different file names need to be specified when the database copy is created during the restore operation. 

Changing the Database Name 

The name of the database can be changed as it is restored to the destination computer, without having to restore the database first and then change the name manually. For example, it may be necessary to change the database name from Sales to SalesCopy to indicate this is a copy of a database.

The database name explicitly supplied when restoring a database is used automatically as the new database name. Because the database name does not already exist, a new one is created using the files in the backup.

Database Ownership 

When a database is restored onto another computer, the SQL Server login or Microsoft Windows NT user who initiates the restore operation becomes the owner of the new database automatically. When the database is restored, the system administrator or the new database owner can change database ownership.

Restoring Full-Text Index Data 

If the database being copied contains tables that have been defined for full-text indexing, then the destination computer must also have Full-Text Search installed and the MSSearch Service started before the full-text catalogs can be re-created and repopulated.

Because the metadata for the full-text index definitions are stored in the system tables of a database, it is useful to know in advance whether any of the full-text catalogs on the source computer resided on drives and directories other than the default (\MSSQL7\FtData). These directories or drive mappings may not exist on the destination computer and must be created first. To view the locations of the full-text catalog(s) on the source computer, execute the sp_help_fulltext_catalogs system stored procedure. The PATH column value is the location where the full-text catalog will be re-created on the destination computer. If the PATH column value of the result set is NULL, then this denotes the default full-text catalog location.

See Also 

In This Volume 

Creating and Restoring a Database Backup

Creating and Applying Transaction Log Backups

In Other Volumes 

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"RESTORE FILELISTONLY" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Copying Databases Between Different Code Pages and Sort Orders

A database can be backed up from one computer running Microsoft SQL Server to another only if both use the same sort orders, Unicode collation, and code pages (character set), set up during installation. If a different sort order, Unicode collation, Unicode locale, or character set is used, the restore operation detects this and does not attempt to restore the database. To restore the database, SQL Server must be reinstalled in the destination computer using the same sort order, Unicode collation, and character set as the computer that performed the database backup.

Note The Unicode collation defines the sorting behavior for the nchar and nvarchar data types.

If the databases use a binary sort order, it is not necessary for the character sets to match. If the character sets are the same, all characters are interpreted identically between the two computers. However, if the character sets are different, any extended characters are interpreted differently on the two computers. For example, the character "A" (ASCII value of 65) is interpreted the same for all character sets because it is not an extended character. However, if the character stored is "/" (ASCII value of 246) on the first computer with a character set of code page 437 (U.S. English), the second computer with a character set of code page ISO 8859-1 (Latin 1 or ANSI), interprets the "/" character as the "ö" character (still ASCII value 246).

Note If the computer where the database backup is to be restored does not have the same sort order, Unicode collation, and code page as the backup, is not using a binary sort order, or cannot be reinstalled, then it is not possible to restore the backup. Alternatively, data can be copied between the computers using the Data Transformation Services Import and Export wizards, the bcp utility, or a distributed query as part of an INSERT statement.

See Also 

In This Volume 

Importing and Exporting Data

In Other Volumes 

"Character Set" in Microsoft SQL Server Introduction 

"Sort Order" in Microsoft SQL Server Introduction 

"Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication 

Copying Databases from Earlier Versions

It is not possible to restore a database backup created using Microsoft SQL Server version 6.5 or earlier. Database backups created using SQL Server 6.5 or earlier are in a format incompatible with SQL Server 7.0. To convert a database created using SQL Server 6.5 or earlier to SQL Server 7.0, you can:

• Upgrade SQL Server to 7.0.

  Any databases are upgraded automatically. New backups from the upgraded computer running SQL Server can now be restored into another computer running SQL Server 7.0. 

• Use the Data Transformation Services Import and Export wizards to copy data from one computer running SQL Server directly to another. 

• Use the bcp utility to copy data from a computer running SQL Server 6.5 or earlier to a data file, and then copy the data from the data file into a computer running SQL Server 7.0. 

See Also 

In This Volume 

Importing and Exporting Data 

In Other Volumes 

"Data Transformation Services Wizard" in Microsoft SQL Server Distributed Data Operations and Replication 

"Upgrading from an Earlier Version of SQL Server" in Microsoft SQL Server Introduction 

Copying Databases Between Different Processor Architectures

You can back up and restore databases between servers running Microsoft SQL Server on a different processor architecture. Cross-platform restore is supported from Intel® 32-bit x86 microprocessors to DEC Alpha AXP microprocessors, and from DEC Alpha to Intel x86. For example, you can back up a database from SQL Server running on a computer with a DEC Alpha processor, and restore the database to SQL Server running on a computer with an Intel x86 processor.

Note MIPS and Power PC platforms are no longer supported by SQL Server.

See Also 

In This Volume 

Importing and Exporting Data

In Other Volumes 

"Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication