Post reply

Command to list recovery models for all databases???

  • July 17, 2008 at 9:29 am

    #71673

    Can't find one.. is there a table I can query to get this??

  • July 17, 2008 at 9:38 am

    #844496

    select

    name, recovery_model_desc

    from

    sys.databases

    Will do it in SQL Server 2005. I do not have a SQL Server 2000 instance handy to test it, but I believe:

    select

    name, recovery_model_desc

    from

    dbo.databases

    will work there.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • July 17, 2008 at 10:01 am

    #844514

    Thank you for the reply.

    select * from DBO.DATABASES against a sql server 2000 master DB only shows columns Databasename, Databaseuser, databasepassword and databaseserver..

    I'll have to keep poking around : )

  • July 17, 2008 at 10:42 am

    #844526

    This will do it in 2000.

    SELECT DATABASEPROPERTYEX(N'DatabaseName', N'RECOVERY')

  • July 17, 2008 at 10:54 am

    #844534

    I just noticed you said for all databases. If you don't mind using an undocumented procedure for sql 2000 this will work.

    Create Table #tmp

    (dbname varchar(100), RecoveryModel sql_variant)

    INSERT INTO #tmp

    EXEC sp_msforeachdb '

    SELECT ''?'', DATABASEPROPERTYEX(''?'', N''RECOVERY'')

    '

    Select * from #tmp

    Drop Table #tmp

  • July 17, 2008 at 11:38 am

    #844560

    Ken Simmons (7/17/2008)

    I just noticed you said for all databases. If you don't mind using an undocumented procedure for sql 2000 this will work.

    Create Table #tmp

    (dbname varchar(100), RecoveryModel sql_variant)

    INSERT INTO #tmp

    EXEC sp_msforeachdb '

    SELECT ''?'', DATABASEPROPERTYEX(''?'', N''RECOVERY'')

    '

    Select * from #tmp

    Drop Table #tmp

    No need for a temp table or the msforeach...

    SELECT Name

    , DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]

    FROM master.dbo.sysdatabases

    Your friendly High-Tech Janitor... 🙂

  • July 17, 2008 at 11:40 am

    #844563

    sp_msforeachdb 'use ? select ''?'' as DatabaseName, DATABASEPROPERTYEX(N''?'', N''RECOVERY'')'

    😀

  • July 17, 2008 at 12:27 pm

    #844590

    That worked. Thank you so much

  • July 17, 2008 at 4:20 pm

    #844711

    No need for a temp table or the msforeach...

    Good catch Gordon. The first thing I thought of was sp_msforeachdb and I usually create a temp table so I get the results back in the same dataset. Way more work than the solution really needed.

    Thanks for the followup reply.

Viewing 9 posts - 1 through 8 (of 8 total)

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