Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Simple Recovery Model



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.  

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





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

 


Learn more about SQL Server tools