By default, databases created with Azure SQL Edge use the simple recovery model. This means that you can’t perform log backups on these databases.

If you need to perform log backups on a database created with SQL Edge, you’ll need to change the recovery model of the database to either full or bulk logged.

This can be done with T-SQL with the ALTER DATABASE statement.

The available options when using ALTER DATABASE to set the recovery model are:

• SIMPLE
• FULL
• BULK_LOGGED

Example

Suppose we have a database called KrankyKranes.

We can check it’s current recovery model by querying the sys.databases catalog view:

SELECT 
    name, 
    recovery_model_desc  
FROM sys.databases
WHERE name = 'KrankyKranes';

Result:

+--------------+-----------------------+
| name         | recovery_model_desc   |
|--------------+-----------------------|
| KrankyKranes | SIMPLE                |
+--------------+-----------------------+

It currently uses the simple recovery model.

Let’s change it to the full recovery model:

USE master;  
ALTER DATABASE KrankyKranes 
SET RECOVERY FULL;

Done.

Now let’s check the result:

SELECT 
    name, 
    recovery_model_desc  
FROM sys.databases
WHERE name = 'KrankyKranes';

Result:

+--------------+-----------------------+
| name         | recovery_model_desc   |
|--------------+-----------------------|
| KrankyKranes | FULL                  |
+--------------+-----------------------+

The recovery model was successfully changed as specified.

Note, that you will need ALTER permissions on the database in order to make this change.

Important Next Step

The switch to the full or bulk logged recovery model takes effect only after the first data backup.

Therefore, as soon as you switch from simple recovery mode to either the full or bulk logged recovery model, you should take a full or differential database backup to start the log chain.

The model Database

The reason that databases created in SQL Edge use the simple recovery mode by default, is because the model database uses that mode.

If you find this problematic, you can always change the recovery mode of the model database to FULL. Doing that will result in subsequent databases created to use the full recovery model:

USE master;  
ALTER DATABASE model 
SET RECOVERY FULL;