Friday, 1 September 2017

Set Database To Simple Recovery Model in SQL Server

“Simple” as the name says, is an option in SQL Server to set the database to simple recovery model in order to recover a database from disasters. Simple Recovery Model will trap simple and minute transactions happening in the database, since the transaction will be overwritten on the existing data once set as “Simple”. Though the database is in simple recovery model that is rewriting with new transactions, point-in-time recovery is not possible. However, in case of recovery, either the full backups or differential backups that are latest with respect to date can be used. 

 Some of the reasons to go for Simple Recovery Model:
  1.  Data present is simple and non-critical
  2.  Simple mode is usually used for testing or development environment
  3. Higher performance bulk copy operation.
  4. Space occupied in the disk is drastically less
T/SQL script to set the database to simple recovery model


Follow the below steps in SQL Server Management Studio graphic user interface:

1. Right on the respective database and click “properties”
2. Select “options” from “Select a page” side bar
3. A drop is seen under “Recovery Model”. Select “Simple” from the drop down.
4. Click “OK” to set the database to simple recovery model

The screenshot for the same tagged below: