If you get an error that reads The statement BACKUP LOG is not allowed while the recovery model is SIMPLE when trying to back up a database in SQL Server or Azure SQL Edge, it’s because you’re trying to back up the transaction logs on a database that uses the simple recovery model.

To fix this, change the recovery model to either full or bulk logging.

The Error

Here’s an example of T-SQL code that results in the error:

BACKUP LOG Music 
TO DISK = '/var/opt/mssql/backups/Music.trn';

Result:

Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

The Cause

As mentioned, the error is caused when you try to back up the transaction logs on a database that uses the simple recovery model.

The simple recovery model doesn’t support log backups.

The Solution

To overcome this issue, set the database recovery model to either FULL or BULK_LOGGED:

USE master;  
ALTER DATABASE Music 
SET RECOVERY FULL;

That example set the database to full recovery mode.

However, you will also need to perform at least one full database backup before you start backing up your transaction logs. If you don’t do this, you’ll get error 4214, which states that BACKUP LOG cannot be performed because there is no current database backup.

Here’s an example of performing a full database backup:

BACKUP DATABASE Music 
    TO DISK = '/var/opt/mssql/backups/Music.bak' 
    WITH FORMAT;

Now the transaction logs can be backed up as required:

BACKUP LOG Music 
TO DISK = '/var/opt/mssql/backups/Music.trn';

Result:

Processed 3 pages for database 'Music', file 'Music_log' on file 1.

Using Azure SQL Edge?

If you use Azure SQL Edge, you might find this issue happens a lot. That’s probably because databases created with SQL Edge use the simple recovery model by default. And that’s because the model database uses the simple recovery model.

You can always change the recovery model on the model database to FULL, which will result in subsequent databases using full recovery mode by default.