Backup Recovery Recovery Models Full Recovery Model All

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 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.