SQL Server 2016 slow restore

Posted on

Question :

We have a 300 GB database that I am test restoring to a new SQL Server 2016 instance, on a virtual Windows 2016 server, used for testing purposes.

The VM has 32 GB RAM, running on a 1 socket CPU with 10 logical processors. (E5 – 2670 , 2,6GHz). Not sure about the power setting, will check that with the guys who set up the VM.

The first time I restored, I did it from a .bak– file from a 2014 instance.
The second time (still running) I did it from a backup made on the SQL Server 2016 instance.

Both times it took over an hour before the “5 percent processed” message was shown. Before that the percentage was on 0% (using a script that shows progress of restore). The restore was finished after about 2 hours the first time.
I especially wonder why it takes so long for the process indicator starts going. What is going on in when that stays on 0%?

Is this is normal on a SQL Server 2016 instance, or could something be wrong? On the production server, a restore will go much faster, but it is difficult to compare since the prod is a dedicated server with multiple spindles, while the test-server is a virtual server.

Answer :

The problem was, as proposed by some in comments, that Database Instant File Initialization was not enabled on the instance. I enabled this by giving the user under which the SQL Server service was running the needed permissions, and then restarted the SQL Server Service. The steps to take can be found in the following link: Database Instant File Initialization

Thanks for all the suggestions, some of them pointed me to the solution.

Instant File Initialization (IFI) is enabled by default* in SQL Server 2016.

* The box is pre-checked on the installer (if that’s the method used for installation). The installer gives you the opportunity to grant ‘Perform Volume Maintenance Tasks’ to the service account from the installer GUI so you don’t have to go do it yourself.

  • Are your backups striped or a single file?
  • Are your backups compressed?

To answer your question – “normal” is relative to many factors. I will say, however, that SQL Server 2016 restores shouldn’t take any longer than in previous versions.

Leave a Reply

Your email address will not be published. Required fields are marked *