SA0141 : Database is using Simple Recovery Model

This issue arises when databases in a SQL Server environment are configured with the Simple Recovery Model. This model might seem attractive due to its lower maintenance and reduced storage for transaction logs, but it limits the ability to perform point-in-time data recovery.

For example:

1-- Check the recovery model of a database
2SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';

Using the Simple Recovery Model can result in significant data loss because:

• It prevents point-in-time recovery, meaning data changes made between full backups cannot be recovered if a failure occurs.

• In a production environment, not keeping frequent transaction log backups can lead to an inability to fully restore data, resulting in more extensive recovery time and potential loss of business-critical information.

To minimize data loss during disaster recovery, change the database recovery model to Full Recovery Model.

Follow these steps to address the issue:

1. Identify the current recovery model of your databases using the following query:

2. Switch from Simple Recovery Model to Full Recovery Model using the ALTER DATABASE statement.

3. Start taking regular transaction log backups to ensure point-in-time recovery is possible.

For example:

1-- Check the current recovery model of a specific database
2SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';
3
4-- Change the recovery model to Full
5ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
6
7-- Example of a transaction log backup
8BACKUP LOG YourDatabaseName TO DISK = 'C:BackupsYourDatabaseName_LogBackup.bak';

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Maintenance Rules, Bugs

Recovery Models

Choosing the Recovery Model

Other Resources