title: SQL Server database restore is very slow (FILESTREAM)
body: <p>I created a backup of the MS SQL Server 2014 database which contains a lot of binary data (FILESTREAM), about 2 million records in the table.
  The table itself has the following structure:</p>
  
  <p><img src="https://i.imgur.com/7kmjm8E.png" alt="Table structure"></p>
  
  <p>The database size is around 100GB (70GB of binary FILESTREAM data).
  When trying to restore the database, everything goes smooth up to 60% point (takes a couple of hours), but then it slows down to a crawl. I left if for a week and it didn't move from that 65% percent mark.</p>
  
  <p>A couple of things that I noticed:</p>
  
  <ul>
  <li>The usage of hard disk decreases. At the beginning in the resource monitor I see simultaneous access to several files (more than 20) in the DATA folder and the disk is busy 100%. After that 65% mark I see only 1-2 files being accessed by the SQL server process. The machine overall seems not busy at all.</li>
  <li>It looks like SQL Server is doing something but it doest it VEEERYY slowly.
  I suspect that the problem may be in the number of the STREAM_DATA rows.</li>
  </ul>
  
  <p>How can I speed up the restore process?</p>