This topic describes the benefits of backing up SQL Server databases, basic backup and restore terms, and introduces backup and restore strategies for SQL Server and security considerations for SQL Server backup and restore.
The SQL Server backup
and restore component provides an essential safeguard for protecting critical
data stored in your SQL Server databases. To minimize the risk of catastrophic
data loss, you need to back up your databases to preserve modifications to your
data on a regular basis. 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.
In This Topic:
- Benefits
- Components and Concepts
- Introduction to Backup and Restore Strategies
- Related Tasks
- Benefits
- Backing up your SQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss.
- With valid backups of a database, you can recover your
data 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.
- 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 AlwaysOn Availability Groups or database mirroring, and archiving.
back up [verb]
Copies the data or log records from a SQL
Server database or its transaction log to a backup device, such as a disk, to
create a data backup or log backup.
backup [noun]
A copy of data that can be used to restore and
recover the data after a failure. Backups of a database can also be used to
restore a copy the database to a new location.
backup device
A disk or tape device to which SQL Server
backups are written and from which they can be restored.
backup media
One or more tapes or disk files to which one
or more backup have been written.
data backup
A backup of data in a complete database (a
database backup), a partial database ( a partial backup), or a set of data
files or filegroups (a file backup).
database backup
A backup of a database. Full database backups
represent the whole database at the time the backup finished. Differential
database backups contain only changes made to the database since its most
recent full database backup.
differential backup
A data backup that is based on the latest full
backup of a complete or partial database or a set of data files or filegroups
(the differential base) and that contains only the data that has changed since
that base.
full backup
A data backup that contains all the data in a
specific database or set of filegroups or files, and also enough log to allow
for recovering that data.
log backup
A backup of transaction logs that includes all
log records that were not backed up in a previous log backup. (full recovery
model)
recover
To return a database to a stable and
consistent state.
recovery
A phase of database startup or of a restore
with recovery that brings the database into a transaction-consistent state.
recovery model
A database property that controls transaction
log maintenance on a database. Three recovery models exist: simple, full, and
bulk-logged. The recovery model of database determines its backup and restore
requirements.
restore
A multi-phase process that copies all the data
and log pages from a specified SQL Server backup to a specified database, and
then rolls forward all the transactions that are logged in the backup by
applying logged changes to bring the data forward in time.
Backup and restore
operations occur within the context of a recovery model. A recovery model is a
database property that controls how the transaction log is managed. Also, the
recovery model of a database determines what types of backups and what restore
scenarios are supported for the database. Typically a database uses either the
simple recovery model or the full recovery model. The full recovery model can
be supplemented by switching to the bulk-logged recovery model before bulk
operations. For an introduction to these recovery models and how they affect
transaction log management, see The Transaction Log (SQL
Server).
The best choice of
recovery model for the database depends on your business requirements. To avoid
transaction log management and simplify backup and restore, use the simple
recovery model. To minimize work-loss exposure, at the cost of administrative
overhead, use the full recovery model. For information about the effect of
recovery models on backup and restore, see Backup Overview (SQL Server).
Backing up and
restoring data must be customized to a particular environment and must work
with the available resources. Therefore, a reliable use of backup and restore
for recovery requires a backup and restore strategy. A well-designed backup and
restore strategy maximizes data availability and minimizes data loss, while
considering your particular business requirements.
A backup and restore
strategy contains a backup portion and a restore portion. The backup part of
the strategy defines the type and frequency of backups, the nature and speed of
the hardware that is required for them, how backups are to be tested, and where
and how backup media is to be stored (including security considerations). The
restore part of the strategy defines who is responsible for performing restores
and how restores should be performed to meet your goals for availability of the
database and for minimizing data loss. We recommend that you document your
backup and restore procedures and keep a copy of the documentation in your run
book.
Designing an effective
backup and restore strategy requires careful planning, implementation, and
testing. Testing is required. You do not have a backup strategy until you have
successfully restored backups in all the combinations that are included in your
restore strategy. You must consider a variety of factors. These include the
following:
- The production goals of your organization for the databases, especially the requirements for availability and protection of data from loss.
- The nature of each of your databases: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.
- Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.
Impact of the Recovery
Model on Backup and Restore
Backup and restore
operations occur within the context of a recovery model. A recovery model is a
database property that controls how the transaction log is managed. Also, the
recovery model of a database determines what types of backups and what restore
scenarios are supported for the database. Typically a database uses either the
simple recovery model or the full recovery model. The full recovery model can
be supplemented by switching to the bulk-logged recovery model before bulk
operations. For an introduction to these recovery models and how they affect
transaction log management, see The Transaction Log (SQL
Server).
The best choice of
recovery model for the database depends on your business requirements. To avoid
transaction log management and simplify backup and restore, use the simple
recovery model. To minimize work-loss exposure, at the cost of administrative
overhead, use the full recovery model. For information about the effect of
recovery models on backup and restore, see Backup Overview (SQL Server).
Design the Backup
Strategy
After you have
selected a recovery model that meets your business requirements for a specific
database, you have to plan and implement a corresponding backup strategy. The
optimal backup strategy depends on a variety of factors, of which the following
are especially significant:
- How many hours a day do applications have to access the database?
If there is a predictable off-peak period, we
recommend that you schedule full database backups for that period.
- How frequently are changes and updates likely to occur?
If changes are frequent, consider the
following:
- Under the simple recovery model, consider scheduling differential backups between full database backups. A differential backup captures only the changes since the last full database backup.
- Under the full recovery model, you should schedule frequent log backups. Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.
- Are changes likely to occur in only a small part of the database or in a large part of the database?
For a large database in which changes are
concentrated in a part of the files or filegroups, partial backups and or file
backups can be useful. For more information, see Partial Backups (SQL Server) and Full File Backups (SQL Server).
- How much disk space will a full database backup require?
For more information, see Estimating the Size of a Full
Database Backup, later in this section.
Estimate the Size of a
Full Database Backup
Before you implement a
backup and restore strategy, you should estimate how much disk space a full
database backup will use. The backup operation copies the data in the database
to the backup file. The backup contains only the actual data in the database
and not any unused space. Therefore, the backup is usually smaller than the
database itself. You can estimate the size of a full database backup by using
the sp_spaceused system stored procedure. For more information,
see sp_spaceused (Transact-SQL).
Schedule Backups
Performing a backup
operation has minimal effect on transactions that are running; therefore,
backup operations can be run during regular operations. You can perform a SQL
Server backup with minimal effect on After you decide what types of backups you
require and how frequently you have to perform each type, we recommend that you
schedule regular backups as part of a database maintenance plan for the
database. For information about maintenance plans and how to create them for
database backups and log backups, see Use the Maintenance Plan Wizard.
Test Your Backups
You do not have a
restore strategy until you have tested your backups. It is very important to
thoroughly test your backup strategy for each of your databases by restoring a
copy of the database onto a test system. You must test restoring every type of
backup that you intend to use.
We recommend that you
maintain an operations manual for each database. This operations manual should
document the location of the backups, backup device names (if any), and the
amount of time that is required to restore the test backups.
No comments:
Post a Comment