Over the weekend, I was participating in a DR test. On the database side, that meant restoring backups from the production system to the DR system. What struck me as odd about the performance of the restores was that it seemed to read the backup file quickly enough (i.e. would report 100%), but would then spend a lot of time before actually reporting the restore complete (i.e. x pages restored to file n, y pages restored to log file). Once I copied the backup files to be local to the DR system, everything went quickly. What explains this behavior?

  • Were the backup files in a remote location from the DR test server? Could there have been bandwidth issues? And are there any clues in the Windows or SQL Server log files? – RLF Apr 14 '14 at 14:25
  • Yes, the backup files were remote to the DR system. I'm just confused by the apparent contradiction where it seemed to read the file quickly (or at least as quick as I'd have expected), but then was slow to actually complete the restore. – Ben Thul Apr 14 '14 at 14:30
  • I don't know what you were looking at, but when a restore starts it allocates all the space it needs for the database at the start. Perhaps that is the 100% you were referring to. – RLF Apr 14 '14 at 14:34
  • The 100% I was referring to was the output from the restore process as a result of having added "with stats = 10" to the restore command. – Ben Thul Apr 14 '14 at 17:53

When a database backup is restored it almost immediately allocates the space needed for the database files. So a 300 GB database will quickly appear in the file system at full size, but the file will be essentially empty to start with.

Once the space is allocated, the restore process goes on to read the backup file restoring data at the speed sustainable by the network and the servers. Once the backup is complete, restoring all the backup files, handling roll backs and roll forwards, then the database will be available.

A few years ago we had a very big database with the backup files stores remotely that was going to take 12 hours to restore over the network. So, in that case we cancelled the restore. Then we copied the files locally (about 1 hour) and restored quite quickly after that.

Note that if you database backups are compressed, the backup file will restore faster since there is less network traffic.

  • Adding Instant File Initialization will help drastically reduce the restore time. Highly recommended to turn it on for servers running SQL Server – Kin Shah Apr 14 '14 at 14:49

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.