SQL Server has three recovery models; simple, full, and bulk logged. Each database uses one of these settings.

Backup and restore operations occur within the context of the recovery model of the database

You can change the recovery model of a database by using the ALTER DATABASE statement along with the SET RECOVERY option.

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

• SIMPLE
• FULL
• BULK_LOGGED

You will need ALTER permissions on the database in order to make such changes.

Example

Suppose we have a database called PetHotel.

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 = 'PetHotel';

Result:

+----------+-----------------------+
| name     | recovery_model_desc   |
|----------+-----------------------|
| PetHotel | SIMPLE                |
+----------+-----------------------+

The PetHotel database currently uses the simple recovery model.

Let’s change it to the full recovery model:

USE master;  
ALTER DATABASE PetHotel 
SET RECOVERY FULL;

Done.

Now let’s check the result:

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

Result:

+----------+-----------------------+
| name     | recovery_model_desc   |
|----------+-----------------------|
| PetHotel | FULL                  |
+----------+-----------------------+

The recovery model was successfully changed as specified.

Important Considerations

When switching from simple recovery mode to one of the others, the switch only takes effect 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.

If you switch to the simple recovery model, then be sure to disable any scheduled jobs for backing up the transaction log (the simple recovery model doesn’t use log backups).

Also, the bulk recovery model is specifically for bulk operations. If you switch from the full recovery model to the bulk logging model for this purpose, you should switch back to full recovery mode once you’ve performed the bulk operations.

After switching from the bulk-logged recovery model back to the full recovery model, be sure to back up the log.

See Recovery Models on the Microsoft website for an overview of each recovery model.