Sunday, March 25, 2012

Backing up log when recovery set to Simple

I have a job that I created that backs up transaction logs on a server that has many databases on it. If a db recovery model is set to simple, I want the job to skip over that database, rather then trying to backup the log.

It would be easy to just let it try, and fail, however, the job status will show failed, even if it backs up all database logs, and only fails on one. Is there a way to easily programatically detect if a db is set to simple, and then I can skip backing up that db log?

Thanks
TommySELECT DATABASEPROPERTYEX('Northwind','Recovery')|||Thanks brett, that's what I needed, in case anyone else is interested, here's the syntax I'm using:

Declare @.Recovery_Model nvarchar(20),
Select @.Recovery_Model = CONVERT(nvarchar(20), DATABASEPROPERTYEX( @.Database_Name , 'Recovery' ))
if (@.Recovery_Model = 'SIMPLE')
PRINT 'Simple Mode so skip'
else
PRINT 'do the needful'

No comments:

Post a Comment