Last active
August 29, 2015 14:06
-
-
Save potatoqualitee/9bc2dc00e6d45b184ff0 to your computer and use it in GitHub Desktop.
Restore-HallengrenBackups.ps1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .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