Backing Up and Restoring Databases in SQL Server
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 |
|---|---|
Introduces the types of backups, and also provides a description of the restrictions on backups. |
|
Describes a sample backup strategy and minimizing work-loss exposure under the simple recovery model. |
|
Describes a sample backup strategy and minimizing work-loss exposure under the full 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. |
|
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. |
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. |
|
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. |
|
Contains information about protecting backups and other security considerations for backup and restore. |
|
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. |
|
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. |
|
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. |
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. |
|
Contains information about how to plan for and recover from a disaster. |
|
Contains information about how to optimize performance for data and differential backups and transaction log backups, for restore operations, and for backup devices. |
|
Contains information about performance during crash recovery and about how to improve performance for recovering restored data. |
|
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. |
|
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.
See Also
Concepts
Backing Up and Restoring Federated Database Servers
Overview of the Recovery Models
Transactions (Database Engine)
Other Resources
Backing Up and Restoring an Analysis Services Database
Backing Up and Restoring Full-Text Catalogs
Backing Up and Restoring Replicated Databases
Databases (Database Engine)