Setting trace flag to eliminate successful backup messages in errorlog

Setting trace flag to eliminate successful backup messages in Sql Server error log
-- Written by Jason Qu
--

declare

@InstanceName varchar(2000),

@key varchar(2000),

@FlagName varchar(20),

@value_name VARCHAR(255),

@ArgCnt int,

@Version int --7 (SQL 7 ) 8 (SQL 2000) 9 (SQL 2005) 10 (SQL 2008)

SET NOCOUNT ON

SELECT @Version=@@MICROSOFTVERSION / 0x01000000

SET @FlagName='-T3226'

--show all global trace flag

--For SQL server 2005 and above

create table #t1(TraceFlag varchar(10),Status bit,Global bit,Session bit)

--For SQL server 2000 and below

--create table #t1(TraceFlag varchar(10),Status bit)

insert into #t1

exec('DBCC Tracestatus(-1) WITH NO_INFOMSGS')

if exists(select 1 from #t1 where TraceFlag=substring(@FlagName,3,len(@FlagName))) begin

      print 'Trace Flag(DBCC TRACEON) '+@FlagName+' has been set on '+@@servername

end else begin

      --SET The trace flag in cache, this takes effect immediately

      print 'Setting Trace Flag(DBCC TRACEON) '+@FlagName+' on '+@@servername

      DBCC TRACEON(3226,-1) WITH NO_INFOMSGS

end

select * from #t1

drop table #t1

--SET The trace flag in startup parameters, this will take effect after the service restarts

if (@Version>8) begin

--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

      exec master..xp_instance_regread   

      @rootkey='HKEY_LOCAL_MACHINE',   

      @key='SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',   

      @value_name='MSSQLSERVER', --for named instance, should change this to the instance name   

      @value=@InstanceName output

      print @InstanceName

      set @key= N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\'+@InstanceName+'\\MSSQLServer\\Parameters'

end else begin

      set @key= N'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\Parameters'

end

print @key

--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters

--store current params

create table #t(Value varchar(255),data varchar(255))

insert #t

EXEC   master..xp_instance_regenumvalues

       @rootkey = N'HKEY_LOCAL_MACHINE',

       @key     = @key

select * from #t

SELECT @ArgCnt=count(*) from #t

SET @value_name='SQLArg'+CAST(@ArgCnt AS VARCHAR(3))

--print @value_name

if exists(select 1 from #t where data=@FlagName) begin

      print 'Trace Flag(Startup parameters) '+@FlagName+' has been set on '+@@servername

end else begin

      print 'Setting Trace Flag(Startup parameters) '+@FlagName+' on '+@@servername

      EXEC   master..xp_regwrite

            @rootkey='HKEY_LOCAL_MACHINE',

            @key=@key,

            @value_name=@value_name,

            @type='REG_SZ', @value=@FlagName   

end

drop table #t