GilaMonster (4/6/2013)

---

Since we're going nowhere fast here...

Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.

/****************************************************/

/* Created by: SQL Server 2008 R2 Profiler */

/* Date: 2013/04/06 17:18:38 */

/****************************************************/

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 100;

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 115, 1, @on

exec sp_trace_setevent @TraceID, 115, 6, @on

exec sp_trace_setevent @TraceID, 115, 10, @on

exec sp_trace_setevent @TraceID, 115, 14, @on

exec sp_trace_setevent @TraceID, 115, 11, @on

exec sp_trace_setevent @TraceID, 115, 12, @on

exec sp_trace_setevent @TraceID, 128, 1, @on

exec sp_trace_setevent @TraceID, 128, 6, @on

exec sp_trace_setevent @TraceID, 128, 10, @on

exec sp_trace_setevent @TraceID, 128, 14, @on

exec sp_trace_setevent @TraceID, 128, 11, @on

exec sp_trace_setevent @TraceID, 128, 12, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go