A common question regarding backup and restore
Posted by decipherinfosys on April 27, 2009
One of the common questions that we have seen the client DBA’s ask us around backup and restore is: “Why is it that taking a full backup takes us say 20 minutes but when we try to restore it, it takes much longer? We understand the processes are very different and the comparison is not really an applies-to-apples comparison but at times we have seen the restore than twice as much time as the backup. What could be the reason or is there a way to look “under the hood” to see what is going on?”
Well, first of all – both backup and restore are two very different actions and there can be many reasons for this time difference between the two processes even for the same database. Entire books have been written on these topics and there are too many topics that could effect the performance of both the operations. You should read the BOL articles related to this if you want to get more information on the internal workings of backup and restore. Also, you should read the posts by MVP Paul Randal on backup and restore – here. Even if you are a seasoned DBA, you will learn a lot from these posts.
What we want to point out in this post is that if you want to learn more about what is going on when you do the backup and restore, you can use these trace flags:
DBCC TRACEON (3004, 3605, -1)
Learnt this from a post in one of the forums mentioned here. As noted in the post, this logs a lot of detailed information into the log about the backup and restore processes. The flag 3004 is the one that collects the extended information while 3605 is needed to force the output to the log. Also, you need to understand that you should do this only on a test system as a learning exercise and never do this on a production system. And once done, turn the trace off by using:
DBCC TRACEOFF (3004, 3605, -1)