We all know how to restore SQL Server databases from a backup but being the automation fanatic that I am, I wanted a method in PowerShell to quickly build a “clone” instance with all the databases as backed up from production. I wanted to do this with minimal number of parameters possible.
The criteria that I set forth for myself are
1) Be able to restore one or more databases from latest full-backup
2) Have the script detect the latest backup & location
3) Have the script detect the backup version the target databases were restored from
4) Restore in target only if backup used was not the latest (optionationy all force overwrite again)
5) If there was a failure, resume from where it left off last time
6) If permissions in target databases
were different, optionally, preserve them after restore
7) Produce a detailed report of what was done
Without further delay, let us see the script that uses SQLPSX module
<#
.SYNOPSIS
Given a source server and target server, restores backups of given databases from source to target
.DESCRIPTION
Detects the backup location of given databases on source server and restores to target
.INPUTS
.OUTPUTS
.EXAMPLE
Restore-SQLDatabase -SourceSQLServer "MyProdInstance" -TargetSQLServer "MyDevInstance" -Database "MyDB1" -Verbose
This example does a simple restore of a specific database from source to destination and requests verbose output
.EXAMPLE
(Restore-SQLDatabase -SourceSQLServer "MyProdInstance" -TargetSQLServer "MyDevInstance" -Database "*" -Verbose -force) 4>C:\_dblog\Refresh.log
This example does a restore of all databases in source and redirects the verbose output to a file
.NOTES
Version History
v1.0 - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com] - Initial Release
.LINK
#>
function Restore-SQLDatabase
{
#[CmdletBinding(SupportsShouldProcess=$True)] #For WhatIf and Confirm preference support!
[CmdletBinding()]
param
(
[Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[System.String[]] $Database,
[Parameter(Position=1, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[System.String] $SourceSQLServer,
[Parameter(Position=2, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[System.String] $TargetSQLServer,
[Parameter(Position=3, Mandatory=$false, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[System.String] $PermissionsScript,
[Parameter(Position=4, Mandatory=$false, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[Switch] $force,
[Parameter(Position=5, Mandatory=$false, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
[Switch] $StopOnError = $true
)
# Executes once before first item in pipeline is processed
Begin
{
# Declare all variables with the right datatypes to avoid bugs due to PowerShell not casting properly
[string[]] $databases = @()
[string] $dbPermissionsLogFile = "c:\_dblog\permissions.log"
#Hashtables (keyed by string)
$dbSourceInfo = @{}
$dbTargetInfo = @{}
$dbSkipReasons = @{}
$dbErrorReasons = @{}
$dbRestoreReasons = @{}
[string] $sqlLatestFullBackupInfo = $null
[string] $sqlLatestRestoreInfo = $null
[string] $operation = "[UN-INITIALZIED]"
[string] $subOperation = "[UN-INITIALZIED]"
[string] $error = ""
[string] $fatalError = ""
[Microsoft.SqlServer.Management.Smo.SqlSmoObject] $source = $null;
[Microsoft.SqlServer.Management.Smo.SqlSmoObject] $target = $null;
[System.Data.DataRow[]] $backupInfo = $null;
[System.Data.DataRow[]] $restoreInfo = $null;
[string] $db = $null;
[bool] $canRestore = $false;
[int] $restoredCount = 0;
[Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase] $user = $null;
[string] $userName = $null;
[string[]] $usersToDrop = @();
$sqlLatestFullBackupInfo =
#------------------ Begin SQL ---------------------------
@"
SELECT DatabaseName = x.database_name,
LastBackupFileName = x.physical_device_name,
LastBackupDatetime = x.backup_start_date
FROM ( SELECT bs.database_name,
bs.backup_start_date,
bmf.physical_device_name,
Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'D'
AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1
AND EXISTS
(
SELECT *
FROM sys.databases d
WHERE d.name = x.database_name
)
AND database_name NOT IN ('master','model','msdb','tempdb')
"@
#------------------ End SQL ---------------------------
$sqlLatestRestoreInfo =
#------------------ Begin SQL ---------------------------
@"
/*
#http://www.mssqltips.com/sqlservertip/1860/identify-when-a-sql-server-database-was-restored-the-source-and-backup-date/
*/
SELECT x.*
FROM (SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore],
Ordinal = ROW_NUMBER() OVER( PARTITION BY [rs].[destination_database_name] ORDER BY rs.restore_date DESC )
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id] ) X
WHERE x.Ordinal = 1
AND EXISTS
(
SELECT *
FROM sys.databases d
WHERE d.name = x.destination_database_name
AND name NOT IN ('master','model','msdb','tempdb')
)
"@
#------------------ End SQL ---------------------------
try
{
Write-Verbose "----------------------------------------"
Write-Verbose "Source: $SourceSQLServer"
Write-Verbose "Target: $TargetSQLServer"
Write-Verbose "----------------------------------------"
$operation = "Connect to source server $SourceSQLServer"
#----------------------------------------
Write-Verbose "$operation"
$source = Get-SqlServer -sqlserver $SourceSQLServer
$operation = "Connect to target server $TargetSQLServer"
#----------------------------------------
Write-Verbose "$operation"
$target = Get-SqlServer -sqlserver $TargetSQLServer
$operation = "Identify qualifying source databases"
#----------------------------------------
Write-Verbose "$operation"
if ($Database -eq '*')
{
foreach($db in $source.Databases)
{
if ($db -notin 'master','model','msdb','tempdb')
{
#TODO: Hack to remove surrounding square brackets - need to rework
$databases += $db.Replace("[","").Replace("]","");
}
}
}
else
{
$databases = $Database
}
Write-Verbose "Found $($databases.Count) matche(s) - $databases"
Write-Verbose "----------------------------------------"
$operation = "$SourceSQLServer - Collect source database backup information"
Write-Verbose "$operation"
Write-Verbose "----------------------------------------"
$backupInfo = Get-SqlData -sqlserver $source -dbname "master" -qry $sqlLatestFullBackupInfo -Verbose:$PSBoundParameters.Verbose
#$backupInfo = Invoke-Sqlcmd2 -ServerInstance $SourceSQLServer -Database "master" -Query $sqlLatestFullBackupInfo -As DataRow -Verbose:$PSBoundParameters.Verbose
foreach($row in $backupInfo)
{
$db = $row["DatabaseName"]
#We only care about the databases we need to work with (and not all databases)
if ($databases -contains $db)
{
$subOperation = "Found backup information for $db"
Write-Verbose "$subOperation"
$dbSourceInfo.Add($db,
(New-Object PSObject -Property @{
DatabaseName = $db
LastBackupDateTime = $row["LastBackupDateTime"]
LastBackupFileName = $row["LastBackupFileName"]
BackupFileExists = (Test-Path -Path $row["LastBackupFileName"] -PathType Leaf)
}
))
Write-Verbose $dbSourceInfo[$db]
}
}
Write-Verbose "----------------------------------------"
$operation = "$TargetSQLServer - Collect the target database restore information"
Write-Verbose "$operation"
Write-Verbose "----------------------------------------"
$restoreInfo = Get-SqlData -sqlserver $target -dbname "master" -qry $sqlLatestRestoreInfo -Verbose:$PSBoundParameters.Verbose
#$restoreInfo = Invoke-Sqlcmd2 -ServerInstance $TargetSQLServer -Database "master" -Query $sqlLatestRestoreInfo -As DataRow -Verbose:$PSBoundParameters.Verbose
foreach($row in $restoreInfo)
{
$db = $row["destination_database_name"]
#We only care about the databases we need to work with (and not all databases)
if ($databases -contains $db)
{
$subOperation = "Found restore information for $db"
Write-Verbose "$subOperation"
$dbTargetInfo.Add($db,
(New-Object PSObject -Property @{
DatabaseName = $db
LastRestoreDateTime = $row["restore_date"]
SourceBackupStartDateTime = $row["backup_start_date"]
SourceBackupEndDateTime = $row["backup_finish_date"]
SourceDatabaseName = $row["source_database_name"]
SourceBackupFileName = $row["backup_file_used_for_restore"]
}
))
Write-Verbose $dbTargetInfo[$db]
}
}
Write-Verbose "----------------------------------------"
$operation = "Perform validations"
Write-Verbose "$operation"
Write-Verbose "----------------------------------------"
#Make sure the permissions script exists
if ($PermissionsScript)
{
if (-not (Test-Path $PermissionsScript -PathType Leaf))
{
throw [System.IO.FileNotFoundException] "If specified, permissions script file [$PermissionsScript] must exist and it does not!"
}
}
#TODO: List of things to do
#----------------------------------------
#Make sure the source server exists and can connect
#Make sure the target server exists and can connect
#Make sure the database(s) exist in the source
#Make sure the source database has a valid backup
#Make sure backup file(s) for the source database actually physically exist
#Make sure prod is not the target (unless overridden)
#Source can be the same as taget as long as a flag confirms it
#If the target was already restored from the same source backup, need a -Force switch to re-restore
# (helps with re-running the restore upon failures without having to worry about excluding already succeeded restores)
}
catch
{
#Any error that happens in the "Begin" block is considered fatal. "Process" and "End" blocks will do nothing upon encountering fatal errors
$fatalError = $_.Exception;
Write-Verbose "ERROR: When performing $operation & suboperation $subOperation - $($fatalError)"
Write-Error "ERROR: When performing $operation & suboperation $subOperation - $($fatalError)"
}
finally
{
} #try
}
Process
{
if (-not $fatalError)
{
Write-Verbose "----------------------------------------"
$operation = "Restore qualifying databases"
Write-Verbose "$operation"
Write-Verbose "----------------------------------------"
foreach($db in $databases)
{
try
{
$subOperation = "Restore - $db [$($databases.IndexOf($db)+1) of $($databases.Count)]"
Write-Verbose $subOperation
Write-Verbose "----------------------------------------"
# Make the assumption that we will not be able to restore
$canRestore = $false;
$subOperation = "Determine if it is possible to restore database - $db"
Write-Verbose $subOperation
#Check to see if we have a backup
if (-not $dbSourceInfo.ContainsKey($db))
{
$dbSkipReasons.Add($db, "Source SQL Server $SourceSQLServer does not have database $db or is inaccessible!")
}
else
{
$subOperation = "Check if backup exists for database - $db"
Write-Verbose $subOperation
#Does the backup file(s) physically exist?
if (-not $dbSourceInfo[$db].BackupFileExists)
{
$dbSkipReasons.Add($db, "Backup for source server $SourceSQLServer database $db does not exist!")
}
else
{
if ($dbTargetInfo.ContainsKey($db))
{
if ($dbSourceInfo[$db].LastBackupFileName -eq $dbTargetInfo[$db].SourceBackupFileName)
{
if (-not $force)
{
$dbSkipReasons.Add($db, "Skipping restore since that latest restore of $db is also from backup file $($dbSourceInfo[$db].LastBackupFileName) dated $($dbSourceInfo[$db].LastBackupDateTime). Use -force switch to restore same backup forcibly!")
}
else
{
$dbRestoreReasons.Add($db, "Restore already done from same source but forcing another refresh for -force switch");
$canRestore = $true
}
}
else
{
$dbRestoreReasons.Add($db, "Restore is being done because the prior restore is not from latest backup")
$canRestore = $true
}
}
else
{
$dbRestoreReasons.Add($db, "Restore is being done because database does not exist in target")
$canRestore = $true
} #end - else if ($dbTargetInfo.ContainsKey($db))
} #end - else if (-not $dbSourceInfo[$db].BackupFileExists)
} #end - else if (-not $dbSourceInfo.ContainsKey($db))
# We know if we have what we need to restore
if (-not $canRestore)
{
Write-Verbose "WARNING: $($dbSkipReasons[$db])"
Write-Verbose "WARNING: SKIPPED restore for $db for above reason!"
}
else
{
#-----------------------------------------
#Restore: Prework
#-----------------------------------------
$subOperation = "Restore $db for reason: $($dbRestoreReasons[$db])"
Write-Verbose $subOperation
# If DB exists in target, script out permissions before restoring, then drop the database
if ($dbTargetInfo.ContainsKey($db))
{
if ($PermissionsScript)
{
$subOperation = "Script out privileges for database $db in $targetSQLServer to $dbPermissionsLogFile"
Write-Verbose $subOperation
#-----------------------------------------
(Get-SqlData -sqlserver $target -dbname $db -qry (Get-Content -Path $PermissionsScript | Out-String) -Verbose) 4> $dbPermissionsLogFile
#(Invoke-Sqlcmd2 -ServerInstance $TargetSQLServer -Database $db -Query (Get-Content -Path $PermissionsScript | Out-String) -Verbose) 4> $dbPermissionsLogFile
}
$subOperation = "Drop database $db in $targetSQLServer"
Write-Verbose $subOperation
#-----------------------------------------
$target.KillDatabase($db)
}
#-----------------------------------------
#Actual restore operation happens here!!
#-----------------------------------------
$subOperation = "Restore $db from backup file $($dbSourceInfo[$db].LastBackupFileName) dated $($dbSourceInfo[$db].LastBackupDateTime)"
Write-Verbose $subOperation
Invoke-SqlRestore -sqlserver $target -dbname $db -filepath $dbSourceInfo[$db].LastBackupFileName `
-action Database -force -Verbose:$PSBoundParameters.Verbose
# If we got up to this point, we did a successful restore (although there might be errors in the post-restore) process
$restoredCount++
#-----------------------------------------
#Restore: Postwork
#-----------------------------------------
# If DB existed in target, restore the scripted out permissions before we dropped the database
if ($dbTargetInfo.ContainsKey($db))
{
$subOperation = "Drop users in database $db in $targetSQLServer"
Write-Verbose $subOperation
#-----------------------------------------
$usersToDrop = $null;
foreach($user in $target.Databases[$db].Users)
{
if (-not $user.IsSystemObject)
{
$usersToDrop += $user.Name
#Doing $user.Drop() within this loop will result in error "Collection was modified; enumeration operation may not execute"
#try{ $user.Drop() } catch { "WARNING: Unable to drop $user"}
}
}
if ($usersToDrop)
{
foreach($userName in $usersToDrop )
{
try
{
$subOperation = "Drop user $userName"
Write-Verbose $subOperation
Remove-SqlUser -sqlserver $target -dbname $target.Databases[$db] -name $userName
}
catch { Write-Verbose "WARNING: Unable to drop $userName - $($_.Exception.Message)"}
}
}
$subOperation = "Restore scripted out privileges for database $db in $targetSQLServer"
Write-Verbose $subOperation
#-----------------------------------------
if ($PermissionsScript)
{
$subOperation = "Restoring permissions for database from $dbPermissionsLogFile"
Write-Verbose $subOperation
Get-SqlData -sqlserver $target -dbname $db -qry (Get-Content -Path $dbPermissionsLogFile | Out-String) -Verbose:$PSBoundParameters.Verbose
#Invoke-Sqlcmd2 -ServerInstance $TargetSQLServer -Database $db -Query (Get-Content -Path $dbPermissionsLogFile | Out-String) -Verbose:$PSBoundParameters.Verbose
}
} #end - if ($dbTargetInfo.ContainsKey($db))
} #end - else if (-not $canRestore)
}
catch
{
$error = $_.Exception
$dbErrorReasons.Add($db, $error);
if ($StopOnError)
{
Write-Verbose "ERROR: $($error). Requested halting processing upon error! Stopping immediately."
Write-Error "ERROR: When performing $operation & suboperation $subOperation - $($error)"
break;
}
}
finally
{
} #try
} #Foreach $db
} #if (-not $fatalError)
} #Process
# Executes once after last pipeline object is processed
End
{
try
{
if (-not $fatalError)
{
$operation = "Put summary information together to return"
Write-Verbose "$operation"
Write-Verbose "Summary:"
Write-Verbose "Total databases qualified for restore: $($databases.Count)"
Write-Verbose "Total databases skipped: $($dbSkipReasons.Count)"
Write-Verbose "Total databases w/error: $($dbErrorReasons.Count) (may overlap with skipped)"
Write-Verbose "Total databases restored: $restoredCount (may overlap with errors post-restore"
#Produce a returnable report of custom PS objects
#------------------------------------------------
foreach($db in $databases)
{
$subOperation = "Summarizing for database - $db"
Write-Verbose $subOperation
#These variables are only used for the purpose of this report
$DatabaseName = $null
$Status = $null
$ReasonRestored = $null
$ReasonSkipped = $null
$ReasonError = $null
$ThisBackupTime = $null
$ThisBackupFileName = $null
$ThisBackupFileExists = $null
$PrevRestoreDateTime = $null
$PrevRestoredBackupStartDateTime = $null
$PrevRestoredBackupEndDateTime = $null
$PrevRestoredSourceDatabaseName = $null
$PrevRestoredBackupFileName = $null
# Statuses ------------------------
$DatabaseName = $db
if ($dbRestoreReasons.ContainsKey($db))
{
$ReasonRestored = $dbRestoreReasons[$db]
}
if ($dbSkipReasons.ContainsKey($db))
{
$ReasonSkipped = $dbSkipReasons[$db]
}
if ($dbErrorReasons.ContainsKey($db))
{
$ReasonError = $dbErrorReasons[$db]
}
if (($Status -eq $null) -and $ReasonError) {$Status = 'ERROR'}
if (($Status -eq $null) -and $ReasonSkipped) {$Status = 'SKIPPED'}
if (($Status -eq $null) -and $ReasonRestored) {$Status = 'SUCCESSFUL'}
if (($Status -eq $null)) {$Status = 'ABORTED'}
# Source backup info ------------------------
if ($dbSourceInfo.ContainsKey($db))
{
$ThisBackupTime = $dbSourceInfo[$db].LastBackupDateTime
$ThisBackupFileName = $dbSourceInfo[$db].LastBackupFileName
$ThisBackupFileExists = $dbSourceInfo[$db].BackupFileExists
}
# Prior target restore info ------------------------
if ($dbTargetInfo.ContainsKey($db))
{
$PrevRestoreDateTime = $dbTargetInfo[$db].LastRestoreDateTime
$PrevRestoredBackupStartDateTime = $dbTargetInfo[$db].SourceBackupStartDateTime
$PrevRestoredBackupEndDateTime = $dbTargetInfo[$db].SourceBackupEndDateTime
$PrevRestoredSourceDatabaseName = $dbTargetInfo[$db].SourceDatabaseName
$PrevRestoredBackupFileName = $dbTargetInfo[$db].SourceBackupFileName
}
#This is what gets returned to the caller
New-Object PSObject -Property @{
DatabaseName = $DatabaseName
Status = $Status
ReasonRestored = $ReasonRestored
ReasonSkipped = $ReasonSkipped
ReasonError = $ReasonError
ThisBackupTime = $ThisBackupTime
ThisBackupFileName = $ThisBackupFileName
ThisBackupFileExists = $ThisBackupFileExists
PrevRestoreDateTime = $PrevRestoreDateTime
PrevRestoredBackupStartDateTime = $PrevRestoredBackupStartDateTime
PrevRestoredBackupEndDateTime = $PrevRestoredBackupEndDateTime
PrevRestoredSourceDatabaseName = $PrevRestoredSourceDatabaseName
PrevRestoredBackupFileName = $PrevRestoredBackupFileName
}
} #foreach($db in $databases)
} #if (-not $fatalError)
} #try
catch
{
$error = $_.Exception
Write-Verbose "ERROR: $($error). Requested halting processing upon error! Stopping immediately."
Write-Error "ERROR: When performing $operation & suboperation $subOperation - $($error)"
}
finally
{
Write-Verbose "----------------------------------------"
Write-Verbose "Restore operation completed. Please check statuses."
Write-Verbose "----------------------------------------"
} #try
} #End block
}
The permissions script that I used to script out database permissions is at SQLCentral.com
http://www.sqlservercentral.com/scripts/Security/71562/
The usage examples are shown at the top of the script. Once you run in the cmdlet, you can also get help to see the sytax and usage
PS C:\Users\MyId> Get-Help Restore-SQLDatabase
NAME
Restore-SQLDatabase
SYNOPSIS
Given a source server and target server, restores backups of given databases from source to target
SYNTAX
Restore-SQLDatabase [-Database] <String[]> [-SourceSQLServer] [-TargetSQLServer] [[-PermissionsScript] ] [[-force]]
[[-StopOnError]] []
DESCRIPTION
Detects the backup location of given databases on source server and restores to target
RELATED LINKS
REMARKS
To see the examples, type: "get-help Restore-SQLDatabase -examples".
For more information, type: "get-help Restore-SQLDatabase -detailed".
For technical information, type: "get-help Restore-SQLDatabase -full".
PS C:\Users\MyId>
Have fun cloning your SQL Server databases!
SQL Jana 