A couple of weeks ago a colleague asked me how to determine the recovery model of a database using T-SQL.  In other words, not using SSMS.  For the purpose of this post I’m going to show how to determine the recovery model for a database using SSMS, and then answer the presented question.

The following screen can be viewed in SSMS by RIGHT clicking on the database in Object Explorer ==> Properties ==> Options.

From this we can see that the AdventureWorks database is Simple (the other options are Full and Bulk Logged).

But back to the original question…  How can this be determined using T-SQL?  At the time we were at lunch and I suggested using sp_helpdb.  I wasn’t positive this property was included…  but it turned out to be a good guess:

If you prefer to identify only the recovery model using T-SQL, such as to be able to save the information in a variable of temp table you can use the following approach:

There is an older version of the DATABASEPROPERTYEX called DATABASEPROPERTY.  According to BOL, it is not recommend using DATABASEPROPERTY as it has been scheduled for deprecation.

We can validate that DATABASEPROPERTY is deprecated by tracing deprecation in Profiler:

After running  the same query using DATABASEPROPERTY instead of DATABASEPROPERTYEX we observe the following in Profiler:

This only confirms what BOL was telling us.

OK, last approach (for today, I’m sure there are more methods) to determine the recovery model for a database:

Lessons Learned: As is always the case with SQL Server there are always multiple ways to arrive at the same result.  In this case we learned that we can capture a database’s recovery model by using:

• SSMS
• sp_helpdb
• DATABASEPROPERTYEX
• sys.databases view

We also learned and confirmed that DATABASEPROPERTY (no EX) will not be supported in versions beyond SQL Server 2008. 

Finally, we showed how to verify a deprecated command using SQL Profiler.

Although this post was focused on the recovery model of the database any of these methods will work with any other database properties.  For more information on the entire set of database properties see BOL for DATABASEPROPERTY .

Stay curious; keep learning…

Dave