Database Backup and Recovery Models Explained
Backup & Recovery
Recovery Models
•
Full Recovery Model
–
All activities that affects the database are logged.
•
Bulk-Logged Recovery Model
–
Some actions are logged as having occurred but
individual rows affected are not logged.
•
Simple Recovery Model
–
The inactive portion of the log is truncated every
time a checkpoint is issued.
Full Recovery Model
•
All activities that affects the database are
logged.
•
The transaction log contains a record of all the
modifications to the database.
Bulk-Logged Recovery Model
•
Minimal logging
•
Some actions are logged as having occurred
but individual rows affected are not logged.
•
During next BACKUP LOG event the affected
physical extents are copied to the log backup.
Simple Recovery Model
•
The inactive portion of the log is truncated
every time a checkpoint is issued.
•
Transaction log cannot be backed up and used
for data recovery since it does not have a
complete record of all the transactions that
have modified the database.
Backup Types
•
Full Backup
•
Differential Backup
•
File/Filegroup Backup
•
File/Filegroup with Differential
•
Transaction Log Backup
•
Partial Backup
•
Copy Only Backup
Full Backup
•
Backups up all the data in the database and
records all database file locations.
•
SQL Server logs the beginning of a Full database
backup in the transaction log and then records all
modifications made to the database for the
duration of the backup in the transaction log.
•
The portion of the transaction log that occurred
during the backup is saved to the backup media.
Differential Backup
•
Backups on the data that has changed since
the last Full backup.
•
Includes the portion of the transaction log
that contains database modifications that
occurred during the backup.
File/Filegroup Backup
•
Backup files and filegroups individually.
File/Filegroup with Differential
•
Like Differential
•
Only available if database is in Full or Bulk-
Logged recovery model.
•
Also available if filegroup is marked as Read
Only and database is in Simple Model.
Transaction Log Backup
•
Available in Full or Bulk-Logged Recovery
Models.
•
Three forms:
–
Pure Log Backup
–
Bulk Log Backup
–
Tail Log Backup
Partial Backup
•
Consists of the Primary filegroup, Read Write
filegroups, and any Read only filegroup
specified.
•
Idea is to separate filegroups that can change
for filegroups that cannot change.
Copy Only Backup
•
Creates a backup without affecting the chain
of backups required to restore a database.
•
They are non-logged backups that can be used
outside the maintenance envrionment.
Backup Options
•
Backup Stripe
•
Mirrored Backup
•
Compressed Backup
Backup Strategies
•
Full Backup Only
•
Bull Backup with Differential
•
Full Backup with Transaction Log
•
Full and Differential Backup with Transaction
Log
•
File and Filegroup Backup
•
Filegroup with Differential
•
Partial Backup
Restoring Databases
•
A three phase process
–
Data Copy
•
Data copied
–
Redo Phase
•
Committed transactions are restored from the log
–
Undo Phase
•
Uncommitted transactions are rolled back from the log
Database Restore Preparation
•
Isolate the database by placing it in
SINGLE_USER mode (if it is accessible).
•
Backup up the tail of the transaction log if in
Full or Bulk-Logged recovery model.
•
Gather information about all the backups that
are required to restore the database to the
most recent state.
Explore different database recovery models - Full Recovery, Bulk-Logged Recovery, and Simple Recovery. Learn about backup types like Full Backup, Differential Backup, and File/Filegroup Backup. Understand the importance of transaction logs in data recovery processes.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Recovery Models Full Recovery Model All activities that affects the database are logged. Bulk-Logged Recovery Model Some actions are logged as having occurred but individual rows affected are not logged. Simple Recovery Model The inactive portion of the log is truncated every time a checkpoint is issued.
Full Recovery Model All activities that affects the database are logged. The transaction log contains a record of all the modifications to the database.
Bulk-Logged Recovery Model Minimal logging Some actions are logged as having occurred but individual rows affected are not logged. During next BACKUP LOG event the affected physical extents are copied to the log backup.
Simple Recovery Model The inactive portion of the log is truncated every time a checkpoint is issued. Transaction log cannot be backed up and used for data recovery since it does not have a complete record of all the transactions that have modified the database.
Backup Types Full Backup Differential Backup File/Filegroup Backup File/Filegroup with Differential Transaction Log Backup Partial Backup Copy Only Backup
Full Backup Backups up all the data in the database and records all database file locations. SQL Server logs the beginning of a Full database backup in the transaction log and then records all modifications made to the database for the duration of the backup in the transaction log. The portion of the transaction log that occurred during the backup is saved to the backup media.
Differential Backup Backups on the data that has changed since the last Full backup. Includes the portion of the transaction log that contains database modifications that occurred during the backup.
File/Filegroup Backup Backup files and filegroups individually.
File/Filegroup with Differential Like Differential Only available if database is in Full or Bulk- Logged recovery model. Also available if filegroup is marked as Read Only and database is in Simple Model.
Transaction Log Backup Available in Full or Bulk-Logged Recovery Models. Three forms: Pure Log Backup Bulk Log Backup Tail Log Backup
Partial Backup Consists of the Primary filegroup, Read Write filegroups, and any Read only filegroup specified. Idea is to separate filegroups that can change for filegroups that cannot change.
Copy Only Backup Creates a backup without affecting the chain of backups required to restore a database. They are non-logged backups that can be used outside the maintenance envrionment.
Backup Options Backup Stripe Mirrored Backup Compressed Backup
Backup Strategies Full Backup Only Bull Backup with Differential Full Backup with Transaction Log Full and Differential Backup with Transaction Log File and Filegroup Backup Filegroup with Differential Partial Backup
Restoring Databases A three phase process Data Copy Data copied Redo Phase Committed transactions are restored from the log Undo Phase Uncommitted transactions are rolled back from the log
Database Restore Preparation Isolate the database by placing it in SINGLE_USER mode (if it is accessible). Backup up the tail of the transaction log if in Full or Bulk-Logged recovery model. Gather information about all the backups that are required to restore the database to the most recent state.
