Post reply

Determine DB Recovery Model withing Sproc

  • June 29, 2011 at 10:06 am

    #243081

    For some of my databases I am creating an automated script for index maintenance and backups.

    I have a script for backing up logs and the database but I was hoping to not need to save Recovery Model for the databases being backed up. What is the easiest method of getting Recovery Model from a DatabaseName information?

    I have the database name so I can always retrieve the ID @dbID = db_ID(@dbname)

    This is one method I was attempting but have not found the information.

    EXECUTE sp_msforeachdb 'SELECT * FROM [?].sys.database_files'

    Odds are I am missing something obvious.

    thanks

    Daryl

  • June 29, 2011 at 10:31 am

    #1346048

    SELECT name,recovery_model_desc

    FROM sys.databases

    That will tell you the recovery model for each database.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • June 29, 2011 at 10:31 am

    #1346049

    Check sys.databases view.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • June 29, 2011 at 11:38 am

    #1346114

    http://msdn.microsoft.com/en-us/library/ms178534.aspx

  • June 29, 2011 at 1:35 pm

    #1346196

    Thank you all.

    sys.databases looks like what the doctored ordered. Including information that will be useful for other areas.

  • June 30, 2011 at 2:39 am

    #1346383

    SELECT DATABASEPROPERTYEX(DB_NAME(), 'Recovery')

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

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

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