Private/SqlDatabasesFunctions.ps1
|
function ImportSqlServerAssemblies { [CmdletBinding()] Param ( [string]$LogFile ) WriteLog -Path $LogFile -Message "Loading SQL Server assemblies" [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null WriteLog -Path $LogFile -Message "Microsoft.SqlServer.SMO loaded" [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null WriteLog -Path $LogFile -Message "Microsoft.SqlServer.SmoExtended loaded" } function Write-ProgressMessage { if ( $_.Percent -ne 100) { Write-Host $_.Percent "%" } else { Write-Host $_.Percent "% Please, wait..." } } function BackupSqlDatabase { [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$DatabaseName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ if (Test-Path $_) { $true } else { Throw "'$_' path is not valid. Please, provide a valid value" }})] [string]$BackupDirectory, [string]$LogFile ) $sqlServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($SqlServerName) $timeStamp = Get-Date -format yyyy-MM-dd-HHmmss $targetPath = Join-Path -Path $backupDirectory -ChildPath ($DatabaseName + "_" + $timeStamp + ".bak") $smoBackup = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Backup" $smoBackup.Action = "Database" $smoBackup.BackupSetDescription = "Full Backup of " + $DatabaseName $smoBackup.BackupSetName = $DatabaseName + " Backup" $smoBackup.Database = $DatabaseName $smoBackup.MediaDescription = "Disk" $smoBackup.Devices.AddDevice($targetPath, "File") $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-ProgressMessage } $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host "Database '$DatabaseName' backed up successfully" } $smoBackup.add_PercentComplete($percentEventHandler) $smoBackup.add_Complete($completedEventHandler) $smoBackup.SqlBackup($sqlServer) WriteLog -Path $LogFile -Message "OUTPUT: Database Backup finished for '$DatabaseName' ($SqlServerName)" WriteLog -Path $LogFile -Message "OUTPUT: Backup location '$targetPath'" } function BackupSqlDatabases { [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$DatabaseNames, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupDirectory, [string]$LogFile ) ConfirmSqlServerConnection -SqlServerName $SqlServerName -LogFile $LogFile WriteLog -Path $LogFile -Message "Checking the most recent backups" $sqlDatabasesBackups = Join-Path -Path $BackupDirectory -ChildPath "SqlDatabasesBackups" $backupDir = GetLastCreatedDir -Directory $sqlDatabasesBackups if([String]::IsNullOrWhiteSpace($backupDir)) { WriteLog -Path $LogFile -Message "No backups available" WriteLog -Path $LogFile -Message "Creating unique backup directory" $backupDir = NewUniqueDir -Directory $BackupDirectory -SubDirectory "SqlDatabasesBackups" WriteLog -Path $LogFile -Message "Unique backup directory created '$backupDir'" } else { WriteLog -Path $LogFile -Message "Most recent backups found in '$backupDir'" #if Logs folder is available, that means that, there were problems with the most recent backups $logsDirectory = GetLogDir -Directory $backupDir if ($logsDirectory) { WriteLog -Path $LogFile -Message "WARNING: The most recent backups were not finished successfully. Sitecore.Deployment will try to fix them" } else { WriteLog -Path $LogFile -Message "The most recent backups were finished successfully. Sitecore.Deployment will create new backups for you" WriteLog -Path $LogFile -Message "Creating unique backup directory" $backupDir = NewUniqueDir -Directory $BackupDirectory -SubDirectory "SqlDatabasesBackups" WriteLog -Path $LogFile -Message "Unique backup directory created '$backupDir'" } } WriteLog -Path $LogFile -Message "'$backupDir' directory will be used for storing the current backups" $dbNamesArray = $DatabaseNames.Split(',').Trim() if ($dbNamesArray.Count -gt 0) { ImportSqlServerAssemblies -LogFile $LogFile $lastBackups = Get-ChildItem -Path $backupDir -Filter *.bak $failedDatabases = @() foreach ($dbName in $dbNamesArray) { try { foreach($backup in $lastBackups) { if ($backup.Name.StartsWith($dbName)) { RemoveItem -Path $backup.FullName } } WriteLog -Path $LogFile -Message "OUTPUT: Database Backup started for '$dbName' ($SqlServerName). Please, wait" BackupSqlDatabase -SqlServerName $SqlServerName -DatabaseName $dbName -BackupDirectory $backupDir -LogFile $LogFile } catch { WriteLog -Path $LogFile -Message "ERROR: Backup failed for '$dbName' ($SqlServerName)" -RedirectStream $true $exMessage = $_.Exception | Format-List -Force | Out-String WriteLog -Path $LogFile -Message "VERBOSE_ERROR: $exMessage" if ($dbNamesArray.IndexOf($dbName) -ne $dbNamesArray.Count - 1) { WriteLog -Path $LogFile -Message "WARNING: Continue with next database" -RedirectStream $true } $failedDatabases += $dbName } } if ($failedDatabases.Count -gt 0) { NewLogDir -Directory $backupDir Throw "Back up failed for '$failedDatabases'. Please, try to fix this issue before proceed with next deployment steps" } else { $logsDirectory = GetLogDir -Directory $backupDir if ($logsDirectory) { RemoveItem -Path $logsDirectory } } } } function BackupSqlDatabasesRemotely { [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$DatabaseNames, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupDirectory, $VerboseRemote ) try { $VerbosePreference = $VerboseRemote Write-Verbose "VERBOSE: Importing 'Sitecore.Deployment'" *>&1 Import-Module -Name Sitecore.Deployment -ErrorAction Stop Write-Verbose "VERBOSE: 'Sitecore.Deployment' imported" *>&1 Backup-SqlDatabases -SqlServerName $SqlServerName -DatabaseNames $DatabaseNames -BackupDirectory $BackupDirectory } catch { Write-Warning "ERROR: 'Sitecore.Deployment' is not installed" *>&1 $message = $_.Exception | Format-List -Force | Out-String Write-Verbose "VERBOSE_ERROR: $message" *>&1 } } function RestoreSqlDatabase { [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$DatabaseName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ if (Test-Path $_) { $true } else { Throw "'$_' path is not valid. Please, provide a valid value" }})] [string]$BackupPath, [string]$LogFile ) $sqlServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($SqlServerName) $smoRestore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore $smoRestore.Database = $DatabaseName $smoRestore.ReplaceDatabase = $true $smoRestore.NoRecovery = $false $smoRestore.Action = "Database" $smoRestore.Devices.AddDevice($BackupPath, "File") $smoRestoreDataFile = New-Object -TypeName Microsoft.SqlServer.Management.Smo.RelocateFile $defaultData = $sqlServer.DefaultFile if (($defaultData -eq $null) -or ($defaultData -eq $empty)) { $defaultData = $sqlServer.MasterDBPath } $smoRestoreDataFile.PhysicalFileName = Join-Path -Path $defaultData -ChildPath ($DatabaseName + "_Data.mdf") $smoRestoreLogFile = New-Object -TypeName Microsoft.SqlServer.Management.Smo.RelocateFile $defaultLog = $sqlServer.DefaultLog if (($defaultLog -eq $null) -or ($defaultLog -eq $empty)) { $defaultLog = $sqlServer.MasterDBLogPath } $smoRestoreLogFile.PhysicalFileName = Join-Path -Path $defaultLog -ChildPath ($DatabaseName + "_Log.ldf") $dbFileList = $smoRestore.ReadFileList($sqlServer) $smoRestoreDataFile.LogicalFileName = $dbFileList.Select("Type = 'D'")[0].LogicalName $smoRestoreLogFile.LogicalFileName = $dbFileList.Select("Type = 'L'")[0].LogicalName $smoRestore.RelocateFiles.Add($smoRestoreDataFile) | Out-Null $smoRestore.RelocateFiles.Add($smoRestoreLogFile) | Out-Null if ($sqlServer.Databases[$DatabaseName] -ne $null) { $sqlServer.KillAllProcesses($DatabaseName) } $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-ProgressMessage } $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host "Database '$DatabaseName' restored successfully" } $smoRestore.add_PercentComplete($percentEventHandler) $smoRestore.add_Complete($completedEventHandler) $smoRestore.SqlRestore($sqlServer) WriteLog -Path $LogFile -Message "OUTPUT: Database Restore finished for '$DatabaseName' ($SqlServerName)" } function RestoreSqlDatabases { [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$DatabaseNames, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupDirectory, [string]$LogFile ) ConfirmSqlServerConnection -SqlServerName $SqlServerName -LogFile $LogFile WriteLog -Path $LogFile -Message "Getting the most recent backups" $sqlDatabasesBackups = Join-Path -Path $BackupDirectory -ChildPath "SqlDatabasesBackups" $lastBackupsFolder = GetLastCreatedDir -Directory $sqlDatabasesBackups if([String]::IsNullOrWhiteSpace($lastBackupsFolder)) { Throw "No backups found" } WriteLog -Path $LogFile -Message "Most recent backups found in '$lastBackupsFolder'" $dbNamesArray = $DatabaseNames.Split(',').Trim() if ($dbNamesArray.Count -gt 0) { ImportSqlServerAssemblies -LogFile $LogFile $missingBackups = @() $lastBackups = Get-ChildItem -Path $lastBackupsFolder -Filter *.bak foreach ($dbName in $dbNamesArray) { foreach($backup in $lastBackups) { if ($backup.Name.StartsWith($dbName)) { continue } $missingBackups += $dbName } } if ($missingBackups.Count -gt 0) { Throw "Restore mismatch. No backups were restored. Backups of the following databases are missing '$missingBackups'. Please, make the restore of all databases manually. Last available Sitecore.Deployment backups are located in '$lastBackupsFolder'" } $failedDatabases = @() foreach($backup in $lastBackups) { try { WriteLog -Path $LogFile -Message "OUTPUT: Database Restore started for '$dbName' ($SqlServerName). Please, wait" RestoreSqlDatabase -SqlServerName $SqlServerName -DatabaseName $dbName -BackupPath $backup.FullName -LogFile $LogFile } catch { WriteLog -Path $LogFile -Message "ERROR: Restore failed for '$dbName' ($SqlServerName)" -RedirectStream $true $exMessage = $_.Exception | Format-List -Force | Out-String WriteLog -Path $LogFile -Message "VERBOSE_ERROR: $exMessage" if ($dbNamesArray.IndexOf($dbName) -ne $dbNamesArray.Count - 1) { WriteLog -Path $LogFile -Message "WARNING: Continue with next database" -RedirectStream $true } $failedDatabases += $dbName } } if ($failedDatabases.Count -gt 0) { Throw "Restore failed for '$failedDatabases'. Please, check the backups or try to restore them manually" } } } function RestoreSqlDatabasesRemotely { [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SqlServerName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$DatabaseNames, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$BackupDirectory, $VerboseRemote ) try { $VerbosePreference = $VerboseRemote Write-Verbose "VERBOSE: Importing 'Sitecore.Deployment'" *>&1 Import-Module -Name Sitecore.Deployment -ErrorAction Stop Write-Verbose "VERBOSE: 'Sitecore.Deployment' imported" *>&1 Restore-SqlDatabases -SqlServerName $sqlServerName -DatabaseNames $DatabaseNames -BackupDirectory $BackupDirectory } catch { Write-Warning "ERROR: 'Sitecore.Deployment' is not installed" *>&1 $message = $_.Exception | Format-List -Force | Out-String Write-Verbose "VERBOSE_ERROR: $message" *>&1 } } |