Successful Backup Messages No More...

Andrew Kelly — Mon, 29 Oct 2007 17:09:00 GMT

How many times have you asked for a way to turn off the behavior in SQL Server that logs successful backup messages to the SQL Server Logs and to the Windows Application Event Logs? If you are like most DBA’s probably a lot. I have been asking for this feature for ages. I have sent email to SQLWish, filed on LadyBug and most recently on Connect with never any positive feedback. Well the other day I happened to be on campus in Redmond and was talking to Kevin Farlee (Thanks Kevin) who is a PM on the Storage Engine team for SQL Server. I asked again if we could have this feature and he said he would get back to me. Well he did and the answer is the ability was already there in the form of a trace flag. I could not believe this functionality was there all along and no one knew about it. Well at least I didn’t and I am pretty sure most others as well. So how do you use this functionality? Pretty easy actually and here is an example.

First we will backup the Northwind database (you can substitute your own) to disk. We will then use another feature of SQL Server that I think is also under utilized, sp_readerrorlog to place the contents into a temp table so we can query against it. We then can see the backup message that was logged.

BACKUP DATABASE [Northwind] TO DISK = N'C:\Northwind_BU.bak' WITH INIT,STATS = 10

IF OBJECT_ID('[tempdb].[dbo].[#ErrorLogs]',N'U') IS NOT NULL

DROP TABLE [dbo].[#ErrorLogs]

CREATE TABLE [dbo].[#ErrorLogs]

([LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL ) ;

INSERT INTO #ErrorLogs ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[sp_readerrorlog] 0 ;

SELECT * FROM [dbo].[#ErrorLogs]

WHERE [Text] LIKE 'Database Back%Northwind%' ;

Next we will turn on Trace Flag # 3226 and try it again. This flag can be set via TSQL or via a startup parameter.

DBCC TRACEON (3226)

BACKUP DATABASE [Northwind] TO DISK = N'C:\Northwind_BU.bak' WITH INIT,STATS = 10

TRUNCATE TABLE [dbo].[#ErrorLogs]

INSERT INTO #ErrorLogs ([LogDate], [ProcessInfo], [Text]) EXEC [master].[dbo].[sp_readerrorlog] 0 ;

SELECT * FROM [dbo].[#ErrorLogs]

WHERE [Text] LIKE 'Database Back%Northwind%' ;

Notice that now the most recent backup is not in the log nor will it be in the Event log. No longer will we have to weed thru all those successful messages just to see if there was a failure. Yes all failures will still be reported as before. And if you want to get back to the default behavior just turn the flag back off as such:

-- To turn the behavior off

DBCC TRACEOFF (3226)

Andrew Kelly : Trace Flag, Backup, Maintenance

Successful Backup Messages No More...