SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

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

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.
How to change database recovery model to SIMPLE Recovery Model in SQL Server
How to change database recovery model to SIMPLE Recovery Model in SQL Server

Changing the recovery model of a database will break the backup chain. Hence, as a Best Practice one should immediately take the full backup of the database after changing the recovery model.

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

Previous Page.. Begin Tutorial.. Next Page..

Related Articles

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips