3

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 'sqlps' -DisableNameChecking ;
        }
    } ;
}

$sourcePathFull = join-path (join-path $sourcePath (join-path Full $dbToRestore)) *.bak
$sourcePathDiff = join-path (join-path $sourcePath (join-path Diff $dbToRestore)) *.bak
$sourceFileFull = gci $sourcePathFull | sort LastWriteTime | select -last 1
$sourceFileDiff = gci $sourcePathDiff | sort LastWriteTime | select -last 1
if ((Get-Item $sourceFileFull).LastWriteTime -gt (Get-Item $sourceFileDiff).LastWriteTime)
{
    $sourceFileDiff = $null
}

function GetCreateDbSql ([string]$baseDbFolder, [string]$dbName)
{
return @"
USE [master]
GO
IF db_id('$dbName') IS NOT NULL
    SET NOEXEC ON
GO
CREATE DATABASE [$dbName] ON  PRIMARY 
( NAME = N'AFO_PRIMARY', FILENAME = N'$baseDbFolder$dbName.mdf' , SIZE = 16240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'AFO_log', FILENAME = N'$baseDbFolder$dbName.ldf' , SIZE = 1240KB , MAXSIZE = 2GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'$dbName', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [$dbName].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [$dbName] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$dbName] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$dbName] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$dbName] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$dbName] SET ARITHABORT OFF
GO
ALTER DATABASE [$dbName] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$dbName] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$dbName] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$dbName] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$dbName] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$dbName] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [$dbName] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$dbName] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$dbName] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$dbName] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$dbName] SET  DISABLE_BROKER
GO
ALTER DATABASE [$dbName] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$dbName] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$dbName] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [$dbName] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE [$dbName] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$dbName] SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE [$dbName] SET  READ_WRITE
GO
ALTER DATABASE [$dbName] SET RECOVERY SIMPLE
GO
ALTER DATABASE [$dbName] SET  MULTI_USER
GO
ALTER DATABASE [$dbName] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [$dbName] SET DB_CHAINING OFF
GO
ALTER DATABASE [$dbName] SET RECOVERY SIMPLE
GO
SET NOEXEC OFF
"@
}
function GetRestoreDbSql ([string]$fullBackupFile, [string]$diffBackupFile, [string] $baseDbFolder, [string]$dbName)
{
    $singleUser = @"
USE [master]

Alter Database [$dbName]
  SET SINGLE_USER With ROLLBACK IMMEDIATE
"@
    if ([string]::IsNullOrEmpty($diffBackupFile)) {
    $restoreFull = @"

RESTORE DATABASE [$dbName] FROM  DISK = N'$fullBackupFile' WITH  FILE = 1, RECOVERY, REPLACE,  STATS = 10
    , MOVE 'AFO_PRIMARY' TO '$baseDbFolder$dbName.mdf'
    , MOVE 'AFO_log' TO '$baseDbFolder$dbName.ldf'
"@
    } else {
    $restoreFull = @"

RESTORE DATABASE [$dbName] FROM  DISK = N'$fullBackupFile' WITH  FILE = 1, NORECOVERY, REPLACE,  STATS = 10
    , MOVE 'AFO_PRIMARY' TO '$baseDbFolder$dbName.mdf'
    , MOVE 'AFO_log' TO '$baseDbFolder$dbName.ldf'
"@
    }
    $restoreDiff = if ([string]::IsNullOrEmpty($diffBackupFile)) { "" } else { @"

RESTORE DATABASE [$dbName] FROM  DISK = N'$diffBackupFile' WITH  FILE = 1, STATS = 10, RECOVERY
"@ }    
    return "$singleUser$restoreFull$restoreDiff"
}
function GetTempSqlFilename ([string]$filename)
{
    return "$env:TEMP\$filename"
}
function WriteTempSqlFile ([string]$sql, [string]$filename)
{
    $stream = [System.IO.StreamWriter] $filename
    $stream.Write($sql)
    $stream.Close()
}

foreach ($dbName in $dbNames) {
    $createDbSqlFilename = GetTempSqlFilename "$dbName.create.sql"
    $restoreDbSqlFilename = GetTempSqlFilename "$dbName.restore.sql"
    $createDbSql = GetCreateDbSql $baseDbFolder $dbName
    $fileDiff = if ($fullOnly -eq $TRUE) { $null } else { $sourceFileDiff }
    $restoreDbSql = GetRestoreDbSql $sourceFileFull $fileDiff $baseDbFolder $dbName
    WriteTempSqlFile $createDbSql $createDbSqlFilename
    #Write-Host $createDbSqlFilename
    Invoke-Sqlcmd -InputFile $createDbSqlFilename -ServerInstance $dbServer -QueryTimeout 600
    WriteTempSqlFile $restoreDbSql $restoreDbSqlFilename
    #Write-Host $restoreDbSqlFilename
    Invoke-Sqlcmd -InputFile $restoreDbSqlFilename -ServerInstance $dbServer -QueryTimeout 600
}

Within the limits of my control (i.e. the server itself and the restore scripts), is there any way to make improvements to the performance of the restore?

4
  • You can try to restore on SSD server, detach the new db, copy and rename its files X times to the various X location and attach it again. A copy should be faster. If you have N servers, you can have N thread in parralel doing the restore (VS one by one) Mar 9, 2016 at 12:40
  • ONe database per developer? Why don't they just use their local Developer/Express Edition instead? Mar 9, 2016 at 13:45
  • @spaghettidba its a SQL2k database running 2008 R2 - it also has legacy syntax. Most developers have higher versions of SQL running, so having it locally isn't really an option. Most of them need up to date data as per QA, because the scenarios are complex. It's a dev instance they are restored on though. Mar 9, 2016 at 13:48
  • SQL Server can backup and restore at device speed so doing a 2-step is unlikely save you much, if anything at all, if your bottleneck is IO. Where are your backup files stored? Are they separate from the physical disk (assuming not using SAN/NAS) where you are restoring to? Assuming you have lots of network bandwidth, restoring across the wire can be faster if you don't have multiple local disks but parallel restores over the network might be worse (Window network IO oddity). Also, have you checked to see if you're bottlenecked on IO or is something else slowing you down?
    – SQLmojoe
    Mar 9, 2016 at 16:41

3 Answers 3

3

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.

1

To reduce BACKUP/RESTORE times, try multiple T-SQL disks and compression

I've noticed significant speed gains for both backups and restores by adding multiple disks in the backup and restore T-SQL commands. Before you modify your PS scripts try this basic test and time the outcome. Try using compression as well. Get with your DBAs and show them the difference. Speaking as DBA myself these changes are easy to implement. If they can't lift a finger to make these minor changes, it would make me wonder what they actually do.

Option A:

Use your source system to backup the database to the target QA system using multiple file disks and compression to minimize the I/O and maximize the threading:

On Source SQL Server:

BACKUP DATABASE [MyDatabase] 
to  **DISK** = N'\\\QASystem\ShareLocation\FullBackupFile1.bak'
, **DISK** = N'\\\QASystem\ShareLocation\FullBackupFile2.bak' 
WITH INIT, COMPRESSION

On Target SQL Server:

RESTORE DATABASE [MyDatabase] 
FROM **DISK** = N'X:\LocalDirectory\FullBackupFile1.bak'
, **DISK** = N'X:\LocalDirectory\FullBackupFile2.bak' WITH  FILE = 1
, RECOVERY
, REPLACE
, STATS = 10
, MOVE 'AFO_PRIMARY' TO 'MyDatabase.mdf'
, MOVE 'AFO_log' TO 'MyDatabase.ldf'

Option B:

If your network throughput is really high--ours is 10GB, it might behoove you to use the SSD disk as the restore location for the files instead of backing them up to the slower disk over the network.

On Source SQL Server:

BACKUP DATABASE [MyDatabase] 
to **DISK** = N'Z:\SSDLocalDirectory\FullBackupFile1.bak'
, **DISK** = N'Z:\SSDLocalDirectory\FullBackupFile2.bak' 
WITH INIT, COMPRESSION

On Target SQL Server:

RESTORE DATABASE [MyDatabase] 
FROM **DISK** = N'\\\SSDSystem\ShareLocation\FullBackupFile1.bak'
, **DISK** = N'\\\SSDSystem\ShareLocation\FullBackupFile2.bak' WITH  FILE = 1
, RECOVERY
, REPLACE
, STATS = 10      
, MOVE 'AFO_PRIMARY' TO 'MyDatabase.mdf' 
, MOVE 'AFO_log' TO 'MyDatabase.ldf'

Note: If you notice a performance gain, experiment by adding more disk files to see if it improves further.

Option C:

I don't think this is what I'd pick but it does specifically answer your question as well and might bypass the stubborn DBA. With SQL Server 2008 R2 and above you can create databases that are attached to file share storage. In theory you could share the SSD disk and have the databases live on an actual database share. I would imagine backups would be much faster there, but your network might get saturated depending on how much you access those databases. Using this strategy detach/re-attach would also be an option.

https://support.microsoft.com/en-us/kb/304261

0

All the answers given above are about improving speed on one database restore. But from your question, I know you are also concerned about the restore scalability, i.e. multiple db restore. I actually encountered the similar issue before, and my solution is to do parallel restore with the help of PowerShell workflow.

You can check the link here

PowerShell to automate multiple SQL Server database restores to refresh environments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.