There are three different recovery models in SQL Server, Simple, Full, and Bulk-logged. These recovery models define whether to include SQL Server transaction logs in the backup and determine what restore operation can be performed.
We will talk about the SQL Server recovery model in detail in a separate article. For now, let’s focus on the T_SQL query used to check the recovery model of a database in SQL Server. We can query sys.database view to retrieve the database recovery model. Connect to SQL Server Instance using SQL Server management studio, open new query, and execute the T_SQL query mentioned below.
SELECT name as “DB Name”,
recovery_model_desc as “DB Recovery Model”
FROM sys.databases
GO
The above query will list all the databases along with the recovery model. If there are a large number of databases, you can use the below query to get the recovery model for a specific database. For example, the below query will return the recovery model for msdb system database.
SELECT name as “DB Name”,
recovery_model_desc as “DB Recovery Model”
FROM sys.databases
Where name = N’msdb’
GO
How to change the recovery model in SQL Server
If you want to change the recovery model for a database, use the following T-SQL query. The below-mentioned example will change the recovery model of msdb database to full recovery model.
Use [master]
Go
Alter Database [msdb] Set Recovery Full;
Go