Looking into the various ways you can find out the Recovery model of databases in an SQL Server instance April 18, 2012Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: recovery model
This is going to be a rather short post. I have been teaching the Official Microsoft SQL Server courses in my capacity as MCT and to be totally honest with you I enjoy it very much.One of my favorite subject is Backup & Restore. This is maybe the main DBA task.There is no way to explain adequately Backup & Restore without talking about Recovery models.Well some people in the class did not know how to find the recovery model for each database in the server. Another common question is how to find out the recovery models for all databases at once.
I will show you the various ways I know on how to accomplish that.
We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition, here.
- This is the obvious way (most people should know that even if they are beginners in SQL Server).
I choose the database, in this case AdventureWorks2008R2, select Properties, then select Options.In this case I see that the recovery model is FULL
Have a look at the picture below
- There is a way to find out the recovery model for all databases from SSMS. Select the Databases node.From the menu select View and then Object Explorer Details. Have a look in the picture below to see the output.You must select from the available columns to show the Recovery Model column
- We can find out the Recovery Model for each database in the SQL Server using. In a query window type and then execute the following T-SQL code
USE master GO SELECT name AS [DBName], recovery_model_desc AS [Recovery_Model] FROM sys.databases GO
- We can find out the recovery model for a specific database by typing and executing the following T-SQL statements in a new query window
USE master GO SELECT 'AdventureWorks2008R2' AS [DBName], DATABASEPROPERTYEX('AdventureWorks2008R2', 'Recovery') AS [Recovery_Model] GO
Hope it helps!!!