Table of Contents
[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:
Perform a Full database backup after you change the recovery model of your database (Details):
Keywords
sql, odbc, log, differential, database, MSSQL, recovery, model