SQL Server – Audit Backup\Restore Event with SQL Default trace

12 July,2012 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Audit Backup\Restore Event with SQL Default trace


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer