jump to navigation

Looking into the various ways you can find out the Recovery model of databases in an SQL Server instance April 18, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags:
trackback

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

Comments»

1. Dot Net Rules : Looking into the various ways you can find out the Recovery model of databases in an SQL Server instance - April 18, 2012

[…] server. Another common question is how to find out the recovery models for all databases at once. (read more) Share Posted: Τετάρτη, 18 Απριλίου 2012 6:04 μμ από το μέλος […]


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

%d bloggers like this: