SQL SERVER – Four Different Ways to Find Recovery Model for Database

Perhaps, the best thing about technical domain is that most of the things can be executed in more than one ways. It is always useful to know about the various methods of performing a single task. Today, we will observe four different ways to find out recovery model for any database.

Method 1

Right Click on Database >> Go to Properties >> Go to Option. On the Right side you can find recovery model.

Method 2

Click on the Database Node in Object Explorer. In Object Explorer Details, you can see the column Recovery Model.

Method 3

This is a very easy method and it gives all the database information in one script.

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

Method 4

This method provides only one database at a time.

SELECT 'ADVENTUREWORKS' AS [Database Name],
DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY')
AS [Recovery Model]
GO

My recommendation

Of all the above methods, I prefer Method 3. It is accurate and can be used in T-SQL code. Besides, I can use it for many other purposes as well.

I would like to know which one is your favorite method .

Reference : Pinal Dave (http://blog.SQLAuthority.com)

35 thoughts on “SQL SERVER – Four Different Ways to Find Recovery Model for Database

  1. Nice article and thanks to give simple way of finding recovery model.

    Sir, one more way i know is by use sp_helpdb [databasename] by this method we also know the recovery model.

    Like

  2. Hi,

    It appears as if option number two is valid only for 2008. Management Studio for 2005 only mirrors the data in the treeview and to my knowledge there is no ability to customize the columns. Great tip though!

    Like

  3. Hy Dave,
    This is actually my first post here

    I actually rather the second method, I’m becoming a hudge fan of the system views in order to se high level metadata info.

    But if we are talking about the structure of our data models (for each database) i like to go and make the searchs on the information_schema.

    Greetings from Mexico.

    Like

  4. Dev,
    I did try second method, however I didn’t get all tools under Object Explorer Details and also column Recovery Model is any idea why?

    Like

  5. Method 3 is the best for me, coz you can use it across linked servers e.g. SELECT name AS [Database Name],
    recovery_model_desc AS [Recovery Model]
    FROM remoteservername.master.sys.databases
    GO

    Like

  6. Did anyone has script to check when the database recovery model got changed ? I am looking into the errorlog on server but it seems some logs are missing on server so couldnt find the date and time.

    Like

  7. Method 5
    exec master..sp_helpdb will also give you the rec model of all the databases
    the colums status provides a lot of infos such as also the rec model
    but i agree with you Pinal the method 3 is the one i really prefer
    keep up the good work

    Like

    • I forgot to mention that the above methods to obtain the recovery model are great and I favor method 3 the most. I am currently obtaining the db names, recovery mode and state from sys.databases and then grabbing the database size from sp_helpdb. What I would like to be able to have is a query that gives me all of the information (db name, size, recovery mode and state) in one shot. Is this possible? if so, you advice on how is most appreciated.

      Like

      • exec sp_databases only returns the database name, size and remarks.

        I am asking if there is a query to report the database name, recovery mode, state of the database and size of the database in one run.

        As I indicated previously, I can obtain all info by querying sys.databases and then running sp_helpdb or now sp_databases but I want to run one query and have all four items reported. (name, mode,state and size)

        Is this possible?

        Thanks!

        Like

        • SELECT name AS [Database Name],
          recovery_model_desc AS [Recovery Model],
          state_desc AS [State],
          database_id AS [ Db_id],
          user_access_desc AS [ User Mode ]
          FROM sys.databases
          GO

          Like

        • SELECT sys.databases.name AS [Database Name],
          sys.databases.recovery_model_desc AS [Recovery Model],
          sys.databases.state_desc AS State,
          sys.databases.database_id AS [ Db_id],
          sys.databases.user_access_desc AS [ User Mode ],
          ( ( Sum(sys.master_files.size) * 8 ) / 1024 ) AS [Size (Mbs)]
          FROM sys.databases
          INNER JOIN sys.master_files
          ON sys.master_files.database_id = sys.databases.database_id
          GROUP BY sys.databases.name,
          sys.databases.recovery_model_desc,
          sys.databases.state_desc,
          sys.databases.database_id,
          sys.databases.user_access_desc
          ORDER BY sys.databases.name

          GO

          Like

  8. Some useful information below:

    Databases Default Recovery Model Can we change the recovery model Are backup required? Can be restored? : note: all system databases are version dependants. Can SQL services be started without these databases?
    master Simple Yes (Full, Diff, Simple) Yes Conditions Apply: If master becomes unusable, you can return the database to a usable state in either of the following ways: a. Restore master from a current database backup. If you can start the server instance, you should be able to restore master from a full db backup. b. If severe damage to master prevents you from starting SQL Server, you must rebuild master. No
    model User Configurable Yes (Full, Diff, Simple) Yes Yes, its possible to restore the model db. If severe damage to master prevents you from starting SQL Server, you must rebuild master. No, it will first started but after few seconds services will gets stopped itself.
    msdb Simple (Default) Yes (Full, Diff, Simple) Yes Yes, its possible to restore the model db. If severe damage to master prevents you from starting SQL Server, you must rebuild master. Note: Make sure the collation is same in the backup set. Yes, it will display “Recovery Pending”, it will not logg any server information and SQL agent services will be started somehow it won’t display any job and when try to access any folder located under SQL Server Agent, it will display an error message saying “Failed to retrieve data for this reqeust”. Though we can see the Error log folder, and associated errors occur in the sql server. Once we have msdb files, we need to restart the sql services.
    tempdb Simple No, you can’t change the recovery model for tempdb. It will remain “Simple” No No No
    resource NA NA No Yes, could be rebuild as well. Yes
    Configure Distribution Simple Yes (Full, Diff, Simple) Yes Yes Yes

    Like

    • Will you please explain me, what you have written exactly..
      It is very difficult to understand, looks like full of Q and A…
      But little bit complex to go through it

      Like

  9. hello,
    I have take database backup but after 2 hour my HD cresh and now I want database recovery please help here .. how can take my database recovery again

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  11. Option 3 is especially helpful comparing multiple databases that “should” have the same settings. (Dynamics GP multiple companies). Thanks.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s