Improve Restore Speed

I have a ~3GB backup file from our QA database, and a daily diff (~500MB). For data complexity reasons, some of our developers like to use regular backups from this database, and so we have a process that automatically restores it into multiple databases every night (i.e. 1 database per developer).

When unpacked, the database size reports as ~14GB.

On one of our servers, we run these databases on an SSD, and so restore time is an acceptable 3 mins or so per database. However, on another server, adding an SSD is not an option, and the restores are taking about 17 mins for just two databases.

I'm afraid this isn't going to scale particularly well - I need several more databases on that instance for the various devs, and the restore time is prohibitive.

I've read that splitting the backup into multiple files can often help restore speed, but I have no control on the backup process itself. I can talk to the dbas who are, but theres a good chance they won't want to make any changes.

The restore itself is managed by the following powershell script:

```
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True)]
[string[]]$dbNames
)
$sourcePath = "C:\SQL\Backups\"
$baseDbFolder = "C:\SQL\Data\"
$dbServer = "MyServer"
$dbToRestore = "SourceDatabase"

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

if ( Get-Command "Get-PSSnapin" -errorAction SilentlyContinue -and Get-PSSnapin -Registered | where {$_.name -eq 'SqlServerCmdletSnapin100'} )
{
if( !(Get-PSSnapin | where {$_.name -eq 'SqlServerCmdletSnapin100'}))
{
Add-PSSnapin SqlServerCmdletSnapin100 | Out-Null
} ;
}
else
{
if (Get-Command "Get-PSSnapin" -errorAction SilentlyContinue)
{
if( !(Get-Module | where {$_.name -eq 'sqlps'}))
{
Import-Module

As Steve said in the comments make sure instant file initialization is turned on. And Stings answer discussed multiple files and compression but unfortunately those are going to be on the BACKUP side.

But you can also try modifying the restore command with the BLOCKSIZE, BUFFERCOUNT and MAXTRANSFERSIZE options of the RESTORE command.

Unfortunately the BOL for RESTORE doesn't say much about them so you have to look at the BOL for the BACKUP DATABASE command.

• BLOCKSIZE Specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise.

-
BUFFERCOUNT Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.
The total space used by the buffers is determined by: buffercount * maxtransfersize.

-
MAXTRANSFERSIZE Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

This post had a nice analogy and warning for MAXTRANSFERSIZE and BUFFERCOUNT


This can be a tricky option to use, specifying to many buffers can lead to “Out of memory” errors! Always use this option with care! The total memory used by the recovery process is MaxTransferSize x BufferCount = Memory needed by restore if you do not have the needed amount of memory on your server you will get errors!


You can think of the SQL Server restore process as using buckets to put out a fire.


The BufferCount sets the number of buckets to use to put out the fire while the MaxTransferSize sets how full these buckets should be. As you can imagine using many buckets and only filling them half can impact the time it takes you to put out the fire, just like using only a few buckets but filling them all the way to the top can have a negative impact if the buckets are too heavy to lift.
Setting the MaxTransferSize and BufferCount options can be different for every environment since you need to optimize the values to match your storage and memory configuration. So play around with different values until you get your optimized restore time!

As he said at the end the use of these will be different per server so you will have to play with them somewhat to get the optimal restore speed.

And here is a great question that goes over using them in a fair amount of detail. It is talking about BACKUPS but again, the options are the same.

Edit I've been looking for this all day. Nic Cain has a script that will try a backup out over and over again with variations on the settings to help you find the best results. Here is the link. You should easily be able to modify this script to rest RESTOREs instead.

StackExchange Database Administrators Q#131693, answer score: 3