SQL Server backup and restore operations are closely associated with recovery models. Recovery models are ways to manage how transactions are logged, whether or not the transaction log allows and requires backups, and what types of restore operations are possible. Recovery models helps us to bring back and preserve a database after any unexpected failure without any data loss.
We have three types of recovery models in SQL Server:
1. Simple
2. Full
3. Bulk Logged
It is important to know which recovery model a database is using. By default, every database is created under a Full recovery model. However, as per database, critically it may be changed.
Let us see different ways to find out the recovery model of a database in SQL Server.
Method 1: Find the model using graphical options.
1. Expand Databases in your object explorer within SSMS.
2. Right-click on your database, and click Properties.
3. Go to Options, and the Recovery model is displayed under Collation.
Method 2: Query the SQL Server metadata.
Querying SQL server metadata finds the sys.databases which stores information related to each database.
SELECT Db.name as 'Database Name', DB.recovery_model_desc as 'Recovery Model'
FROM sys.databases as DB
WHERE name = 'Pratice';
Method 3: Find out the recovery model of every database.
This gives us the name and the recovery model of each database.
SELECT Db.name as 'Database Name', DB.recovery_model_desc as 'Recovery Model'
FROM sys.databases as DB;
Method 4: Find out the recovery model using SQL Server’s built-in function.
DATABASEPROPERTYEX functions can be used to find out the recovery model of a database in SQL Server.
SELECT DATABASEPROPERTYEX('Pratice', 'Recovery') as 'Recovery Model';
Pratice is the database name here, and Recovery is the property name.
