Transaction Log Backups are Not Supported Using Simple Recovery Model (code 1800)

Situation

An MS SQL Backup Plan terminates with the following info message:

Cause

Transaction Log backups are only supported using the Full and Bulk-Logged recovery models.

A transaction log backup features the backup of the active part of the transaction log. So after you issue a Full or Differential backup the transaction log backup will have any transactions that were created after those other backups completed. After the transaction log backup is issued, the space within the transaction log can be used for other processes. If a transaction log backup is not taken, the transaction log will continue to grow.

Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

Solution

Switch Simple recovery mode to Full or Bulk-Logged.

1. Connect to the required instance of the SQL Server Database Engine.
2. In Object Explorer, click the server name to expand the server tree.
3. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
4. Right-click the database that was listed as skipped, then click Properties.
5. In the Database Properties dialog box, in the Select a page group, click Options.

6. In the Recovery model, select Full or Bulk-logged.
7. Click OK.
8. Restart your backup plan.