February 26, 2007 at 7:32 am
Hi,
I am looking for a t-sql command to get the list of databases with a full recovery model. Please let me know if you know a command for that.
Thanks,
sridhar.
February 26, 2007 at 7:53 am
Where are two ways of doing this. One would be converting the status value from sysdatabases, the other is using the DATABASEPROPERTYEX function. Here's one example :
CREATE TABLE #SimpleDB ([DB_Name] sysname)
DECLARE @db as nvarchar(128)
DECLARE DatabaseList CURSOR FAST_FORWARD FOR
SELECT [name] FROM master..sysdatabases
--WHERE state_desc = 'ONLINE'
ORDER BY [name]
OPEN DatabaseList
FETCH NEXT FROM DatabaseList
INTO @db
WHILE (@@fetch_status = 0)
BEGIN
IF DATABASEPROPERTYEX(@db,'Recovery') = 'SIMPLE'
INSERT INTO #SimpleDb
Values(@db)
FETCH NEXT FROM DatabaseList
INTO @db
END
SELECT * FROM #SimpleDB
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
February 26, 2007 at 7:54 am
Try:
SELECT [name]
FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX([name],'recovery') = 'FULL'
February 26, 2007 at 8:07 am
Thank you. It worked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply