2

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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