In SQL Server, a recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Databases can use one of the following three recovery models: simple, full, and bulk-logged.

You can query the sys.databases catalog view to get a list of databases and their recovery models.

Example

Here’s an example to demonstrate:

SELECT 
    name, 
    recovery_model_desc  
FROM sys.databases;

Result:

+-----------------------+-----------------------+
| name                  | recovery_model_desc   |
|-----------------------+-----------------------|
| master                | SIMPLE                |
| tempdb                | SIMPLE                |
| model                 | FULL                  |
| msdb                  | SIMPLE                |
| Music                 | FULL                  |
| KrankyKranes          | FULL                  |
| WideWorldImporters    | SIMPLE                |
| World                 | FULL                  |
| PetHotel              | FULL                  |
| StereoSystems         | FULL                  |
| NarrowNationExporters | FULL                  |
| TestDB                | FULL                  |
+-----------------------+-----------------------+

In this example, most of my databases use the full recovery model, but some use the simple recovery model.

The master, tempdb, and msdb databases use the simple recovery model by default. The model database uses the full recovery model, which means that any new databases created will use the full recovery model by default. This setting may be different on your system, depending on which edition you’re using, and whether or not it’s been changed.

See the Microsoft website for more information on recovery models.

If you don’t want to list all databases, you can always use a WHERE clause to narrow it down to just one database:

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

Result:

+-----------------------+-----------------------+
| name                  | recovery_model_desc   |
|-----------------------+-----------------------|
| NarrowNationExporters | FULL                  |
+-----------------------+-----------------------+

Here’s how to change the recovery model.