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 / Backup | Complete | Differential | Transaction Log |
| Simple | Required | Allowed | Not Allowed |
| Bulk-Logged | Required | Allowed | Required |
| Full | Required | Allowed | Required |
Resolution:
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.
C:\> osql -E -S SQLSERVER1\INSTANCE1 -Q "ALTER DATABASE xxx SET RECOVERY FULL"
Where
- SQLSERVER1\INSTANCE1 = the SQL Server and corresponding instance
- xxx = the name of the database