I love that I can still learn cool new stuff with SQL Server even after 15 years 
I was trying to troubleshoot a problem and got to the point where I was questioning everything I knew about backups/restores because I just couldn’t explain what was going on and why a restore was taking so long to finish.
Then I came across this little nugget of awesomeness to help me along the way:
DBCC TRACEON (3004, 3605, -1)
Don’t worry little birdies I’ll feed ya…
Trace Flag 3004 – This gives you a very detailed explanation as to what your restores (and backups) are doing during execution
Trace Flag 3605 – This will write out the results of your Mgmt Studio query to the SQL Server Log rather than to the results window
The results will look something like this (click to enlarge):
To disable (turn off) these trace flags:
DBCC TRACEOFF (3004, 3605, -1)
If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk
