• Skip to content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

Header Right

  • Home
  • Blog
  • Services
  • Pocket DBA™
  • Speaking
  • Contact
  • About

SQL 101 – Recovery models in simple terms

January 10, 2008 by Kevin3NF Leave a Comment

A recovery model is simply a choice made on each database that determines how much data you can recover if your db goes “toes up”.

If you do not backup your database, you have chosen recovery model “terminated” or “update resume”

The 3 that are offered by Microsoft are:

  • Bulk-logged (rarely seen, and generally more advanced, so I’m skipping it)
  • Simple
  • Full

Simple vs. Full is very simply a matter of how much data can you afford to lose if the database is lost and you are forced to restore from your backups.

Simple recovery model: does not allow backing up the transaction logs, therefore you cannot restore any of the data in them. You can only restore to the point of the Full backup and any one Differential you may have.

Full recovery model: You can restore from t-log backups (assuming you have them), right up to a specific point in time. Reduced data loss, assuming the backup files are valid.

When to use:

Simple: When you do not care about the data, such as in a Development or Test environment where you can regenerate from another source. Also useful in a static, read-only database (assuming you have a full backup).

Full: Pretty much any live production database that has Inserts, Updates and Deletes happening.

Switching from one to the other:

Simple to Full: Immediately take a Full or Differential backup

Full to Simple: No specific action required, other than verifying regular data backups are in place and working.

Maintenance plan considerations:

If you have both Simple and Full recovery model databases in your SQL instance, and you create a Maintenance Plan to back up data and logs, you may run into an issue (at least in SQL 2000) where the automated deletion of old t-log backups is failing. Make two plans: one for Full and one for Simple. I have no idea if this issue still presents in SQL 2005.

I hope this is clear…please feel free to comment.

Kevin3NF

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: Accidental DBA, backup, Beginner, EntryLevel, SQL

About Kevin3NF

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • Index
  • Install
  • IRL
  • Management
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • PowerShell
  • Puzzle
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • vendor code modify
  • video
  • vNext

Copyright © 2019 · Genesis Sample on Genesis Framework · WordPress · Log in

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.