Transcript F08vl03.FH8

Chapter Overview
•
Understanding Data Restoration Issues
•
Understanding the Types of Database Backups
• Understanding the Restoration Process
1
Data Restoration Goals
•
Provide fault tolerance where possible.
•
Monitor for problems before they occur.
• Plan for all forms of disasters.
•
Determine acceptable data restoration times for
each type of disaster.
•
Quickly verify proper database functionality.
2
Types of Data Backups
Full database backup
Differential database backup
Filegroup (or file) backup
Differential filegroup (or file)
backup
Transaction log backup
Snapshot backup technology
3
Types of Data Restorations
Full database restore
Filegroup (or file) restore
with full recovery
Recovery to a point in time
Recovery to a named
transaction
4
Databases to Back Up
•
Applicable user databases
•
The master system database
• The msdb system database
•
The model system database, if this database
has been customized
•
The distribution system database, if replication
is being used
5
Components of a Data
Restoration Plan
•
Document the entire system.
•
Document the backup plan.
• Automate backups.
•
Test the backup plan.
•
Store backup media securely.
•
Determine how long to retain backups.
6
Full Database Backups
•
Complete copy of a database as of the end of
the backup.
• Online backup with only a small impact on
performance.
•
Either a full database backup or a backup of all
filegroups is the starting point for a database
restoration.
•
Perform after initial population of the database
and periodically thereafter.
7
Differential Database
Backups
•
Complete copy of all database changes since the last
full database backup
•
•
Records only the most recent change
•
Generally uses significantly less space than a full
database backup
•
Use with medium to large databases between full
database backups
Length of time required proportional to the number
of modified extents
8
File and Filegroup Backups
F08vl01.FH8
9
Differential File and
Filegroup Backups
•
Complete copy of all changes in file or filegroup
since the last full file or filegroup backup
• Conceptually the same as differential database
backups
•
Use to back up frequently changing tables in
between full file or filegroup backups
10
Transaction Log Backups
•
Sequential record of changes since the last
transaction log backup.
•
At the end of a transaction log backup, all VLFs
containing inactive portions of the transaction log
are truncated.
•
Time and space required depends on frequency of
changes to the database and frequency of
transaction log backups.
•
Frequency of transaction log backups depends on
several factors.
11
Full Database Backup
Strategy
12
Full Database and Transaction
Log Backup Strategy
F08vl03.FH8
13
Differential Backup Strategy
14
Database File or Filegroup
Backup Strategy
15
Automatic Recovery
Process
16
Recovery Process Using a
Full Database Backup
17
Recovery Process Using
Filegroup (or File) Backups
18
Restoration and Recovery
to Another Point in Time
19
Recovery Scenarios
•
A user database data disk failure
•
A user database transaction log disk failure
• A master database disk failure
20
Chapter Summary
•
Develop, document, and test a data restoration
strategy that anticipates all forms of disasters.
•
Perform full database backups regularly, and use
differential database backups between full backups
to reduce recovery time.
•
Perform frequent transaction log backups to provide
redundancy and to truncate the transaction log.
•
Use the Full Recovery model if point-in-time recovery
or recovery to a specific mark is needed.
21