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?
