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
tsql
- 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
tsql
- SELECT name,DATABASEPROPERTYEX(name,'Recovery')
- FROM sysdatabases
For SQL Server 2005 and up, you should use the following command
tsql
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.