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

« SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY

SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database »

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

July 16, 2009 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 12 Comments

12 Responses

on July 16, 2009 at 9:57 am | Reply aasim

i like the way , dave explains complex topic so simply and simple topics with more sweet way.
on July 16, 2009 at 4:23 pm | Reply Kuldip

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.
on July 16, 2009 at 7:06 pm | Reply abkma

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!
on July 16, 2009 at 10:50 pm | Reply Otoniel Diaz

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.
on July 17, 2009 at 12:25 am | Reply Krunal

Hi Pinal,

really good and in depth explaination.. Thanks
on July 17, 2009 at 10:08 am | Reply Ashok

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?
on September 7, 2009 at 1:29 pm | Reply Steve Culshaw

I like the third one

Cheers,
SteveC
on November 23, 2009 at 9:45 pm | Reply JG

I like the third one to.. As usual you rock you rock you rock you rock you rock!!!!
on October 1, 2011 at 12:02 am | Reply Rajeev

all these methodes are usable at interviews to impress the interviewer so these are helpful for us
on October 24, 2011 at 8:34 pm | Reply Laura

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
on October 31, 2011 at 4:35 pm | Reply ganesathandavam (@gthandavam)

I like the third one, since it lists the same for all the databases on a server.
on November 16, 2011 at 3:48 am | Reply John Waclawski

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

Thank you….AGAIN!! :-)