Recovery model and status of all databases | SansSQL

Tuesday, July 1, 2008

Recovery model and status of all databases

SELECT name,
DATABASEPROPERTYEX(name, 'Recovery') as [Recovery Model],
DATABASEPROPERTYEX(name, 'Status') as Status
FROM master.dbo.sysdatabases

Use this query to get the recovery model and status of all the databases present in the server.


EXEC sp_msforeachdb 'Select databasepropertyex(''?'', ''recovery'')as ''Recovery Model of ? Database'''

To find only the recovery model of all the databases.


SDR said...

I know it has been a year since this was posted but it is never to late for a comment. Thank you for this it is exactly what I needed.

Kal Patel said...

Works great

Thomas in Utah, USA said...

Three years after the posting, this tip still comes in handy, thanks!

Anonymous said...


Anonymous said...


khanibrahim said...

Hello there, just became alert to your blog through Google, and found that it’s truly informative. I am going to watch out for brussels. I’ll appreciate if you continue this in future. Lots of people will be benefited from your writing. Cheers!

Post a Comment