Backing Up and Restoring Databases in SQL Server
Microsoft SQL Server enables you to back up and restore your databases. The SQL Server backup and restore component provides an important safeguard for protecting critical data stored in SQL Server databases. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare 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
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.Copy-Only Backups
Describes copy-only backups. Copy-only backups are isolated backups that are intended as special-purpose additions to regularly scheduled conventional backups.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 (SQL Server)
Contains information about protecting backups and other security considerations for backup and restore.Restore and Recovery Overview (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.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.
See Also