SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples
Related Tutorials: SQL Server Database Backup Options>
Next Topic: FULL Recovery Model
Page 3 / 16
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.
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.
Note: As mentioned before whenever 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:-
- Full Backup
- Differential backups
- File / FileGroup backups
- Partial backups
- Copy-Only backups
Note: - 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
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.
Note: - 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.
- SQL Server FULL Recovery Model Step by Step Tutorial with Examples
- SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
- Different Types of SQL Server Backups
- Permissions Required to Take Database Backup in SQL Server
- FULL Database Backup in SQL Server Step by Step Tutorial with Examples
Last Updated On: Feb 12, 2014
Please leave your Valuable Comment or Let us know how this article helped you: