Error: (MS SQL Error) The statement BACKUP LOG is not allowed while the recovery model is SIMPLE – ocBackup Support

Summary:
The recovery mode for SQL Server databases need to be in FULL mode. SIMPLE mode is not compatible.

Applies to:
OS: Windows (ALL)
Backup Set Type: MS SQL Server Backup
Backup Manager version: 5.X.X.X

Description:

When performing a MS SQL transaction log backup, the following error message is received in the backup report:

Backup Logs

No.	Type	Timestamp	Backup Logs
1	Info	YYYY/MM/DD hh:mm	Start [ Windows platform (COMPUTER NAME), Offsite Backup Manager 5.x.x.x ]
2	Info	YYYY/MM/DD hh:mm	Start running pre-commands
3	...	...	...
*	Error	YYYY/MM/DD hh:mm	[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.

Backup Logs

No.	Type	Timestamp	Backup Logs
*	Info	...	...
*	Error	YYYY/MM/DD hh:mm	[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.
*	Info	...	...
*	Error	YYYY/MM/DD hh:mm	[Microsoft][ODBC SQL Server Driver][SQL Server] BACKUP LOG is terminating abnormally.

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:
To resolve the issue, please modify the recovery model of the database in concern to FULL.

For OBM version prior to 5.5.X.X:
.f you are using OBM version prior to 5.5.X.X, please open Enterprise Manager or SQL Server Management Studio (depending on the MS SQL version in concern), right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list.

Example with MS SQL 2000:

Example with MS SQL 2005:

If you are using MSDE, please issue the following commands:

Example:
>osql -E -S [Server Name] -Q "ALTER DATABASE [Database Name] SET RECOVERY FULL"

This will enable transaction logging option for the MSDE databases and allow transaction log backup to be performed.

For OBM version 5.5.X.X or later:
If you are using OBM version 5.5.X.X or later, you can simply modify the recovery mode of your database by right clicking on the corresponding database in the Backup Source menu of OBM, and then select Recovery Model:

Example:

Ref. 1855