SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples

Introduction

SIMPLE recovery model as the name suggests it is the most basic recovery model which is available in SQL Server. In this recovery model every transaction is written to the transactional log file and once the transaction is completed successfully the data gets written to data file; SQL Server will automatically clear the space used by the transaction within transaction log file for newer transactions.

Since transactional log space is reused and transactional log backup is not allowed there is no possibility to achieve Point in Time recovery when SIMPLE recovery model is used. Hence you will have to rely on most recent Full Database Backup and the subsequent Differential backups to recovery the database. Hence, this recovery model is best suited for user databases which are running in Development or Testing environments or a database which is configured as read-only.

This is Part 3 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

In Simple recovery model SQL Server will automatically truncate the transactional log file during the following scenarios.

• Whenever the transaction log file is 70% full
• A CHECKPOINT command is executed internally or it is executed manually
• Whenever the active portion of the transaction log file exceeds the size that SQL Server could recover within the time specified in recovery interval (min) parameter using SP_CONFIGURE.

When  a database is configured to use a Simple Recovery Model you will not be able to perform the transaction log backup this is by design from Microsoft.

When to choose SIMPLE Recovery Model for a database in SQL Server?

• Database is currently running in Development, Testing or Quality Assurance environments
• Data is not critical for the organization and can be recreated very easily and in less time
• Data rarely changes or remains more or less static for a major time period
• You are fine losing any/all the transactions since the last time database was successfully backed up
• Not keen to achieve Point In Time recovery of a database

Different types of backups which can be performed when a database is in SIMPLE Recovery Model are:-

• Full Backup
• Differential backups
• File / FileGroup backups
• Partial backups
• Copy-Only backups

A very common misunderstanding is that when a database is configured to use Simple recovery model nothing is logged. However this is not at all true. In Simple recovery model everything is logged but Point in Time is not possible as it’s not possible to take the transaction log backup. At the same time the Bulk operations are logged minimally as like in Bulk Logged recovery model.

How to Change Database Recovery Model to SIMPLE Using TSQL Command

ALTER DATABASE AdventureWorksDW SET RECOVERY SIMPLE
GO

How to Change Database Recovery Model to SIMPLE Using SSMS

• Connect to SQL Server Instance using SQL Server Management Studio
• Expand Database Node and then right click the user Database and select Properties from the drop down menu
• Click Options Page on the right side pane as highlighted in the below snippet
• Under Recovery Model choose SIMPLE and click OK to save.

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.