XML Feeds
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
- 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
- SELECT name,DATABASEPROPERTYEX(name,‘Recovery’)
- FROM sysdatabases
For SQL Server 2005 and up, you should use the following command
- 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
