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.

SQL SERVER - Four Different Ways to Find Recovery Model for Database rm2

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

SQL SERVER - Four Different Ways to Find Recovery Model for Database rm3

Method 4

This method provides only one database at a time.

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

SQL SERVER - Four Different Ways to Find Recovery Model for Database rm4

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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY
Next Post
SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database

Related Posts

39 Comments. Leave new

  • i like the way , dave explains complex topic so simply and simple topics with more sweet way.

    Reply
  • 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.

    Reply
  • 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!

    Reply
  • 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.

    Reply
  • Hi Pinal,

    really good and in depth explaination.. Thanks

    Reply
  • 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?

    Reply
  • I like the third one

    Cheers,
    SteveC

    Reply
  • I like the third one to.. As usual you rock you rock you rock you rock you rock!!!!

    Reply
  • all these methodes are usable at interviews to impress the interviewer so these are helpful for us

    Reply
  • I like the third one, and I also have to add that your way of explaining things has helped me on many occassions. Thanks Dave

    Reply
  • ganesathandavam (@gthandavam)
    October 31, 2011 4:35 pm

    I like the third one, since it lists the same for all the databases on a server.

    Reply
  • Once again, good sir, PinalDave to the rescue!! Method 3 was exactly what I was looking for!

    Thank you….AGAIN!! :-)

    Reply
  • 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

    Reply
  • George Whiting dba
    May 21, 2012 10:16 pm

    should method 3 work in ver 2008 r2? I get errored out.
    I would like it best, too, if I can get it to work!

    Reply
  • 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.

    Reply
  • Perfect, this is exactly what I needed.

    Reply
  • 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

    Reply
  • How can rollback last update statement? the update statement not in transaction.

    Reply
  • Bharathi Kannan K
    November 11, 2012 5:04 am

    Bharathi Kannan,

    Awesome, I like the way the query explained. It helped me a lot in a critical situation.

    Reply
  • how can you obtain for all databases on a server, the database name, recovery mode, State and size of each database?

    Reply
    • 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.

      Reply
    • EXEC sp_databases

      Reply
      • 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!

      • 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

      • Thank you, i will give it a try when i get a chance.

      • 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

Leave a Reply