SQL Server Simple Recovery Model


MENU
By:
Overview

The "Simple" recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server.  With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup).  With this recovery model you are exposed to any failures since the last backup completed, because you will only be able to restore the data to the point when the backup occured.

Explanation

The "Simple" recovery model is the most basic recovery model for SQL Server.  Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions.  Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed.  Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.

Here are some reasons why you may choose this recovery model:

  • Your data is not critical and can easily be recreated
  • The database is only used for test or development
  • Data is static and does not change
  • Losing any or all transactions since the last backup is not a problem
  • Data is derived and can easily be recreated

Type of backups you can run when the data is in the "Simple" recovery model:

  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups

Set SQL Server Simple Recovery Model using T-SQL

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Simple" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO

Set SQL Server Simple Recovery Model using Management Studio

  • Right click on database name and select Properties
  • Go to the Options page
  • Under Recovery model select "Simple"
  • Click "OK" to save
change database simple recovery model

<< Previous

Next >>



Comments For This Article




Thursday, July 21, 2022 - 4:53:58 AM - Istvan Voros Back To Top (90293)
Hi Masters,

I have a simple mode database. I insert about 300 KB data in loops, each loops ends with commit. The log file is continuously growing, growing :(

Why? The article says, that "once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions"

Thank you,
Istvan

Thursday, December 16, 2021 - 10:04:04 AM - Greg Robidoux Back To Top (89596)
Hi Zhong,

When the database is in simple recovery mode once the transactions are complete and get written to the data file, that space in the transaction log can be reused so the transaction log doesn't need to keep growing. It is a bit more complex than that, but overall, that is how it works.

-Greg

Wednesday, December 15, 2021 - 10:00:25 PM - zhong steve Back To Top (89592)
when the new log will replace the old log if the database is on the simple mode

Friday, July 16, 2021 - 7:47:21 AM - Greg Robidoux Back To Top (89016)
Hi Tan, the recovery model is just how the database works to save transactions and this impacts what types of backups you can do. See the other pages in this tutorial for how to actually do the backup.

Friday, July 16, 2021 - 4:16:32 AM - Tan Back To Top (89012)
On SSMS, I don't see a simple recovery can use to backup file.

Tuesday, April 10, 2018 - 9:32:27 AM - Greg Robidoux Back To Top (75659)

Hi Umar,

When a transaction is committed to the database the values are written to the data pages, so when the database is backed up it also has these values.  Also, when a full database backup is taken the backup process reads the data file as well as the active portion of the transaction log, so when the restore occurs it will roll back or roll forward any transactions that were in play during the backup.

-Greg

Saturday, April 7, 2018 - 3:13:28 PM - umar waqas Back To Top (75637)

 in Simpler recovery model, as said above transaction log is cleared after transaction is committed or rolled back. qustion is then how database can be restored when transaction log is being after each transaction.

please reply anyone

 

Monday, January 15, 2018 - 11:05:45 AM - PM Back To Top (74956)

 

 I have a database set to simple recovery.  The autogrowth for the log is the default of 10%   If I run the disk usage report I can see that the autogrowth and autoshrink is happeing constantly for the log file, every few seconds.  ,  The data file is 5.8gb

I am just not sure what to set the auto growth to. I know it is too small but would love a recommendation for what this should be set to.

Thank you

Monday, January 1, 2018 - 9:59:50 PM - IG Q Back To Top (74656)

Is there an instance that a database recovery will change to SIMPLE automatically?

I changed my db recovery to FULL but when my maintenance plan got an error, i found out that the recovery that i set to FULL is now back to SIMPLE.

 

Wednesday, February 15, 2017 - 7:41:48 AM - abhishek kumar Back To Top (46359)

 I was searching for a precise and to the point information regarding backups and recovery model. this is just perfect. thank you so much!

 

Tuesday, February 23, 2016 - 2:40:20 AM - Riddhav Back To Top (40752)

can you please help me to make script to set all databases recovery model to simple mode and also purge the log files 

 














get free sql tips
agree to terms