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?
Sql-server – Slow performance of remote restore
backuprestoresql server
Related Question
- Sql-server – How to recover from a failed SQL Restore
- SQL Server 2014 – Restore Keeps Failing
- SQL Server – Resolving Differential Backup Issues
- SQL Server 2014 – Double Concurrent Restore from One Backup for 3 Node AG
- Sql-server – Under what conditions could restore of a huge database get delayed, in SQL server 2016, hosted on VM
- Sql-server – How to choose which backup files to restore when identical timestamps exist for diff and log backups
- SQL Server sp_AllNightLog Setup – How to Successfully Set Up sp_AllNightLog for a Reporting Server
Best Answer
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.