Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to determine the duration of backup restore. I executed a few restore command to restore some backups located on network share drive. Here is command and summary,

RESTORE DATABASE [test]
FROM DISK = '\\network\test.bak'
WITH
    MOVE 'test_data' TO 'D:\test_data.mdf',
    MOVE 'test_log' TO 'E:\test_log.ldf',
    CHECKSUM, STATS;

Backup A
1st restore take about 5 min. Ok, after restore complete, I delete the database
2nd restore take about 1.8 min. Hm.. Let's delete the database and try again.
3rd restore take about 1.5 min. Hm..

Backup B (almost same size as backup A, at the same network location)
1st restore take about 1.8 min. I deleted it and try again
2nd restore take about 1.5 min. Ok..

The service account is enabled for instant file initialization.

My question is why the restore duration varied on different restore attempt on the same backup file to the same machine? Is it purely on network throughput (perhaps someone was doing something on the network share and stuff), or something else like cache or something in SQL internal?

share|improve this question

1 Answer 1

Some of it will have to do with caching on the remote machine. You've also got to account for what else is flying across the network. Also some of those blocks may be in cache on the SQL Server's disk cache (not SQL's buffer pool) as they are the same exact blocks the disk may use the cached copy not the actual page you are restoring.

Don't forget things like witches, gremlins, boogie men, etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.