Understanding database recovery models in SQL Server

A recovery model is a database configuration option that determines the type of backup that one could perform and provides the ability to restore the data or recover it from a failure.

The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation.

Types of recovery models

All SQL Server database backup, restore, and recovery operations are based on one of three available recovery models:

  • FULL
  • BULK_Logged


The SIMPLE recovery model is the simplest among the available models. It supports full, differential, and file level backups. Transaction log backups are not supported. The log space is reused whenever the SQL Server background process checkpoint operation occurs. The inactive portion of the log file is removed and is made available for reuse.

Point-in-time and page restore are not supported, only the restoration of the secondary read-only file is supported.

Reasons to choose the simple database recovery model

  1. Most suited for Development and Test databases
  2. Simple reporting or application database, where data loss is acceptable
  3. The point-of-failure recovery is exclusively for full and differential backups
  4. No administrative overhead

It supports:

  1. Full backup
  2. Differential backup
  3. Copy-Only backup
  4. File backup
  5. Partial backup


Continue readingΒ Understanding database recovery models in SQL Server

Happy Learning!



About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL Server Backup and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s