How to trace SQL Server Backup

13 January,2020 by Tom Collins

Question: I'm trying to understand for educational purposes , what are the exact steps the SQL Server Backup is taking to progress through a SQL Server backup. Is there a way or method to trace the steps taken to complete a backup?

Answer:  Before I progress with outlining a method - I want to set a WARNING that this trace flag should only be used in guidance with experienced support staff - and only on non production systems.  Thoroughly test on non-production first and ensure consulting is involved 

Using the 3004 and 3605 DBCC TRACEON (3004 , 3605 , -1) , you can learn some interesting details about a backup operation. You can see examples on Slow SQL Server Backup and Restore with DBCC TRACEON (3004, 3605, -1)

I've used these trace flags previously to try and understand slow backup and restores 

An example of enabling the trace flags 3004 and 3605. Notice there is a command at the end which switched the trace flags off 

DBCC TRACEON (3004 , 3605 , -1);
backup database MY_DB to disk = 'M:MY_BB.bak'
DBCC TRACEOFF (3004 , 3605 , -1);

 


Author: Tom Collins (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 How to trace SQL Server Backup


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