HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to tell what's failing at making backups?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
backupswhatfailingtellhowmaking

Problem

I have an instance of SQL server that is regularly getting errors like this:

02/25/2013 23:22:38,spid138,Unknown,BackupVirtualDeviceFile::SendFileInfoBegin:  
failure on backup device '{E0EA0FA7-D8FA-401B-8B4D-7C479F32A35D}21'. 
Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).


and this:

02/25/2013 23:22:38,Backup,Unknown,BACKUP failed to complete the command BACKUP DATABASE master. 
Check the backup application log for detailed messages.


According to our backup administrator, our Data Protector backups are A) running at a different time and B) running successfully. If that's the case, then I've got some rogue service attempting backups and failing.

Either way, I need figure out what's causing the errors. So, is there any way to track or log what entities are trying to make backups?

Solution

You can capture backup events using SQL Server Audit.

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION BackuAudit
  FOR SERVER AUDIT ServerAudit
  ADD (BACKUP_RESTORE_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION BackuAudit
  WITH (STATE = ON);
GO


Then you can view the Audit Logs from within SSMS (Security > Audits).

If you can't use audit (e.g. due to edition restrictions), you can use a server-side trace:

declare @rc int, @TraceID int, @maxfilesize bigint = 5; 

exec sp_trace_create @TraceID output, 0, N'c:\temp\backup_trc', @maxfilesize, NULL;

exec sp_trace_setevent @TraceID, 115, 1,  1;
exec sp_trace_setevent @TraceID, 115, 9,  1;
exec sp_trace_setevent @TraceID, 115, 3,  1;
exec sp_trace_setevent @TraceID, 115, 4,  1;
exec sp_trace_setevent @TraceID, 115, 5,  1;
exec sp_trace_setevent @TraceID, 115, 6,  1;
exec sp_trace_setevent @TraceID, 115, 7,  1;
exec sp_trace_setevent @TraceID, 115, 8,  1;
exec sp_trace_setevent @TraceID, 115, 10, 1;
exec sp_trace_setevent @TraceID, 115, 11, 1;
exec sp_trace_setevent @TraceID, 115, 12, 1;
exec sp_trace_setevent @TraceID, 115, 14, 1;
exec sp_trace_setevent @TraceID, 115, 21, 1;
exec sp_trace_setevent @TraceID, 115, 23, 1;
exec sp_trace_setevent @TraceID, 115, 26, 1;
exec sp_trace_setevent @TraceID, 115, 28, 1;
exec sp_trace_setevent @TraceID, 115, 29, 1;
exec sp_trace_setevent @TraceID, 115, 34, 1;
exec sp_trace_setevent @TraceID, 115, 35, 1;
exec sp_trace_setevent @TraceID, 115, 37, 1;
exec sp_trace_setevent @TraceID, 115, 40, 1;
exec sp_trace_setevent @TraceID, 115, 41, 1;
exec sp_trace_setevent @TraceID, 115, 49, 1;
exec sp_trace_setevent @TraceID, 115, 50, 1;
exec sp_trace_setevent @TraceID, 115, 51, 1;
exec sp_trace_setevent @TraceID, 115, 60, 1;
exec sp_trace_setevent @TraceID, 115, 64, 1;

exec sp_trace_setstatus @TraceID, 1;

select TraceID = @TraceID;


I've probably captured more columns there than you need...

Code Snippets

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION BackuAudit
  FOR SERVER AUDIT ServerAudit
  ADD (BACKUP_RESTORE_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION BackuAudit
  WITH (STATE = ON);
GO
declare @rc int, @TraceID int, @maxfilesize bigint = 5; 

exec sp_trace_create @TraceID output, 0, N'c:\temp\backup_trc', @maxfilesize, NULL;

exec sp_trace_setevent @TraceID, 115, 1,  1;
exec sp_trace_setevent @TraceID, 115, 9,  1;
exec sp_trace_setevent @TraceID, 115, 3,  1;
exec sp_trace_setevent @TraceID, 115, 4,  1;
exec sp_trace_setevent @TraceID, 115, 5,  1;
exec sp_trace_setevent @TraceID, 115, 6,  1;
exec sp_trace_setevent @TraceID, 115, 7,  1;
exec sp_trace_setevent @TraceID, 115, 8,  1;
exec sp_trace_setevent @TraceID, 115, 10, 1;
exec sp_trace_setevent @TraceID, 115, 11, 1;
exec sp_trace_setevent @TraceID, 115, 12, 1;
exec sp_trace_setevent @TraceID, 115, 14, 1;
exec sp_trace_setevent @TraceID, 115, 21, 1;
exec sp_trace_setevent @TraceID, 115, 23, 1;
exec sp_trace_setevent @TraceID, 115, 26, 1;
exec sp_trace_setevent @TraceID, 115, 28, 1;
exec sp_trace_setevent @TraceID, 115, 29, 1;
exec sp_trace_setevent @TraceID, 115, 34, 1;
exec sp_trace_setevent @TraceID, 115, 35, 1;
exec sp_trace_setevent @TraceID, 115, 37, 1;
exec sp_trace_setevent @TraceID, 115, 40, 1;
exec sp_trace_setevent @TraceID, 115, 41, 1;
exec sp_trace_setevent @TraceID, 115, 49, 1;
exec sp_trace_setevent @TraceID, 115, 50, 1;
exec sp_trace_setevent @TraceID, 115, 51, 1;
exec sp_trace_setevent @TraceID, 115, 60, 1;
exec sp_trace_setevent @TraceID, 115, 64, 1;

exec sp_trace_setstatus @TraceID, 1;

select TraceID = @TraceID;

Context

StackExchange Database Administrators Q#35457, answer score: 2

Revisions (0)

No revisions yet.