Tuesday, October 2, 2007

How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008

How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)


--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases


--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases

No comments: