Post reply

getting the list of databases with full recovery model

  • February 26, 2007 at 7:32 am

    #62900

    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

    #691730

    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

    #691732

    Try:

    SELECT [name]

    FROM master.dbo.sysdatabases

    WHERE DATABASEPROPERTYEX([name],'recovery') = 'FULL'

     

  • February 26, 2007 at 8:07 am

    #691772

    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