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.
[sourcecode language=”SQL”]
/*
.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
[/sourcecode]
Leave a Reply