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 (https://blog.sqlauthority.com)
39 Comments. Leave new
i like the way , dave explains complex topic so simply and simple topics with more sweet way.
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.
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!
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.
Hi Pinal,
really good and in depth explaination.. Thanks
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?
I like the third one
Cheers,
SteveC
I like the third one to.. As usual you rock you rock you rock you rock you rock!!!!
all these methodes are usable at interviews to impress the interviewer so these are helpful for us
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
I like the third one, since it lists the same for all the databases on a server.
Once again, good sir, PinalDave to the rescue!! Method 3 was exactly what I was looking for!
Thank you….AGAIN!! :-)
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
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!
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.
Perfect, this is exactly what I needed.
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
How can rollback last update statement? the update statement not in transaction.
Bharathi Kannan,
Awesome, I like the way the query explained. It helped me a lot in a critical situation.
how can you obtain for all databases on a server, the database name, recovery mode, State and size of each database?
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.
EXEC sp_databases
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