DataLock Software: All
OS: Windows

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 (DataLock Test System), DataLock Software 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.

or

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.

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

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 DataLock Software, and then select Recovery Model:

       Example:
       

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 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.

N/A