internal/Restore-DBFromFilteredArray.ps1
|
Function Restore-DBFromFilteredArray { <# .SYNOPSIS Internal function. 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. #> [CmdletBinding()] param ( [parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [string]$DbName, [parameter(Mandatory = $true, ValueFromPipeline = $true)] [object[]]$Files, [String]$RestoreLocation, [DateTime]$RestoreTime = (Get-Date).addyears(1), [switch]$NoRecovery, [switch]$ReplaceDatabase, [switch]$Scripts, [switch]$ScriptOnly, [switch]$VerifyOnly, [object]$filestructure, [System.Management.Automation.PSCredential]$SqlCredential ) Begin { $FunctionName = "Restore-DBFromFilteredArray" Write-Verbose "$FunctionName - Starting" $Results = @() $InternalFiles = @() } # -and $_.BackupStartDate -lt $RestoreTime process { foreach ($File in $Files){ $InternalFiles += $File } } End { $Server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential $ServerName = $Server.name $Server.ConnectionContext.StatementTimeout = 0 $Restore = New-Object Microsoft.SqlServer.Management.Smo.Restore $Restore.ReplaceDatabase = $ReplaceDatabase If ($null -ne $Server.Databases[$DbName]) { try { Write-Verbose "$FunctionName - Set $DbName offline to kill processes" Invoke-SQLcmd2 -ServerInstance:$SqlServer -Credential:$SqlCredential -query "Alter database $DbName set offline with rollback immediate; use $DbName" } catch { Write-Verbose "$FunctionName - No processes to kill" } } $OrderedRestores = $InternalFiles | Sort-object -Property BackupStartDate, BackupType Write-Verbose "of = $($OrderedRestores.Backupfilename)" foreach ($RestoreFile in $OrderedRestores) { if ($Restore.RelocateFiles.count -gt 0) { $Restore.RelocateFiles.Clear() } foreach ($File in $RestoreFile.Filelist) { if ($RestoreLocation -ne '' -and $FileStructure -eq $NUll) { $MoveFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile $MoveFile.LogicalFileName = $File.LogicalName $MoveFile.PhysicalFileName = $RestoreLocation + (split-path $file.PhysicalName -leaf) $null = $Restore.RelocateFiles.Add($MoveFile) } elseif ($RestoreLocation -eq '' -and $FileStructure -ne $NUll) { $FileStructure = $FileStructure.values foreach ($File in $FileStructure) { $MoveFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile $MoveFile.LogicalFileName = $File.logical $MoveFile.PhysicalFileName = $File.physical $null = $Restore.RelocateFiles.Add($MoveFile) } } elseif ($RestoreLocation -ne '' -and $FileStructure -ne $NUll) { Write-Error "Conflicting options only one of FileStructure or RestoreLocation allowed" } } try { Write-Verbose "$FunctionName - Beginning Restore" $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 = $ReplaceDatabase $Restore.ToPointInTime = $RestoreTime if ($DbName -ne '') { $Restore.Database = $DbName } else { $Restore.Database = $RestoreRile.DatabaseName } $Action = switch ($RestoreFile.BackupType) { '1' {'Database'} '2' {'Log'} '5' {'Database'} Default {} } Write-Verbose "$FunctionName restore action = $Action" $restore.Action = $Action if ($RestoreFile -eq $OrderedRestores[-1] -and $NoRecovery -ne $true) { #Do recovery on last file Write-Verbose "$FunctionName - Doing Recovery on last file" $Restore.NoRecovery = $false } else { $Restore.NoRecovery = $true } $Device = New-Object -TypeName Microsoft.SqlServer.Management.Smo.BackupDeviceItem $Device.Name = $RestoreFile.BackupPath $Device.devicetype = "File" $Restore.Devices.Add($device) Write-Verbose "$FunctionName - Performaing restore action" if ($ScriptOnly) { $restore.Script($server) } elseif ($VerifyOnly) { Write-Progress -id 1 -activity "Verifying $dbname backup file on $servername" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0)) $Verify = $restore.sqlverify($server) Write-Progress -id 1 -activity "Verifying $dbname backup file on $servername" -status "Complete" -Completed if ($verify -eq $true) { return "Verify successful" } else { return "Verify failed" } } else { Write-Progress -id 1 -activity "Restoring $DbName to ServerName" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0)) $Restore.sqlrestore($Server) if ($scripts) { $restore.Script($Server) } Write-Progress -id 1 -activity "Restoring $DbName to $ServerName" -status "Complete" -Completed #return "Success" } $null = $Restore.Devices.Remove($Device) Remove-Variable device } catch { Write-Warning $_.Exception.InnerException } } if ($NoRecovery -eq $false -and $ScriptOnly -eq $false) { Invoke-SQLcmd2 -ServerInstance:$SqlServer -Credential:$SqlCredential -query "Restore database $DbName with recovery" } #$server.ConnectionContext.Disconnect() } } |