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]BACKUP LOG is not allowed while the
trunc. log on chkpt. option is enabled. Use BACKUP DATABASE or disable the option
using sp_dboption.
Or
[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 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.
Resolution
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 Servosity Pro, and then select Recovery Model:
Depending on your exact setup, you may also need to modify some settings in your SQL Management Console. In this case, 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 SQL 2000:
Example 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.