You want to quickly find out what the recovery model is for your database but you don’t want to start clicking and right-clicking in SSMS/Enterprise Manager to get that information. This is what you can do, you can use databasepropertyex to get that info. Replace ‘msdb’ with your database name

T-SQL
1
select databasepropertyex('msdb','Recovery') 
select databasepropertyex('msdb','Recovery') 

What if you want it for all databases in one shot? No problem here is how, this will work on SQL Server version 2000

T-SQL
1
2
SELECT name,DATABASEPROPERTYEX(name,'Recovery') 
 from sysdatabases
SELECT name,DATABASEPROPERTYEX(name,'Recovery') 
 from sysdatabases

For SQL Server 2005 and up, you should use the following command

T-SQL
1
2
select name,recovery_model_desc
from sys.databases
select name,recovery_model_desc
from sys.databases

I have also added this to our SQL Server admin Wiki here: Find Out The Recovery Model For Your Database
Make sure to check out the other wiki entries

If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum