Restore-SQL-Database.ps1
|
<#PSScriptInfo .VERSION 1.1 .GUID 5749b455-e6a7-44fe-a012-d0e80c97a437 .AUTHOR Andreas Kahlroth .COMPANYNAME .COPYRIGHT .TAGS .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .DESCRIPTION Restore SQL database #> param() function Restore-SQL-Database { param( [Parameter(Mandatory=$true)] [string]$SqlBackupFile, [string]$SqlServer="localhost", [Parameter(Mandatory=$true)] [string]$SqlDatabase, [string]$SqlServerLogin=$SqlDatabase, [string]$SqlServerLoginPassword="Passw0rd", [string]$SqlDatabaseUser=$SqlDatabase ) Write-Host "[INFO] Start Restore-SQL-Database" $currentLocation = Get-Location #Check if backupfile exists if(!(Test-Path -Path $SqlBackupFile)) { Write-Error "Path to $SqlBackupFile is invalid" exit } Write-Host "[INFO] Server: $SqlServer" Write-Host "[INFO] Database: $SqlDatabase" Write-Host "[INFO] Server Login: $SqlServerLogin" Write-Host "[INFO] Database User: $SqlDatabaseUser" Write-Host "[INFO] Backup: $SqlBackupFile" #Load SMO assemblies [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null #Set restore configurations $server = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -Argumentlist $SqlServer $server.ConnectionContext.StatementTimeout = 0 $smoRestore = New-Object -Type Microsoft.SqlServer.Management.Smo.Restore $backupDevice = New-Object -Type Microsoft.SqlServer.Management.Smo.BackupDeviceItem -Argumentlist $SqlBackupFile,"File" $smoRestore.Devices.Add($backupDevice) $smoRestore.NoRecovery = $false $smoRestore.ReplaceDatabase = $true $smoRestore.Action = "Database" $smoRestore.PercentCompleteNotification = 10 $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-Host "[INFO] Restoring $($_.Percent)%" } $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host "[INFO] Restore of database $SqlDatabase with $SqlBackupFile completed" } $smoRestore.add_PercentComplete($percentEventHandler) $smoRestore.add_Complete($completedEventHandler) $smoRestoreDetails = $smoRestore.ReadBackupHeader($server) #Get data/logfile names from backupfile $DBLogicalNameDT= $smoRestore.ReadFileList($server) foreach ($row in $DBLogicalNameDT) { $FileType = $row["Type"].ToUpper() if ($FileType.Equals("D")) { $dBLogicalName = $row["LogicalName"] } elseif ($FileType.Equals("L")) { $logLogicalName = $row["LogicalName"] } } #Data- and logfile paths $dbDataFile = "$($server.Settings.DefaultFile)$($SqlDatabase).mdf" $dbLogFile = "$($server.Settings.DefaultLog)$($SqlDatabase)_log.ldf" Write-Host "[INFO] Data: $dbDataFile" Write-Host "[INFO] Log: $dbLogFile" #Set restore configurations $smoRestore.Database = $SqlDatabase $dbRestoreFile = New-Object -Type Microsoft.SqlServer.Management.Smo.RelocateFile $dbRestoreLog = New-Object -Type Microsoft.SqlServer.Management.Smo.RelocateFile $dbRestoreFile.LogicalFileName = $dBLogicalName $dbRestoreFile.PhysicalFileName = $dbDataFile $dbRestoreLog.LogicalFileName = $logLogicalName $dbRestoreLog.PhysicalFileName = $dbLogFile $smoRestore.RelocateFiles.Add($dbRestoreFile) | Out-Null $smoRestore.RelocateFiles.Add($dbRestoreLog) | Out-Null #Create databas if it dont exists if(!($server.Databases[$SqlDatabase])) { $db = New-Object -Type Microsoft.SqlServer.Management.Smo.Database -Argumentlist $server,$SqlDatabase $db.Create() Write-Host "[INFO] Created database $($db.Name)" } #Drop SQL connections $killConnectionsSQL = "DECLARE @dbname sysname " + ` "SET @dbname = '$SqlDatabase' " + ` "DECLARE @spid int " + ` "SELECT @spid = min(spid) FROM master.dbo.sysprocesses " + ` "WHERE dbid = db_id(@dbname) " + ` "WHILE @spid Is Not Null " + ` "BEGIN " + ` "EXEC ('Kill ' + @spid) " + ` "SELECT @spid = min(spid) FROM master.dbo.sysprocesses " + ` "WHERE dbid = db_id(@dbname) and spid > @spid " + ` "END" $masterDb = $server.Databases["master"] $masterDb.ExecuteNonQuery($killConnectionsSQL) Write-Host "[INFO] Dropped connections to $SqlDatabase" #Restore backup to db Write-Host "[INFO] Starting restore of $SqlDatabase" $smoRestore.SqlRestore($server) $db = $server.Databases[$SqlDatabase] #Re-create service broker $serviceBrokerSql = "ALTER DATABASE [$SqlDatabase] SET NEW_BROKER with rollback immediate" $db.ExecuteNonQuery($serviceBrokerSql) Write-Host "[INFO] Re-created service broker" #Rename log and datafile logical names if($($db.FileGroups[0].Files[0].Name) -cne $SqlDatabase) { $db.FileGroups[0].Files[0].Rename($SqlDatabase) Write-Host "[INFO] Renamed datafile logical name to $SqlDatabase" } if($($db.LogFiles[0].Name) -cne "$($SqlDatabase)_log") { $db.LogFiles[0].Rename("$($SqlDatabase)_log") Write-Host "[INFO] Renamed logfile logical name to $($SqlDatabase)_log" } #Shrink logfile (.ldf) to 0KB $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple $db.Alter() $db.LogFiles[0].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly) $db.LogFiles.Refresh($true) $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full $db.Alter() Write-Host "[INFO] Logfile shrunk to $($db.LogFiles[0].Size/1KB) KB" #Create login if it dont exists if(!($server.Logins[$SqlServerLogin])) { $login = New-Object -Type Microsoft.SqlServer.Management.Smo.Login -Argumentlist $server,$SqlDatabase $login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin $login.PasswordExpirationEnabled = $false $login.PasswordPolicyEnforced = $false $login.DefaultDatabase = $SqlDatabase $login.Create($SqlServerLoginPassword) Write-Host "[INFO] Created SQLServer login $($login.Name)" } #Re-create database user if ($db.Users[$SqlDatabaseUser]) { #Remove brokerservices owned by user [System.Collections.ArrayList]$brokerServices = $db.ServiceBroker.Services foreach($service in $brokerServices) { if($service.Owner -like $SqlDatabaseUser) { $service.Drop() Write-Host "[INFO] Dropped $($service.Name)" } } #Drop user $db.Users[$SqlDatabaseUser].Drop() } $databaseUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $db,$SqlDatabaseUser $databaseUser.Login = $SqlServerLogin $databaseUser.DefaultSchema = "backend" $databaseUser.Create() #Assign database role for new user $databaserole = $db.Roles["db_owner"] $databaserole.AddMember($SqlDatabaseUser) $databaserole.Alter | Out-Null Write-Host "[INFO] Re-created database user $SqlDatabaseUser" Set-Location -Path $currentLocation Write-Host "[DONE]" -ForegroundColor White -BackgroundColor Green Write-Host "" } |