How can we Help ?

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE

When performing a MS SQL transaction log backup, the following error message is received in the backup report:
[Microsoft][ODBC SQL Server Driver][SQL Server] The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is not allowed while the trunc. log on chkpt. option is enabled. Use BACKUP DATABASE or disable the option using sp_dboption.


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 / Backup Complete Differential Transaction Log
Simple Required Allowed Not Allowed
Bulk-Logged Required Allowed Required
Full Required Allowed Required


Solution 1

Remove the additional schedule for 'Transaction Logs' which may also be called 'Incremental/Differential'. Set the 'Database - Full' schedule to run daily.


Solution 2
To resolve the issue, please modify the recovery model of the database in concern to FULL.  You can simply modify the recovery mode of your database by right clicking on the corresponding database in the Backup Source menu of backup software, and then select Recovery Model:

If your backup schedules are set for a DB (Full) backup every night, then you have the option to leave your DB's in SIMPLE recovery mode because you will not need to backup the transaction logs.  Simply remove your backup schedule for the transaction logs.

But for example if you wanted to do a full DB backup on Fridays only and t-logs during the week (which is the default), this does require the DB's to be in FULL recovery mode and your schedule(s) should look something like this.

T-logs:  Every Mon Tue Wed Thu at 21:00 until backup finished
Database:  Every Fri at 21:00 until backup finished

Since SQL DB's get really good compression with backup software, you can more often than not execute a DB backup nighltly in SIMPLE recovery mode without the transaction logs.  Only customers with larger SQL DB's or slow bandwidth connections need to look at backing up using the Microsoft FULL recovery model.

Here is a good Recovery Model Overview from Microsoft

Note:  In some cases the SQL may switch back to a recovery model set to simple depite these steps. In this case the only remaining option is to forgo backing up transactions logs and instead run database backups nightly. This is acomplished within the backup schedule of the SQL backup job within the Backup Manager.

If you have multiple databases and you would like different recovery models for each, simply create different backup sets for each.


You may also set the recovery model via the command line by running the following commands. Run it along with
each of the database that you want to run transaction log backup.



  • SQLSERVER1\INSTANCE1 = the SQL Server and corresponding instance
  • xxx = the name of the database
Average rating:0 (0 Votes)

Contact Information

    2850 Interstate Dr. #2, 33805
    Toll-Free: +1 800.213.2157
    US Local: +1 727.564.9230
    UK Local: +44 2036.080613
    Phone Hours: 9AM - 6PM EST
Copyright © 2015 All rights reserved.