Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Restore-HallengrenBackups.ps1
<#
.SYNOPSIS
Restores SQL Server databases from the backup directory structure created by Ola Hallengren's database maintenance scripts.
.DESCRIPTION
Many SQL Server database administrators use Ola Hallengren's SQL Server Maintenance Solution which can be found at http://ola.hallengren.com
Hallengren uses a predictable backup structure which made it relatively easy to create a script that can restore an entire SQL Server database instance, down to the master database (next version), to a new server. This script is intended to be used in the event that the originating SQL Server becomes unavailable, thus rendering my other SQL restore script (http://goo.gl/QmfQ6s) ineffective.
.PARAMETER ServerName
Required. The SQL Server to which you will be restoring the databases.
.PARAMETER RestoreFromDirectory
Required. The directory that contains the database backups (ex. \\fileserver\share\sqlbackups\SQLSERVERA)
.PARAMETER IncludeSystemDBs
This switch will restore the master, model and msdb backups found within RestoreFromDirectory
.PARAMETER ReuseFolderStructure
Restore-HallengrenBackups.ps1 will restore to the default user data and log directories, unless this switch is used. Useful if you're restoring from a server that had a complex db file structure.
.PARAMETER IncludeDBs
Migrates ONLY specified databases. This list is auto-populated for tab completion.
.PARAMETER ExcludeDBs
Excludes specified databases when performing -AllUserDBs migrations. This list is auto-populated for tab completion.
.PARAMETER Force
Will overwrite any existing databases on $ServerName. Note that because of the potentially destructive nature of the nature of this script (when -IncludeSystemDBs is used), you shouldn't need force anything, as Restore-HallengrenBackups.ps1 is meant to be run on a clean SQL Server.
.NOTES
Author : Chrissy LeMaire
Requires: PowerShell Version 3.0, SMO, sysadmin access on destination SQL Server.
.LINK
http://gallery.technet.microsoft.com/scriptcenter/Restore-SQL-Backups-cd958ec1
.EXAMPLE
.\Restore-HallengrenBackups.ps1 -ServerName sqlcluster -RestoreFromDirectory \\fileserver\share\sqlbackups\SQLSERVER2014A -IncludeSystemDBs
Description
All databases, including master/msdb/model, contained within \\fileserver\share\sqlbackups\SQLSERVERA will be restored to sqlcluster, down the most recent full/differential/logs.
#>
#Requires -Version 3.0
[CmdletBinding(DefaultParameterSetName="Default", SupportsShouldProcess = $true)]
Param(
[parameter(Mandatory = $true)]
[string]$ServerName,
[parameter(Mandatory = $true)]
[string]$RestoreFromDirectory,
[parameter(Mandatory = $false)]
[switch]$IncludeSystemDBs,
[parameter(Mandatory = $false)]
[switch]$ReuseFolderStructure,
[parameter(Mandatory = $false)]
[switch]$force
)
DynamicParam {
if ($RestoreFromDirectory) {
$newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
$paramattributes = New-Object System.Management.Automation.ParameterAttribute
$paramattributes.ParameterSetName = "__AllParameterSets"
$paramattributes.Mandatory = $false
$systemdbs = @("master","msdb","model")
$argumentlist = (Get-ChildItem -Path $RestoreFromDirectory -Directory).Name | Where-Object { $systemdbs -notcontains $_ }
$validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $argumentlist
$combinedattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute]
$combinedattributes.Add($paramattributes)
$combinedattributes.Add($validationset)
$IncludeDBs = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("IncludeDBs", [String[]], $combinedattributes)
$ExcludeDBs = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("ExcludeDBs", [String[]], $combinedattributes)
$newparams.Add("IncludeDBs", $IncludeDBs)
$newparams.Add("ExcludeDBs", $ExcludeDBs)
return $newparams
}
}
BEGIN {
Function Restore-SQLSystemDatabase {
<#
.SYNOPSIS
Restores master, model and msdb.
.EXAMPLE
Restore-SQLSystemDatabase $servername $dbname $backupfile $systemdir
.OUTPUTS
$true if success
$true if failure
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$servername,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$dbname,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$backupfile,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$systemdir
)
$restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
$restore.ReplaceDatabase = $true
switch ($dbname) {
"master" { $datalogical = "master"; $dataphysical = "$systemdir\master.mdf"
$loglogical = "mastlog"; $logphysical = "$systemdir\mastlog.ldf" }
"model" { $datalogical = "modeldev"; $dataphysical = "$systemdir\model.mdf"
$loglogical = "modellog"; $logphysical = "$systemdir\modellog.ldf" }
"msdb" { $datalogical = "MSDBData"; $dataphysical = "$systemdir\MSDBData.mdf"
$loglogical = "MSDBLog"; $logphysical = "$systemdir\MSDBLog.ldf" }
}
$movedatafile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
$movedatafile.LogicalFileName = $datalogical
$movedatafile.PhysicalFileName = $dataphysical
$null = $restore.RelocateFiles.Add($movedatafile)
$movelogfile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
$movelogfile.LogicalFileName = $loglogical
$movelogfile.PhysicalFileName = $logphysical
$null = $restore.RelocateFiles.Add($movelogfile)
try {
$restore.ReplaceDatabase = $true
$restore.Database = $dbname
$restore.Action = "Database"
$restore.NoRecovery = $false
$device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem
$device.name = $backupfile
$device.devicetype = "File"
$restore.Devices.Add($device)
$restore.sqlrestore($servername)
return $true
} catch { return $false }
}
Function Restore-SQLMasterDB {
<#
.SYNOPSIS
Restoring the master database requires starting up in a specific mode. This function changes the startup params,
restarts sql, and restores the database.
.EXAMPLE
$result = Restore-SQLMasterDB $server $sqlservice $backupfile $startparams
.OUTPUTS
true or false
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$sqlservice,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$master,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$startparams
)
$systemdir = $server.MasterDBPath
$netbiosname = Get-NetBIOSName $server
$servername = $server.name
$instance = ($servername.split("\"))[1]
if ($instance -eq $null) { $instance = "MSSQLSERVER" }
Write-Host "Changing startup parameters to allow restore of master db." -ForegroundColor Yellow
$sqlservice.StartupParameters = "-m;$($startparams)"; $sqlservice.Alter()
Write-Warning "Attempting to restart SQL Services.."
if ($server.isclustered) {
try {
$remotesqlservice = Get-Service -ComputerName $netbiosname | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" }
$clusteredservices = Get-ClusterResource -Cluster $servername | Where-Object { $_.Name.StartsWith("SQL Server") -and $_.OwnerGroup -eq "SQL Server ($instance)" }
$clusteredservices | Stop-ClusterResource
try { $remotesqlservice | Start-Service } catch { throw "bullshit" }
} catch {
Write-Host "SQL Service is clustered and Service Control Manager could not be contacted. Quitting." -ForegroundColor Red
return $false
}
} else {
Stop-SQLService $sqlservice
Start-SQLservice $sqlservice
}
$sqlservice.StartupParameters = "-T3608;-m;$($startparams)"
$sqlservice.Alter()
Write-Host "Restoring master" -ForegroundColor Yellow
$result = Restore-SQLSystemDatabase $servername "master" $master $systemdir
return $result
}
Function Start-ReplaceSystemDBs {
<#
.SYNOPSIS
Performs all of the steps needed to restore master, msdb and model.
.EXAMPLE
Start-ReplaceSystemDBs $server $RestoreFromDirectory
.OUTPUTS
true or false
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$RestoreFromDirectory
)
$servername = $server.name
$netbiosname = Get-NetBIOSName $server
$instance = ($servername.split("\"))[1]
if ($instance -eq $null) { $instance = "MSSQLSERVER" }
$server.ConnectionContext.StatementTimeout = 0
$master = Get-ChildItem "$RestoreFromDirectory\master\FULL\*.bak" | sort LastWriteTime | select -last 1
$msdb = Get-ChildItem "$RestoreFromDirectory\msdb\FULL\*.bak" | sort LastWriteTime | select -last 1
$model = Get-ChildItem "$RestoreFromDirectory\model\FULL\*.bak" | sort LastWriteTime | select -last 1
Write-Host "Getting master backup version" -ForegroundColor Yellow
$restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
$device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem $master, "FILE"
$restore.Devices.Add($device)
$backupinfo = $restore.ReadBackupHeader($server)
$backupversion = [version]("$($backupinfo.SoftwareVersionMajor).$($backupinfo.SoftwareVersionMinor).$($backupinfo.SoftwareVersionBuild)")
if ($backupversion -ne $server.version) {
Write-Warning "Not the same version (backup: $backupversion, server: $($server.version)). Cannot restore system dbs."
return $false
}
if ($server.isclustered) {
try {
$remotesqlservice = Get-Service -ComputerName $netbiosname | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" }
$clusteredservices = Get-ClusterResource -Cluster $servername | Where-Object { $_.Name.StartsWith("SQL Server") -and $_.OwnerGroup -eq "SQL Server ($instance)" }
} catch {
Write-Host "SQL Service is clustered and Service Control Manager could not be contacted. Quitting." -ForegroundColor Red
return $false
}
}
Write-Host "Master backup version and SQL Server match. Going forward." -ForegroundColor Green
$null = [Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$sqlserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $netbiosname
$systemdir = $server.MasterDBPath
$sqlservice = ($sqlserver.Services | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" })
$sqlagent = ($sqlserver.Services | Where-Object { $_.DisplayName -eq "SQL Server Agent ($instance)" })
Write-Host "Backing up current master just in case.." -ForegroundColor Yellow
$currentmasterbackup = "$RestoreFromDirectory\master\FULL\ps-migrationmaster.bak"
$sql = "BACKUP DATABASE [master] TO DISK = N'$currentmasterbackup' WITH NOFORMAT, INIT"
try {
$server.databases['master'].ExecuteNonQuery($sql)
Write-Host "Successfully backed up master database" -ForegroundColor Green
} catch {
Write-Warning "Couldn't backup master. Aborting."
return $false
}
$startparams = $sqlservice.StartupParameters
if ($servername -eq 'sqlcluster') {
$startparams = "-dM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf;-eM:\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"
}
$result = Restore-SQLMasterDB $server $sqlservice $master $startparams
# IF RESTORE OF MASTER WENT SUCCESSFULLY
if ($result) {
$sql = "ALTER DATABASE msdb MODIFY FILE (NAME=MSDBData, FILENAME= '$systemdir\MSDBData.mdf');
ALTER DATABASE msdb MODIFY FILE (NAME=MSDBLog, FILENAME= '$systemdir\MSDBLog.ldf');
ALTER DATABASE model MODIFY FILE (NAME=modeldev, FILENAME= '$systemdir\model.mdf');
ALTER DATABASE model MODIFY FILE (NAME=modellog, FILENAME= '$systemdir\modellog.ldf');
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME= '$systemdir\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME= '$systemdir\templog.ldf');"
Write-Host "Master successfully restored! Restarting SQL Server." -ForegroundColor Green
Start-Sleep 5
} else {
Write-Host "Master could not be master restored." -ForegroundColor Red
Write-Host "Aborting system db restores and starting SQL Server in normal mode." -ForegroundColor Red
$sqlservice.StartupParameters = $startparams
try { Stop-SQLservice $sqlservice; Start-SQLservice $sqlservice } catch {}
return $false
}
Write-Host "Resetting startup to normal, and updating msdb, model and tempdb locations." -ForegroundColor Yellow
$timeout = new-timespan -Minutes 2
$sw = [diagnostics.stopwatch]::StartNew()
do {
try {
$server.ConnectionContext.Connect()
$server.databases['master'].ExecuteNonQuery($sql)
Write-Host "Master successfully updated with new system db locations! Restarting SQL Server." -ForegroundColor Green
$success = $true
} catch {
write-warning "Connect failed, retrying..";
if ($server.isclustered) {
if ($remotesqlservice.Status -ne "Stopped") { Stop-SQLservice $remotesqlservice $true }
Start-SQLservice $remotesqlservice $true
} else {
if ($sqlservice.ServiceState -ne "Stopped") { Stop-SQLservice $sqlservice }
Start-SQLservice $sqlservice
}
}
} until ($success -eq $true -or $sw.elapsed -gt $timeout)
$sqlservice.StartupParameters = $startparams; $sqlservice.Alter()
if ($server.isclustered) {
Stop-SQLService $remotesqlservice $true
} else { Stop-SQLservice $sqlservice }
Start-SQLservice $sqlservice
Write-Warning "Restoring msdb"
Restore-SQLSystemDatabase $server "msdb" $msdb $systemdir
Write-Warning "Restoring model"
Restore-SQLSystemDatabase $server "model" $model $systemdir
$sqlagent.start()
try { $server.databases.refresh()
foreach ($db in $server.databases) {
if ($db.status -ne "Normal") {
try { $dbname = $db.name
$sql = "ALTER DATABASE [$dbname] SET EMERGENCY; drop database [$dbname]"
$server.databases['master'].ExecuteNonQuery($sql)
} catch {}
}
}
} catch {}
Remove-Item $currentmasterbackup
Write-Host "Systems databases successfully restored! Moving on to user databases.." -ForegroundColor Green
return $true
}
Function Start-SQLService {
<#
.SYNOPSIS
Restarting SQL Server via SQL's WMI objects returns $true too quickly. This function waits until the SQL service has started or a timeout occurs.
.EXAMPLE
$result = Start-SQLService $sqlservice $clustered
.OUTPUTS
true or false
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$service,
[Parameter(Mandatory = $false)]
[bool]$clustered
)
$timeout = new-timespan -Seconds 30
$sw = [diagnostics.stopwatch]::StartNew()
if ($clustered) {
do {
$service.Refresh()
try {
$service.Start()
Write-Warning "Starting SQL.." }
catch { Write-Warning "Having trouble starting the SQL service. Let's try again." }
Start-Sleep 2
}
until ($service.Status -eq "Running" -or $sw.elapsed -gt $timeout)
$status = $service.Status
} else {
do {
$service.Refresh()
try {
$service.Start()
Write-Warning "Starting SQL.." }
catch {
Write-Warning "Having trouble starting the SQL service. Let's try again." }
Start-Sleep 2
}
until ($service.ServiceState -eq "Running" -or $sw.elapsed -gt $timeout)
$status = $service.ServiceState
}
if ($status -eq "Running") { return $true }
else { return $false }
}
Function Stop-SQLService {
<#
.SYNOPSIS
Restarting SQL Server via SQL's WMI objects returns $true too quickly. This function waits until the SQL service has stopped or a timeout occurs.
.EXAMPLE
$result = Stop-SQLService $sqlservice
.OUTPUTS
true or false
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$service,
[Parameter(Mandatory = $false)]
[bool]$clustered
)
$timeout = new-timespan -Seconds 30
$sw = [diagnostics.stopwatch]::StartNew()
if ($clustered) {
do {
$service.Refresh()
try {
$service.Stop()
Write-Warning "Stopping SQL.."
} catch { Write-Warning "Having trouble stopping the SQL service. Let's try again." }
Start-Sleep 2
}
until ($service.Status -eq "Stopped" -or $sw.elapsed -gt $timeout)
$status = $service.Status
} else {
do {
$service.Refresh()
try {
$service.Stop()
Write-Warning "Stopping SQL.."
} catch { Write-Warning "Having trouble stopping the SQL service. Let's try again." }
Start-Sleep 2
}
until ($service.ServiceState -eq "Stopped" -or $sw.elapsed -gt $timeout)
$status = $service.ServiceState
}
if ($status -eq "Stopped") { return $true }
else { return $false }
}
Function Drop-SQLDatabase {
<#
.SYNOPSIS
Uses SMO's KillDatabase to drop all user connections then drop a database. $server is
an SMO server object.
.EXAMPLE
Drop-SQLDatabase $server $dbname
.OUTPUTS
$true if success
$false if failure
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$dbname
)
try {
$server.KillDatabase($dbname)
$server.refresh()
Write-Host "Successfully dropped $dbname on $($server.name)." -ForegroundColor Green
return $true
}
catch { return $false }
}
Function Get-SQLFileStructures {
<#
.SYNOPSIS
Dictionary object that contains file structures for SQL databases
.EXAMPLE
$filestructure = Get-SQLFileStructures $server $dbname $filelist $ReuseFolderstructure
foreach ($file in $filestructure.values) {
Write-Host $file.physical
Write-Host $file.logical
Write-Host $file.remotepath
}
.OUTPUTS
Dictionary
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true,Position=0)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true,Position=1)]
[string]$dbname,
[Parameter(Mandatory = $true,Position=2)]
[object]$filelist,
[Parameter(Mandatory = $false,Position=3)]
[bool]$ReuseFolderstructure
)
$destinationfiles = @{};
$logfiles = $filelist | Where-Object {$_.Type -eq "L"}
$datafiles = $filelist | Where-Object {$_.Type -ne "L"}
$filestream = $filelist | Where-Object {$_.Type -eq "S"}
if ($filestream) {
$sql = "select coalesce(SERVERPROPERTY('FilestreamConfiguredLevel'),0) as fs"
$fscheck = $server.databases['master'].ExecuteWithResults($sql)
if ($fscheck.tables.fs -eq 0) { return $false }
}
# Data Files
foreach ($file in $datafiles) {
# Destination File Structure
$d = @{}
if ($ReuseFolderstructure -eq $true) {
$d.physical = $file.PhysicalName
} else {
$directory = Get-SQLDefaultPaths $server data
$filename = Split-Path $($file.PhysicalName) -leaf
$d.physical = "$directory\$filename"
}
$d.logical = $file.LogicalName
$destinationfiles.add($file.LogicalName,$d)
}
# Log Files
foreach ($file in $logfiles) {
$d = @{}
if ($ReuseFolderstructure) {
$d.physical = $file.PhysicalName
} else {
$directory = Get-SQLDefaultPaths $server log
$filename = Split-Path $($file.PhysicalName) -leaf
$d.physical = "$directory\$filename"
}
$d.logical = $file.LogicalName
$destinationfiles.add($file.LogicalName,$d)
}
return $destinationfiles
}
Function Get-SQLDefaultPaths {
<#
.SYNOPSIS
Gets the default data and log paths for SQL Server. Needed because SMO's server.defaultpath is sometimes null.
.EXAMPLE
$directory = Get-SQLDefaultPaths $server data
$directory = Get-SQLDefaultPaths $server log
.OUTPUTS
String with file path.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$filetype
)
switch ($filetype) { "data" { $filetype = "mdf" } "log" { $filetype = "ldf" } }
if ($filetype -eq "mdf") { $sql = "select SERVERPROPERTY('InstanceDefaultDataPath') as physical_name" }
else { $sql = "select SERVERPROPERTY('InstanceDefaultLogPath') as physical_name" }
$dataset = $server.databases['master'].ExecuteWithResults($sql)
$filepath = $dataset.Tables[0].physical_name
if ($filepath -ne [DBNull]::Value) { $filepath = $filepath.TrimEnd("\"); return $filepath }
else {
$dbname = "randomdb$(Get-Random)" #ensure there's at least one user db
$sql = "create database $dbname;
Declare @dbname varchar(255);
SET @dbname = (SELECT top 1 name FROM sys.databases where database_id > 4);
EXEC (N'select top 1 physical_name from ' + @dbname + '.sys.database_files where physical_name like ''%.$filetype''');
drop database $dbname"
$dataset = $server.databases['master'].ExecuteWithResults($sql)
$filepath = ($dataset.Tables[0].rows[0]).physical_name
if ($filepath -ne $null) { return Split-Path($filepath) }
else {return $null}
}
}
Function Join-AdminUNC {
<#
.SYNOPSIS
Parses a path to make it an admin UNC.
.EXAMPLE
Join-AdminUNC sqlserver C:\windows\system32
Output: \\sqlserver\c$\windows\system32
.OUTPUTS
String
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$servername,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$filepath
)
if (!$filepath) { return }
if ($filepath.StartsWith("\\")) { return $filepath }
if ($filepath -ne $null -and $filepath -ne [System.DBNull]::Value) {
$newpath = Join-Path "\\$servername\" $filepath.replace(':\','$\')
return $newpath
}
else { return }
}
Function Get-NetBIOSName {
<#
.SYNOPSIS
Takes a best guess at the NetBIOS name of a server.
.EXAMPLE
$sourcenetbios = Get-NetBIOSName $server
.OUTPUTS
String with netbios name.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server
)
$servernetbios = $server.ComputerNamePhysicalNetBIOS
if ($servernetbios -eq $null) {
$servernetbios = ($server.name).Split("\")[0]
$servernetbios = $servernetbios.Split(",")[0]
}
return $($servernetbios.ToLower())
}
Function Test-SQLSA {
<#
.SYNOPSIS
Ensures sysadmin account access on SQL Server. $server is an SMO server object.
.EXAMPLE
if (!(Test-SQLSA $server)) { throw "Not a sysadmin on $source. Quitting." }
.OUTPUTS
$true if syadmin
$false if not
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server
)
try {
$sql = "select IS_SRVROLEMEMBER ('sysadmin') as issysadmin"
$issysadmin = ($server.databases['master'].ExecuteWithResults($sql)).Tables.issysadmin
if ($issysadmin -eq $true) { return $true } else { return $false }
}
catch { return $false }
}
Function Restore-SQLDatabase {
<#
.SYNOPSIS
Restores .bak file to SQL database. Creates db if it doesn't exist. $filestructure is
a custom object that contains logical and physical file locations.
.EXAMPLE
$filestructure = Get-SQLFileStructures $sourceserver $destserver $ReuseFolderstructure
Restore-SQLDatabase $destserver $dbname $backupfile $filetype
.OUTPUTS
$true if success
$true if failure
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$server,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$dbname,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$backupfile,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[string]$filetype,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[object]$filestructure
)
$servername = $server.name
$server.ConnectionContext.StatementTimeout = 0
$restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
$restore.ReplaceDatabase = $true
foreach ($file in $filestructure.values) {
$movefile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
$movefile.LogicalFileName = $file.logical
$movefile.PhysicalFileName = $file.physical
$null = $restore.RelocateFiles.Add($movefile)
}
try {
$percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
}
$restore.add_PercentComplete($percent)
$restore.PercentCompleteNotification = 1
$restore.add_Complete($complete)
$restore.ReplaceDatabase = $true
$restore.Database = $dbname
$restore.Action = $filetype
$restore.NoRecovery = $true
$device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem
$device.name = $backupfile
$device.devicetype = "File"
$restore.Devices.Add($device)
Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
$restore.sqlrestore($servername)
Write-Progress -id 1 -activity "Restoring $dbname to $servername" -status "Complete" -Completed
return $true
} catch {$x = $_.Exception; return $x }
}
}
PROCESS {
<# ----------------------------------------------------------
Sanity Checks
- Is SMO available?
- Is the SQL Server reachable?
- Is the account running this script an admin?
- Is SQL Version >= 2000?
- Is $NetworkShare valid?
- If the SQL Server is clustered and you're replacing
systems dbs, are the failover cluster modules available?
---------------------------------------------------------- #>
if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null )
{ throw "Quitting: SMO Required. You can download it from http://goo.gl/R4yA6u" }
if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") -eq $null )
{ throw "Quitting: Extended SMO Required. You can download it from http://goo.gl/R4yA6u" }
if (!([string]::IsNullOrEmpty($RestoreFromDirectory))) {
if (!($RestoreFromDirectory.StartsWith("\\"))) {
throw "RestoreFromDirectory must be a valid UNC path (\\server\share)."
}
if (!(Test-Path $RestoreFromDirectory)) {
throw "$RestoreFromDirectory does not exist or cannot be accessed."
}
}
Write-Host "Attempting to connect to SQL Server.." -ForegroundColor Green
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
try { $server.ConnectionContext.Connect() } catch { throw "Can't connect to $ServerName. Quitting." }
$server.ConnectionContext.StatementTimeout = 0
if ($server.isclustered -and $IncludeSystemDBs) {
try { $null = Get-ClusterResource -Cluster $servername }
catch {
throw "The SQL Server is clustered and you do not have Failover Clustering Management Tools installed or the cluster service cannot be accessed. `nTry: Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools"
}
}
if (!(Test-SQLSA $server)) { throw "Not a sysadmin on $ServerName. Quitting." }
if ($sourceserver.versionMajor -lt 8 -and $server.versionMajor -lt 8) {
throw "This script can only be run on SQL Server 2000 and above. Quitting."
}
if ($sourceserver.versionMajor -eq 8 -and $IncludeSystemDBs) {
throw "Migrating system databases not supported in SQL Server 2000."
}
# Convert from RuntimeDefinedParameter object to regular array
if ($IncludeDBs.Value -ne $null) {$IncludeDBs = @($IncludeDBs.Value)} else {$IncludeDBs = $null}
if ($ExcludeDBs.Value -ne $null) {$ExcludeDBs = @($ExcludeDBs.Value)} else {$ExcludeDBs = $null}
$dblist = @(); $skippedb = @{}; $migrateddb = @{}; $restoredb = @{}; $sysdbcollection = @{}; $userdbcollection = @{}
$systemdbs = @("master","msdb","model")
if ($IncludeSystemDBs) {
$result = Start-ReplaceSystemDBs $server $RestoreFromDirectory
if ($result) {
$migrateddb.Add("master","Successfully migrated")
$migrateddb.Add("msdb","Successfully migrated")
$migrateddb.Add("model","Successfully migrated")
} else {
throw "System database migration failed. Aborting."
}
}
$subdirectories = (Get-ChildItem -Directory $RestoreFromDirectory).FullName
foreach ($subdirectory in $subdirectories) {
if ((Get-ChildItem $subdirectory).Name -eq "FULL") { $dblist += $subdirectory; continue }
}
if ($dblist.count -eq 0) {
throw "No databases to restore. Did you use the correct file path? Format should be \\fileshare\share\sqlbackups\sqlservername"
}
foreach ($db in $dblist) {
$dbname = Split-Path $db -leaf
if ($systemdbs -contains $dbname) { continue }
#if ($systemdbs -notcontains $dbname) { continue }
if (!([string]::IsNullOrEmpty($IncludeDBs)) -and $IncludeDBs -notcontains $dbname) { continue }
if (!([string]::IsNullOrEmpty($ExcludeDBs)) -and $ExcludeDBs -contains $dbname) {
$skippedb.Add($dbname,"Explicitly Skipped")
Continue
}
if (!$IncludeSystemDBs -and $systemdbs -contains $dbname) { continue }
if ($server.databases[$dbname] -ne $null -and !$force -and $systemdbs -notcontains $dbname) {
Write-Warning "$dbname exists at $ServerName. Use -Force to drop and migrate."
$skippedb[$dbname] = "Database exists at $ServerName. Use -Force to drop and migrate."
continue
}
if ($server.databases[$dbname] -ne $null -and $force -and $systemdbs -notcontains $dbname) {
If ($Pscmdlet.ShouldProcess($servername,"DROP DATABASE $dbname")) {
Write-Host "$dbname already exists. -Force was specified. Dropping $dbname on $servername." -ForegroundColor Yellow
$dropresult = Drop-SQLDatabase $server $dbname
if (!$dropresult) { $skippedb[$dbname] = "Database exists and could not be dropped."; continue }
}
}
$full = Get-ChildItem "$db\FULL\*.bak" | sort LastWriteTime | select -last 1
$since = $full.LastWriteTime; $full = $full.FullName
$diff = $null; $logs = $null
if (Test-Path "$db\DIFF"){
$diff = Get-ChildItem "$db\DIFF\*.bak" | Where { $_.LastWriteTime -gt $since } | sort LastWriteTime | select -last 1
$since = $diff.LastWriteTime; $diff = $diff.fullname
}
if (Test-Path "$db\LOG"){
$logs = (Get-ChildItem "$db\LOG\*.trn" | Where { $_.LastWriteTime -gt $since })
$logs = ($logs | Sort-Object LastWriteTime).Fullname
}
$restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
$device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem $full, "FILE"
$restore.Devices.Add($device)
try { $filelist = $restore.ReadFileList($server) }
catch {
throw "File list could not be determined. This is likely due to connectivity issues with the server. Script terminating."
}
$filestructure = Get-SQLFileStructures $server $dbname $filelist $ReuseFolderstructure
if ($filestructure -eq $false) {
Write-Warning "$dbname contains FILESTREAM and filestreams are not supported by destination server. Skipping."
$skippedb[$dbname] = "Database contains FILESTREAM and filestreams are not supported by destination server."
continue
}
$backupinfo = $restore.ReadBackupHeader($server)
$backupversion = [version]("$($backupinfo.SoftwareVersionMajor).$($backupinfo.SoftwareVersionMinor).$($backupinfo.SoftwareVersionBuild)")
if ($dbname -eq "master") { $masterversion = $backupversion }
if ($backupversion -gt $server.version) { throw "Backup version cannot be newer than server version." }
Write-Host "Restoring FULL backup to $dbname to $servername" -ForegroundColor Yellow
$result = Restore-SQLDatabase $server $dbname $full "Database" $filestructure
if ($result -eq $true){
if ($diff) {
Write-Host "Restoring DIFFERENTIAL backup" -ForegroundColor Yellow
$result = Restore-SQLDatabase $server $dbname $diff "Database" $filestructure
if ($result -ne $true) { $result | fl -force ; return}
}
if ($logs) {
Write-Host "Restoring $($logs.count) LOGS" -ForegroundColor Yellow
foreach ($log in $logs) {
$result = Restore-SQLDatabase $server $dbname $log "Log" $filestructure }
}
}
if ($result -eq $false) { Write-Warning "$dbname could not be restored."; continue }
$sql = "RESTORE DATABASE [$dbname] WITH RECOVERY"
try {
$server.databases['master'].ExecuteNonQuery($sql)
$migrateddb.Add($dbname,"Successfully migrated")
Write-Host "Successfully restored $dbname." -ForegroundColor Green
} catch { Write-Warning "$dbname could not be recovered." }
try {
$server.databases.refresh()
$server.databases[$dbname].SetOwner('sa')
$server.databases[$dbname].Alter()
Write-Host "Successfully change $dbname dbowner to sa" -ForegroundColor Green
} catch { Write-Warning "Could not update dbowner to sa." }
} #end of for each database folder
$timenow = (Get-Date -uformat "%m%d%Y%H%M%S")
$csvfilename = "$($server.name.replace('\','$'))-$timenow"
$migrateddb.GetEnumerator() | Sort-Object Value; $skippedb.GetEnumerator() | Sort-Object Value
$migrateddb.GetEnumerator() | Sort-Object Value | Select Name, Value | Export-Csv -Path "$csvfilename-db.csv" -NoTypeInformation
}
END {
#Clean up
$server.ConnectionContext.Disconnect()
Write-Host "Script completed" -ForegroundColor Green
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment