Bài giảng Cơ sở dữ liệu nâng cao - Chapter 2: Disaster recovery. Chương này trình bày một số nội dung sau: Overview about database backup and recovery, backup & restore strategy, disaster recovery planning,... Mời các bạn cùng tham khảo.
AMBIENT/
Chủ đề:
Nội dung Text: Bài giảng Cơ sở dữ liệu nâng cao - Chapter 2: Disaster recovery
- 4/5/17 1
Chapter 2
Disaster Recovery
- 4/5/17 2
Agenda
• Recovery technique
• Disaster recovery planning
- 4/5/17 3
Overview about database backup and recovery
• Data is a critical asset for an organization to maintain
information about its customers, inventory, purchases,
financials, and products.
• A variety of failures can bring down your database.
Anything from a user error to a natural disaster could take
your database offline. Your backup and recovery plan
needs to account for the possibility of these failures and
more.
- 4/5/17 4
Overview about database backup and recovery
• Types of failure:
• 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.
- 4/5/17 5
Overview about database backup and recovery
• The purpose of creating SQL Server backups is
to enable you to recover a damaged database.
• A well-designed backup and restore strategy
maximizes data availability and minimizes data
loss, while considering your particular business
requirements.
- 4/5/17 6
Backup & restore strategy
• The backup 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
• Where and how backup media is to be stored (including security
considerations)
- 4/5/17 7
Backup & restore strategy
• The restore strategy defines:
• Who is responsible for performing restores
• How restores should be performed to meet the goals
• for availability of the database
• for minimizing data loss
• Designing an effective backup and restore strategy
requires careful planning, implementation, and testing
- 4/5/17 8
Backup method
• Database Engine provides four different
backup methods:
• Full database backup
• Diferential backup
• Transaction log backup
• File (or filegroup) backup
- 4/5/17 9
Backup method
• Full database backup:
• Capture the state of the database at the time the
backup is started.
• During the full database backup, the system copies
the data, the schema of all tables of the database
and the corresponding file structures.
• Even all uncommitted transactions in the
transaction log are written to the backup media.
- 4/5/17 10
Backup method
• Differential Backup
• Create a copy of only the parts of the database that
have changed since the last full database backup.
• The advantage of a differential backup is speed. It
minimizes the time required to back up a database.
- 4/5/17 11
Backup method
• Transaction Log Backup:
• Consider only the changes recorded in the log. This
form of backup is therefore not based on physical
parts (pages) of the database, but rather on logical
operations - that is, changes executed using the
DML statements INSERT, UPDATE, and DELETE.
Again, because the amount of data is smaller, this
process can be performed significantly quicker than
a full database backup and quicker than a
differential backup.
- 4/5/17 12
Backup method
• File or Filegroup Backup:
• Back up specific database files (or filegroups)
instead of the entire database.
• Individual files (or filegroups) can be restored from
a database backup, allowing recovery from a failure
that affects only a small subset of the database
files.
• You can use either a database backup or a
filegroup backup to restore individual files or
filegroups. This means that you can use database
and transaction log backups as your backup
- 4/5/17 13
Recovery model
• Designed to control transaction log maintenance
• Every recovery model lets you back up a whole or partial
SQL Server database or individual files or filegroups of the
database. Table-level backups cannot be created.
• SQL Server has three possible recovery models:
• Simple
• Full
• Bulk Logged
• However, only Simple and Full recovery models are
meant for regular use. Bulk Logged,is designed to be an
adjunct to the Full recovery model.
- 4/5/17 14
Recovery model
• Simple Recovery model
• Minimize administrative overhead for the transaction
log, because the transaction log is not backed up
• Data is recoverable only to the most recent backup of
the lost data
- 4/5/17 15
Recovery model
• Full Recovery model
• All activities that affect the database are logged in the transaction
log.
• The advantage of this full logging is that every transaction can be
recovered in the event of a failure.
- 4/5/17 16
Recovery model
• Bulk log recovery model
• Log most bulk operations.
• For certain large-scale bulk operations such as bulk import or index
creation, switching temporarily to the bulk-logged recovery model
increases performance and reduces log space consumption.
- 4/5/17 17
Backup Types
• The scope of a backup of data (a data backup) can be a
whole database, a partial database, or a set of files or
filegroups.
• SQL Server supports 3 backup types:
• Full backup
• Differential backup
• Transaction log backup
- 4/5/17 18
Backup Types
• Full backup:
• Contain all the data in a specific database or set of filegroups or
files, and also enough log to allow for recovering that data.
• Differential backup:
• based on the latest full backup of the data.
• Contain only the data that has changed since the differential base.
• Transaction log backup:
• In Full or Bulk-Logged recovery models
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
