page contents

About the Post

Author Information

Different Ways to Find the Recovery Model for a SQL Server Database

Perhaps, the best thing about SQL Server and Microsoft products as a whole is that most of the tasks can be executed in more than one way. At times, it is might be useful to know about the various methods of performing a single task. Today, we will observe four different methods to find out a recovery model for any database.

Step 1

This method provides only one database at a time.

Right Click on Database >> Go to Properties >> Go to Option. On the Right side you can find the recovery model.

Step 2

Click on the Database Node in Object Explorer.

In Object Explorer Details, you can see the column Recovery Model.

Step 3

This is a very easy way to get this information and it gives all of the database Recovery Model in one script.

 

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

 Step 4

This method provides only one database at a time.

SELECT 'ADVENTUREWORKS' AS [Database Name],
DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY')
AS [Recovery Model]
GO

 My personal recommendation

Of all the above methods, I prefer Step 3. It is fast and accurate, plus it can be used in T-SQL code. Besides, I can use it for many other purposes as well.

Tags: , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.