SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples

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

Because transactional log space is reused and transactional log backup is not permitted, Point in Time recovery is not possible when the SIMPLE recovery model is used. As a result, you must rely on the most recent Full Database Backup and subsequent Differential backups to recover the database. As a result, this recovery model is best suited for user databases running in Development or Testing environments, or for a database configured as read-only.

During the following scenarios, SQL Server will automatically truncate the transactional log file in the Simple recovery model.

• 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 be unable to perform transaction log backups. This is by design on Microsoft’s part.

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

• The database is currently in development, testing, or quality assurance environments.
• Data is not critical to the organization and can be recreated quickly and easily.
• Data rarely changes or remains relatively static over long periods of time.
• You are fine with losing any/all transactions since the last successful backup of the database.
• Not eager to achieve database Point In Time recovery

When a database is in SIMPLE Recovery Model, the following backups can be performed:

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

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.