I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk.

I want to restore database as quickly as possible as part of a "reset my dev environment" process. The system is mostly idle when I do this. The system runs SQL Server and Tomcat 7. I usually have an RDP session which has one or more of the following open Perfmon, SSMS, Visual Studio 2013, Visual Studio 2010. Process Monitor. I'm willing to tune this restore to the point where this RDP session becomes completely unresponsive during the restore process. No one but me is using this server.

Using Nic Cain's post I was able to get my restore process from 6xx seconds to ~100-120 seconds with the following parameters:

    FROM DISK = N'$(BackupFile)' 
            MOVE N'RMS_SIMULATION' TO N'$(MdbPath)\XXXX.mdf',
            STATS = 5,
            MAXTRANSFERSIZE = 4194304,
            BUFFERCOUNT = 72

By adding MAXTRANSFERSIZE and BUFFERCOUNT I was able to peg the I/O Data bytes/sec and I/O Data operations/sec counters to the top of the perfmon chart. Before that, bytes/sec would spike to the top, but spend more time at the bottom of the scale, and operations/sec was spikey in the middle of the scale.

Now I know I can tweak with the numbers and run the restores 100 times at different numbers and measure the restore time. While, there is a certain scientific vigor in that approach, I'd like something more data-centric.

I know I don't want SQL Server page faulting during the restore, because hitting virtual memory is a big slow down. However, what other perfmon counters should I be looking at?

  • How big is this backup? I think a 2 minute range is pretty darn reasonable. I'm not sure going much deeper into this is going to yield big gains that are worth your time. – LowlyDBA - John McCall Feb 6 '15 at 14:58
  • Do you have instant file initialization on? It may help. Technet article – Mark House Feb 6 '15 at 15:06
  • @JohnM There would be value in being able to run this every time I modify a particular stored procedure I am working on currently. There would be value in the saw sharpening exercise of the understanding of perfmon I'd gain from trying. – Justin Dearing Feb 6 '15 at 15:14
  • 1
    you can check Backup/Restore Throughput per/sec. Instant File Initialization will definitely help cutting down the restore time + you are spot on tuning MAXTRANSFERSIZE, BUFFERCOUNT. Also, if you add more backup destination files and tune your BUFFERCOUNT, then that would get you maximum benefit. Splitting your backup to more files will help you speed up restore. Check this blog post for more details. – Kin Shah Feb 6 '15 at 15:45
  • I did the measurement you are talking about (all combinations tested on multiple IO devices). Turns out the best value depends on the device. 4MB and 2 buffers is often a good choice. 72 buffers makes no sense. I think you should test buffer count 1-8 and stay with 4MB. – usr Feb 6 '15 at 21:54

If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx). This can also be less resource-intensive than a full restore.

The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot was taken so it can be significantly faster than a normal restore in many cases. Like a regular full restore, the log is initialized so keeping the log size small will reduce the restore time.

| improve this answer | |

Instant file initialization doesn't apply to transaction log files. So, you may also help yourself by precreating and/or adjusting the destination database to at least the sizes from the database you are restoring from.

Thus, SQL Server won't have to zero out any files during the restore.

| improve this answer | |
  • So basically "IF EXISTS DROP;CREATE DATABASE (specify LDF size explicitly)" Does this apply to a FULL backup to simple restore model? – Justin Dearing Feb 6 '15 at 19:09
  • Applies to all restores. No need to drop if it's already there. Just make sure data and tlog files are as large as they are on the source. – Tom Fox Feb 6 '15 at 20:07
  • 1
    Log files get zeroed during the restore. Pre-creating a log file the same size as the one to be restored will not help one iota. – Max Vernon Feb 6 '15 at 20:36
  • @MaxVernon So if I were to shrink the lob before taking the backup, I'd get a faster restore at the cost of the log have to grow itself during the first set of ops in simple mode? – Justin Dearing Feb 6 '15 at 21:21
  • 1
    Yes. Justin, that's correct. the only way to speed log restores is to restore a small log. – Max Vernon Feb 6 '15 at 23:18

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.