We have 4 test environments running SQL Server 2014 Developer edition. Every night, we refresh one of those environments by restoring a SQL Backup file stored on a separate server, so that it's in a known state.
Every Monday, we refresh all 4 environments to the same known state. All restore jobs start at 1am.
The backup file we restore from is a regular SQL backup taken from a SQL 2014 Server using a standard
BACKUP DATABASE command. The restore command is issued by SQL Server Agent using a standard
RESTORE DATABASE command.
When the daily restore runs, it takes between 2-2.5 hours to complete. On a Monday morning, that server completes in around 3 hours. This makes some sense as there are 4 servers trying to read the same backup file instead of one.
When the other 3 servers restore on Monday morning, the restore command is issued as scheduled at 1am, but they restore command then appears to do nothing until about 11am (< 10% CPU, disk and network usage).
When selecting from
sys.dm_exec_requests on all 3 servers during this time, I can see that the percentage complete is 0% until about 11am, when it starts increasing fairly evenly on all 3 servers. At the same time, network and disk usage both increase to around 100Mbps - which is an expected number for our dev environment.
I'm trying to work out what is happening for the approx. 10 hours between the restore command being issued and the data being written to disk.
The file doesn't appear to be locked by any one server, as at 11am all 3 servers are able to restore at roughly the same speed.
We will look to stagger the restore, or even move it to the weekend, but I'd really like to try and work out why the server is not restoring for 10 hours.
Is there something in the restore command that would cause it to wait?