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);
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |