Find Database Recovery Mode

  • Hey Guyz,

    I was wondering what TSQL Script you use to find database recovery mode?

    I am using this.

    SELECT CONVERT(SYSNAME, DATABASEPROPERTYEX(N'master', 'Recovery'))

    Just wanted to share this with y'll and find out what others are using?

    Do we have a snippet section where I can post little scripts which are useful?

    Cheers,

    Nirav


    Kindest Regards,

    WRACK
    CodeLake

  • I've been using this query:

    select recovery_model_desc from sys.databases where name = 'master'

    The sys.databases view is in every database.

    I guess you could post your statement in the "Scripts" section. See the navigation list on the left side of the screen.

    Greg

  • Use master

    declare @DBName varchar(35),

    @STR varchar (255)

    declare DBRecoveryModelGenerator_cursor cursor for

    select name from sysdatabases

    where category in ('0', '1','16')

    order by name

    open DBRecoveryModelGenerator_cursor

    fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)

    begin

    if (@@fetch_status <> -2)

    begin

    select @STR = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName

    exec (@str)

    end fetch next from DBRecoveryModelGenerator_cursor into @DBName end

    close DBRecoveryModelGenerator_cursor

    DEALLOCATE DBRecoveryModelGenerator_cursor

    go

    SQL DBA.

  • Greg Charles (3/18/2008)


    I've been using this query:

    select recovery_model_desc from sys.databases where name = 'master'

    The sys.databases view is in every database.

    I guess you could post your statement in the "Scripts" section. See the navigation list on the left side of the screen.

    Hi Greg,

    Thanks for the reply. Will the script you posted also work under SQL 2000? I am not 100% sure that my script will work under SQL 2000.


    Kindest Regards,

    WRACK
    CodeLake

  • $sanjayattray (3/18/2008)


    Use master

    declare @DBName varchar(35),

    @STR varchar (255)

    declare DBRecoveryModelGenerator_cursor cursor for

    select name from sysdatabases

    where category in ('0', '1','16')

    order by name

    open DBRecoveryModelGenerator_cursor

    fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)

    begin

    if (@@fetch_status <> -2)

    begin

    select @STR = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName

    exec (@str)

    end fetch next from DBRecoveryModelGenerator_cursor into @DBName end

    close DBRecoveryModelGenerator_cursor

    DEALLOCATE DBRecoveryModelGenerator_cursor

    go

    Hi Sanjay,

    Thanks for replying. It seems to me that you are looping through the list of databases and printing the recovery model of each database.

    I would normally stay away from cursor and just use the following query. I don't mean to be bashing your method, just a suggestion.

    [font="Courier New"]SELECT [name] AS [DatabaseName], CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel] FROM master.dbo.sysdatabases ORDER BY name[/font]


    Kindest Regards,

    WRACK
    CodeLake

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply