Audit Backup\Restore Event with SQL Default trace

A DBA asked me about tracking down the application which triggered a regular BACKUP request at an unexpected time.  This unexpected backup was causing performance issues for business users. He couldn’t see from the SQL Server Error logs an application name that could make a BACKUP request.   I suggested he use the SQL Server Default trace Audit Backup/Restore event. The Audit Backup/Restore event class occurs when a backup or restore command is executed

The Default Trace supplies very useful information for troubleshooting  SQL Server problems. There are a wide range of events audited. The default trace is lightweight and I’d recommend DBAs don’t turn off this trace. Read this post for general information - SQL Server – default trace FAQ.   Included in the post are details on listing all events traced.

I’ve used the default trace for different troubleshooting scenarios such as:

SQL LOG FILE AUTOGROW performance troubleshooting

Who made DDL table changes on the database.

When troubleshooting Backup or Restore Issues these questions are useful:

1)      What loginname is used for the Back \ Restore request?

2)      What was the start time?

3)      What was the  command text?

4)      What application triggered the request?

It is not a guarantee that all BACKUP \ RESTORE information is available , but some clues can offer direction for further direction.

 This query lists all the Backup \ Restore events on the SQL Server Instance. Note: the default setting of the default trace rolls over the trace file, so  backup the trace files for historical information. When the SQL Server instance restarts the default trace files are refreshed.

select e.name as eventclass,t.loginname, t.spid, t.starttime, 
t.textdata, t.objectid, t.objectname, t.databasename, 
t.hostname, t.ntusername, 
t.ntdomainname, t.clientprocessid, t.applicationname, t.error 
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
inner join sys.trace_events e on t.eventclass = e.trace_event_id
where eventclass=115