T-SQL Stored Procedure to Get DB’s Recovery Model

As a lazy DBA, I am not a fan of writing ad-hoc queries, therefore I keep wrapping up codes which I think that will come in handy some time later. Here is the piece of T-SQL that checks the recovery model of all or a specific DB on an instance.


/*
.SYNOPSIS
Stored procedure to get recovery model(s) of a specific DB(s).

.SQL Version
2005+

.Last Modified
2014-03-19 by Nick Xu

.Example
EXEC sp__GetRecoveryModel
EXEC sp__GetRecoveryModel @dbname = 'AdventureWorks'
*/

--For generic setting
USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp__GetRecoveryModel' AND type = N'P')
BEGIN
	DROP PROC sp__GetRecoveryModel
END
GO


CREATE PROC sp__GetRecoveryModel
@dbname AS VARCHAR(50) = NULL
AS
BEGIN
	IF @dbname is NOT NULL
	BEGIN
		SELECT [DatabaseName] = name,
		   [RecoveryModel] = recovery_model_desc 
		FROM   sys.databases
		WHERE name = @dbname
	END
	ELSE
	BEGIN
		SELECT [DatabaseName] = name,
		   [RecoveryModel] = recovery_model_desc 
		FROM   sys.databases
		ORDER BY [RecoveryModel] DESC
	END
END
GO




Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Recovery Model, SQL Server, T-SQL Script and tagged . Bookmark the permalink.

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