Recovery Models (SQL Server) - SQL Server (2024)

Table of Contents
In this article Recovery Model Overview Related Tasks See Also Feedback Feedback In this article FAQs
  • Article

Applies to: Recovery Models (SQL Server) - SQL Server (1) SQL Server

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

In this Topic:

  • Recovery Model Overview

  • Related Tasks

Recovery Model Overview

The following table summarizes the three recovery models.

Recovery modelDescriptionWork loss exposureRecover to point in time?
SimpleNo log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).

Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in the simple recovery model:

-Log shipping

-Always On or Database mirroring

-Media recovery without data loss

-Point-in-time restores

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.Can recover only to the end of a backup. For more information, see Complete Database Restores (Simple Recovery Model).

For a more in depth explanation of the Simple recovery model, see SQL Server Simple Recovery Model provided by the folks at MSSQLTips!

FullRequires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error). For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone.

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For information about using log backups to restore to the point of failure, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

Note: If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases. For more information, see Recovery of Related Databases That Contain Marked Transaction.

Bulk loggedRequires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For information about operations that can be minimally logged, see The Transaction Log (SQL Server).

Log backups may be of a significant size because the minimally-logged operations are captured in the log backup. For information about database backups under the bulk-logged recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model).

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.

Can recover to the end of any backup. Point-in-time recovery is not supported.

Related Tasks

See Also

backupset (Transact-SQL)
sys.databases (Transact-SQL)
ALTER DATABASE SET Options (Transact-SQL)
Back Up and Restore of SQL Server Databases
The Transaction Log (SQL Server)
Automated Administration Tasks (SQL Server Agent)
Restore and Recovery Overview (SQL Server)

See Also
Fast SQL learning tipsLearn SQL: What You Should Know About SQL Before Getting Started in 2022 | Berkeley Coding Boot CampSQL Developer Salary Guide: What to Expect in 202413 Basic SQL Commands and Queries Programmers Should Know

Feedback

Was this page helpful?

Feedback

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.

Submit and view feedback for

Recovery Models (SQL Server) - SQL Server (2024)

FAQs

What are the 3 recovery models in SQL Server? ›

In SQL Server, there are three main recovery models: Simple, Full, and Bulk-Logged.

View Details ›
What are the SQL Server recovery model System databases? ›

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged.

See Details ›
What is the default recovery model in SQL Server? ›

The database file stores the physical data added to the database, and the transaction log keeps records of database modifications. By default, the recovery method of a SQL database is set to 'Full'.

Discover More Details ›
How to find recovery model in SQL Server? ›

Use SQL Server Management Studio

Right-click the database, and then select Properties, which opens the Database Properties dialog box. In the Select a page pane, select Options. The current recovery model is displayed in the Recovery model list box.

Find Out More ›
What are the three main recovery techniques in database? ›

  • Database Recovery techniques.
  • Undoing – If a transaction crashes, then the recovery manager may undo.
  • Deferred update – This technique does not physically update the database on disk.
  • Immediate update – In the immediate update, the database may be updated by.

Discover More Details ›
What are the three phases of backup and recovery in SQL Server? ›

To perform a database restore, the Database Engine executes three steps:
  • Creates the database and transaction log files if they don't already exist.
  • Copies all the data, log, and index pages from the backup media of a database to the database files.
  • Applies the transaction log, in what is known as the recovery process.
Mar 3, 2023

Find Out More ›
What are the 4 system databases in SQL Server? ›

In this article
System databaseBackups required?Recovery model
modelYesUser configurable1
msdbYesSimple (default)
Resource Database (RDB)NoNone
tempdbNoSimple
2 more rows
Nov 21, 2023

Find Out More ›
How many types of recovery techniques are in database? ›

Commit/Redo Recovery Technique

The system uses the log records to reapply the changes made by the transaction and restore the database to its most recent consistent state. In addition to these two techniques, there is also a third technique called checkpoint recovery.

Get More Info ›
How do I know if my database is in recovery mode? ›

If the state_desc column shows 'RECOVERY_PENDING' or 'RECOVERY_IN_PROGRESS', it means that the database is still in recovery mode.

Find Out More ›
How do I change SQL Server recovery model to simple? ›

How do you set the database recovery model in SQL Server to...
  1. Right-click on the Database name and select Properties.
  2. On the Options tab identify the drop-down list: Recovery Model.
  3. Select Simple from the list.
  4. Apply the changes.

Tell Me More ›

What is the difference between restore and recovery in SQL Server? ›

Restoring involves copying backup files from a secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. Recovery is the process of applying redo logs to the database to roll it forward.

Continue Reading ›
What is the difference between restore with recovery and no recovery in SQL Server? ›

With NORECOVERY Option

state to prevent users from accessing the database unless additional backups are restored. Restore with NORECOVERY is used for restoring each database backup except the last. The last backup can be restored using the RECOVERY option to bring the db online for usage.

Learn More ›
What is the simple recovery model log file in SQL Server? ›

Simple Recovery Model

After writing the SQL Server transaction log records from the buffer cache to the SQL transaction log file in the disk drive, the log is truncated when a checkpoint operation is performed to commit the transactions. For more information about the checkpoints, check the Database Checkpoints.

See More ›
How to recover corrupted model database in SQL Server? ›

How To Repair a Corrupted SQL Database
  1. Step 1 – Attempt Repair with SQL Server Management Studio (Optional) ...
  2. Step 2 – Choose a Good Database Repair Tool (Recommended) ...
  3. Step 3 – Download Your SQL Repair Tool. ...
  4. Step 4 – Run Your SQL Database Repair Tool. ...
  5. Step 5 – Scan the Corrupted SQL Database.

View More ›
What is the pseudo simple recovery model? ›

The database behavior during the log chain break scenario is called the pseudo-simple recovery model. In this model, the database behaves similar to a simple SQL Server Recovery Model and truncates the logs once the transaction is committed.

Keep Reading ›
What are the three 3 major categories of SQL? ›

Data Definition Language (DDL) Statements. Data Manipulation Language (DML) Statements. Transaction Control Statements.

Read On ›
What are the different types of data recovery? ›

The four types of data recovery are: 1) logical data recovery, which addresses issues like file corruption, formatting, and accidental deletion; 2) physical data recovery, which involves repairing hardware issues like damaged drives or broken components; 3) remote data recovery, which is the process of recovering data ...

Get More Info Here ›
What are the different types of failover in SQL? ›

Three forms of failover exist: automatic failover (without data loss), planned manual failover (without data loss), and forced manual failover (with possible data loss), typically called forced failover. Automatic and planned manual failover preserve all your data.

Get More Info ›
What is database recovery and its types? ›

Database recovery is the process of restoring the database to a correct (consistent) state in the event of a failure. In other words, it is the process of restoring the database to the most recent consistent state that existed shortly before the time of system failure.

Read On ›
Top Articles
How to Remove Header and Footer in Excel (6 Methods) - ExcelDemy
TP-Link Deco S4 Review - Great Range & Mediocre Speed
Directory Alpha Search: l | Ulster County Regional Chamber Of Commerce
Ulster Chamber retools to help stressed small businesses through the pandemic
Latest Posts
Garamsythe Waterway - Final Fantasy XII The Zodiac Age
How to Optimize Google My Business for Your Restaurant: Must-Know Tips
Article information

Author: Dan Stracke

Last Updated:

Views: 5829

Rating: 4.2 / 5 (63 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Dan Stracke

Birthday: 1992-08-25

Address: 2253 Brown Springs, East Alla, OH 38634-0309

Phone: +398735162064

Job: Investor Government Associate

Hobby: Shopping, LARPing, Scrapbooking, Surfing, Slacklining, Dance, Glassblowing

Introduction: My name is Dan Stracke, I am a homely, gleaming, glamorous, inquisitive, homely, gorgeous, light person who loves writing and wants to share my knowledge and understanding with you.