===== [V7] ISSUE: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE (MS SQL transaction log backup) ===== Article ID: 5292
Reviewed: 2018-01-24

Product Version:
AhsayOBM: 7.15.2.0 to 7.x
OS: Windows ATTENTION 1st January, 2022: v7 officially End-of-Life [details] ===== Problem Description ===== When performing a MS SQL transaction log backup (ODBC backup mode), the following warning message is received in the backup report::
No. Type Timestamp Log
* ... ... ...
* info ... [Start] Backing up "SQL_hostname\MSDB\db_name" using "Transaction log" to "temp_path"
* warn YYYY/MM/DD hh:mm:ss [Microsoft][ODBC SQL Server Driver][SQL Server]The statement BACKUP LOG is not
allowed while the recovery model is SIMPLE. Use BACK UP DATABASE or change the
recovery model using ALTER DATABASE.
* ... ... ...
===== Cause ===== The message suggests that recovery model of the database in concern is currently set to SIMPLE. When using the simple recovery model, the log is truncated when periodic checkpoints occur. Only full database and differential database backups are allowed.

The following table summarizes the recovery models and backup types available with each recovery model:
Recovery Model Full Backup Differential Backup Transaction Log Backup
Simple Supported Supported Not Supported
Bulk-Logged Supported Supported Supported
Full Supported Supported Supported
===== Resolution ===== To resolve the issue, modify the recovery model of the database in concern to FULL. Modify the recovery model of your database by right clicking on the corresponding database in the Source menu of AhsayOBM, and then select Recovery model:
Important:
Perform a Full database backup after you change the recovery model of your database (Details):

===== Keywords ===== sql, odbc, log, differential, database, MSSQL, recovery, model