Backing Up and Restoring Databases in SQL Server

SQL Server 2005 Books Online (November 2008)

Microsoft SQL Server 2005 provides high-performance backup and restore capability. The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in SQL Server databases. Implementing a well-planned backup and restore strategy helps protect databases against data loss because of damage caused by a variety of failures. Testing your strategy by restoring a set of backups and recovering your database prepares you to respond effectively to a disaster.

A copy of data that can be used to restore and recover the data is called a backup. Backups let you restore data after a failure. With good backups, you can recover from many failures, such as:

Media failure.
User errors, for example, dropping a table by mistake.
Hardware failures, for example, a damaged disk drive or permanent loss of a server.
Natural disasters.

Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up database mirroring, and archiving.

In This Section

Topic	Description
Backup Overview (SQL Server)	Introduces the types of backups, and also provides a description of the restrictions on backups.
Backup Under the Simple Recovery Model	Describes a sample backup strategy and minimizing work-loss exposure under the simple recovery model.
Backup Under the Full Recovery Model	Describes a sample backup strategy and minimizing work-loss exposure under the full recovery model.
Backup Under the Bulk-Logged Recovery Model	Contains information that is unique to backing up data under the bulk-logged recovery model, and the impact on backup of changing a database to read-only after bulk-logged transactions.
Introduction to Backup and Restore Strategies in SQL Server	Helps you analyze and refine your data availability requirements.
Creating Full and Differential Backups of a SQL Server Database	Contains information about differential bases, how differential backups work, and how to create the various types of data and differential backups: database backups, partial and differential partial backups, and file and filegroup backups.
Working with Transaction Log Backups	Contains information about how to back up and apply transaction logs. This topic is relevant only for databases that use the full/bulk-logged recovery model.
Working with Backup Media in SQL Server	Contains information about how SQL Server works with backup devices, using backup media, viewing information about and verifying SQL Server backups, detecting and coping with media errors, and using mirrored backup media sets.
Security Considerations for Backup and Restore	Contains information about protecting backups and other security considerations for backup and restore.
Overview of Restore and Recovery in SQL Server	Contains an introduction to restore scenarios as they are supported under the simple recovery model and the full/bulk-logged recovery models, as well as a description of how restore and backup recovery work, and overviews of the restore system tables and the RESTORE statement.
Implementing Restore Scenarios for SQL Server Databases	Contains an introduction to the basic concepts of restoring and recovering backups and how they work, a brief overview of restore operations, and information about how to implement the various restore scenarios.
Working with Restore Sequences for SQL Server Databases	Contains information about how to combine multiple RESTORE statements to restore a sequence of backups from a single database and recover the database. Provides information about what occurs when files or file groups have been added, dropped, or have had their names changed since they were backed up, and also describes optimizations that can be used to minimize or eliminate unnecessary rolling forward during a file restore operation.
Considerations for Backing Up and Restoring System Databases	Contains information about which system databases require backing up and which systems do not, and about how to back up and restore the master, msdb, and model databases.
Using Marked Transactions (Full Recovery Model)	Describes how to use marked transactions in two or more related, full-recovery model databases that must be kept logically consistent. By creating marked transactions, you can retain consistent between them during restore and recovery.
Disaster Recovery	Contains information about how to plan for and recover from a disaster.
Optimizing Backup and Restore Performance in SQL Server	Contains information about how to optimize performance for data and differential backups and transaction log backups, for restore operations, and for backup devices.
Understanding Recovery Performance in SQL Server	Contains information about performance during crash recovery and about how to improve performance for recovering restored data.
Backup and Restore in Large Mission-Critical Environments	Contains a description of several methods that you can use to increase the speed of backup and restore operations to minimize the effect on users during both operations.
Backup and Restore APIs for Independent Software Vendors	Contains an introduction to APIs that let an independent software vendor (ISV) integrate SQL Server backup and restore into its products.

Note:
For information about how to schedule backup jobs for a database, see Maintenance Plan Wizard.

Concepts

Other Resources

Help and Information