Functions/Set-SdtDbaLogWalk.ps1
|
Function Set-SdtDbaLogWalk { <# .SYNOPSIS Create Log Walk job in YourOrg environment. This scripts out Database restore code along with job creation. .DESCRIPTION This function accepts Source Server/Database & Destination Server/Database, and creates Log Walk Job. .PARAMETER SourceServer Name of the source server .PARAMETER SourceDbName Name of the Source database .PARAMETER DestinationServer Name of the Destination server .PARAMETER DestinationDbName Name of the Source database .PARAMETER ScriptOutPermissions With this switch, script out existing database permissions .PARAMETER RecoverDatabase With this switch, script out database RESTORE to perform refresh and RECOVER databases by brining them in READ_WRITE mode .PARAMETER GenerateRESTOREScriptOnly With this switch, script out database RESTORE with update script for [DBALastFileApplied] ONLY. Do not check Baselining, SelfServiceModules, ServiceBroker setup, or LogWalk existance. .PARAMETER SizeThreshold_GB Size in GigaBytes. Default 30. For backup files larger than SizeThreshold_GB, ROBOCOPY command will be generated to copy them on Destination Server from Source Server. .PARAMETER ProcessAllLogBackups With this switch, we process all available Log backup files. By default, we process, last Full, diff & 1st Log backup. .EXAMPLE C:\PS> Setup-DbaLogWalk -SourceServer DbServer01 -SourceDbName MyDb1 -DestinationServer DbServer02 Generates RESTORE tsql code for [MyDb1] database, and creates Log Walk job named [DBA Log Walk - Restore MyDb1 as MyDb1] .EXAMPLE C:\PS> Setup-DbaLogWalk -SourceServer DbServer01 -SourceDbName MyDb1 -DestinationServer DbServer02 -DestinationDbName MyDb2 Generates RESTORE tsql code for [MyDb2] database, and creates Log Walk job named [DBA Log Walk - Restore MyDb1 as MyDb2] .EXAMPLE C:\PS> Setup-DbaLogWalk -SourceServer DbServer01 -SourceDbName MyDb1 -DestinationServer DbServer02 -DestinationDbName MyDb2 -ScriptOutPermissions Script out existing permissions of DestinationDbName, and then Generate RESTORE tsql code for [MyDb2] database, and creates Log Walk job named [DBA Log Walk - Restore MyDb1 as MyDb2] .EXAMPLE C:\PS> Setup-DbaLogWalk -SourceServer DbServer01 -SourceDbName MyDb1 -DestinationServer DbServer02 -DestinationDbName MyDb2 -ScriptOutPermissions -RecoverDatabase Script out existing permissions of DestinationDbName and Generate RESTORE tsql code for [MyDb2] database. The code generated will bring database in READ_WRITE mode(RECOVERY). .EXAMPLE C:\PS> Setup-DbaLogWalk -SourceServer DbServer01 -SourceDbName MyDb1 -DestinationServer DbServer02 -DestinationDbName MyDb2 -GenerateRESTOREScriptOnly Generate RESTORE tsql code for [MyDb2] database with NORECOVERY option .EXAMPLE C:\PS> Setup-DbaLogWalk -SourceServer DbServer01 -SourceDbName MyDb1 -DestinationServer DbServer02 -DestinationDbName MyDb2 -GenerateRESTOREScriptOnly -ProcessAllLogBackups Generate RESTORE tsql code for [MyDb2] database with NORECOVERY option with Last Full + Diff + All available log backups. .LINK https://github.com/imajaydwivedi/SQLDBATools .NOTES Author: Ajay Dwivedi EMail: ajay.dwivedi2007@gmail.com Date: June 14, 2019 Documentation: https://github.com/imajaydwivedi/SQLDBATools #> [CmdletBinding()] Param ( [Parameter( Mandatory=$true )] [ValidateNotNullOrEmpty()] [Alias('SQLInstance_Source')] [String]$SourceServer, [Parameter( Mandatory=$true )] [ValidateNotNullOrEmpty()] [Alias('SourceDb')] [String]$SourceDbName, [Parameter( Mandatory=$true )] [ValidateNotNullOrEmpty()] [Alias('SQLInstance_Destination')] [String]$DestinationServer, [Parameter( Mandatory=$false )] [Alias('DestinationDb')] [String]$DestinationDbName, [Parameter( Mandatory=$false )] [Alias('ScriptPermissions')] [Switch]$ScriptOutPermissions, [Parameter( Mandatory=$false )] [Alias('SetRecovery','EnableDatabaseRecovery','EnableRecovery')] [Switch]$RecoverDatabase, [Parameter( Mandatory=$false )] [Alias('GenerateDatabaseRESTOREScriptOnly')] [Switch]$GenerateRESTOREScriptOnly, [Parameter( Mandatory=$false )] [Alias('BackupFileSizeThreshold4RemoteCopy_GB')] [Double]$SizeThreshold_GB = 30, [Parameter( Mandatory=$false )] [Alias('ProcessAllAvailableLogBackups')] [Switch]$ProcessAllLogBackups ) <# Script to Setup Log Walk jobs Prerequisites: 1) Source Server should have databases in 'FULL' recovery model 2) If Destination databases are present, then scriptout permissions, then REPLACE, keeping same database file locations 3) Check if usual DBA procedures are present a) master..sp_Kill b) master..sp_HealthCheck c) master..sp_WhoIsActive d) DBA..usp_WhoIsActive_Blocking 4) Setup Baselining with [sp_WhoIsActive] 5) Setup ServiceBroker for new LogWalkAlert 6) Restore Databases from Source to Destination with NORECOVERY 7) Create DBA..usp_DBADropDbSnaphot 8) Create DBA..usp_DBAApplyTLogs #> if($RecoverDatabase) {$GenerateRESTOREScriptOnly = $true} # variable to track Fatal Error $abort = $false; $runningCode = $null; $NewLine_Single = "`r`n"; $NewLine_Double = "`r`n`r`n"; $destinationDbExists = 0; # Create File Path $DbaLogWalk_ScriptPath = "c:\temp\YourOrgDbaLogWalk\$($SourceServer)___2___$($DestinationServer)\$((Get-Date -Format 'yyyy-MMM-dd'))\"; Write-Verbose "Checking if script path '$DbaLogWalk_ScriptPath' exists"; if([System.IO.Directory]::Exists($DbaLogWalk_ScriptPath) -eq $false) { New-Item -ItemType "directory" -Path $DbaLogWalk_ScriptPath; } if([string]::IsNullOrEmpty($DestinationDbName) -eq $true) {$DestinationDbName = $SourceDbName}; $ErrorMessage = $null; # Create Source Server Token try { $runningCode = "Connect-DbaInstance -SqlInstance $SourceServer"; $friendlyErrorMessage = "Kindly verify if server '$SourceServer' is up and accessible"; $sourceSrvToken = Connect-DbaInstance -SqlInstance $SourceServer; } catch { $ErrorMessage = $_.Exception.Message; $FailedItem = $_.Exception.ItemName; $abort = $true; } if ($abort) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; $returnMessage = "RunningCode => $runningCode" + $NewLine_Single + "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } # Create Destination Server Token try { $runningCode = "Connect-DbaInstance -SqlInstance $DestinationServer"; $friendlyErrorMessage = "Kindly verify if server '$DestinationServer' is up and accessible"; $destinationSrvToken = Connect-DbaInstance -SqlInstance $DestinationServer; } catch { $ErrorMessage = $_.Exception.Message; $FailedItem = $_.Exception.ItemName; $abort = $true; } if ($abort) { if ([string]::IsNullOrEmpty($ErrorMessage) -eq $false) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; } else {$returnMessage = '';} if ([string]::IsNullOrEmpty($runningCode) -eq $false) { $rcMessage = "RunningCode => $runningCode" + $NewLine_Single; } else {$rcMessage = '';} if ([string]::IsNullOrEmpty($friendlyErrorMessage) -eq $false) { $feMessage = "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double; } else {$feMessage = '';} $returnMessage = $rcMessage + $feMessage + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } # Check 01 => Source database Recovery Model if($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-Host "Verifying the recovery model for database '$SourceDbName'"; } $runningCode = "select d.recovery_model_desc from sys.databases as d where d.name = '$SourceDbName'"; $friendlyErrorMessage = "Kindly make sure recovery model for database '$SourceDbName' is set to FULL."; $rModel = Invoke-DbaQuery -SqlInstance $sourceSrvToken -Query "select d.recovery_model_desc from sys.databases as d where d.name = '$SourceDbName'" | Select-Object -ExpandProperty recovery_model_desc; if([String]::IsNullOrEmpty($rModel) -or $rModel -ne 'Full') { $abort = $true; } if ($abort) { if ([string]::IsNullOrEmpty($ErrorMessage) -eq $false) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; } else {$returnMessage = '';} if ([string]::IsNullOrEmpty($runningCode) -eq $false) { $rcMessage = "RunningCode => $runningCode" + $NewLine_Single; } else {$rcMessage = '';} if ([string]::IsNullOrEmpty($friendlyErrorMessage) -eq $false) { $feMessage = "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double; } else {$feMessage = '';} $returnMessage = $rcMessage + $feMessage + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } # Check 02 => Verify existance of Destination db, and Scriptout permissions $tsqlQuery = @" IF EXISTS (SELECT * FROM sys.databases as d where d.name = '$DestinationDbName') SELECT 1 AS [Exists], case when d.state_desc = 'ONLINE' then 1 else 0 end as IsOnline FROM sys.databases as d where d.name = '$DestinationDbName'; ELSE SELECT 0 AS [Exists], 0 as IsOnline; "@; $ExistsOnline = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery; $destinationDbExists = $ExistsOnline.Exists; $destinationDbOnline = $ExistsOnline.IsOnline; if([string]::IsNullOrEmpty($destinationDbExists) -eq $false -and $destinationDbOnline -eq 1 -and $ScriptOutPermissions -eq $true) { Write-Host "Scripting out permissions for database [$SourceDbName] in file '$($DbaLogWalk_ScriptPath)01) Destination-Permissions-ScriptOut.sql'" -ForegroundColor Yellow; Export-SqlUser -SqlInstance $destinationSrvToken -Database $DestinationDbName -Path "$($DbaLogWalk_ScriptPath)01) Destination-Permissions-ScriptOut.sql" -NoClobber -Append; } # Check 03 => Verify existance of sp_Kill, sp_WhoIsActive, usp_WhoIsActive_Blocking if($GenerateRESTOREScriptOnly) { $tsqlQuery = @" IF OBJECT_ID('master..sp_WhoIsActive') IS NULL OR OBJECT_ID('master..sp_Kill') IS NULL OR OBJECT_ID('DBA..usp_WhoIsActive_Blocking') IS NULL SELECT 0 as [Exists] ELSE SELECT 1 as [Exists] "@; $runningCode = $null; $friendlyErrorMessage = "Kindly make sure self help stored procedures like sp_HealthCheck/sp_WhoIsActive/sp_Kill/usp_WhoIsActive_Blocking are created using 'Setup-SelfServiceModules' cmdlet."; $exists = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery | Select-Object -ExpandProperty Exists; if([string]::IsNullOrEmpty($exists) -eq $true -or $exists -eq 0) { $abort = $true; } if ($abort) { if ([string]::IsNullOrEmpty($ErrorMessage) -eq $false) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; } else {$returnMessage = '';} if ([string]::IsNullOrEmpty($runningCode) -eq $false) { $rcMessage = "RunningCode => $runningCode" + $NewLine_Single; } else {$rcMessage = '';} if ([string]::IsNullOrEmpty($friendlyErrorMessage) -eq $false) { $feMessage = "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double; } else {$feMessage = '';} $returnMessage = $rcMessage + $feMessage + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } # Check 04 => Setup Baselining with [sp_WhoIsActive] $tsqlQuery = @" IF NOT EXISTS (select * from msdb.dbo.sysjobs as j where j.name = 'DBA - Log_With_sp_WhoIsActive - Cleanup') SELECT 0 as [Exists] ELSE SELECT 1 as [Exists] "@; $runningCode = $null; $friendlyErrorMessage = "Kindly make sure server baselining is done with [sp_WhoIsActive] using 'Setup-BaselineWithWhoIsActive' cmdlet."; $exists = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery | Select-Object -ExpandProperty Exists; if([string]::IsNullOrEmpty($exists) -eq $true -or $exists -eq 0) { $abort = $true; } if ($abort) { if ([string]::IsNullOrEmpty($ErrorMessage) -eq $false) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; } else {$returnMessage = '';} if ([string]::IsNullOrEmpty($runningCode) -eq $false) { $rcMessage = "RunningCode => $runningCode" + $NewLine_Single; } else {$rcMessage = '';} if ([string]::IsNullOrEmpty($friendlyErrorMessage) -eq $false) { $feMessage = "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double; } else {$feMessage = '';} $returnMessage = $rcMessage + $feMessage + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } # Check 05 => Setup ServiceBroker for new LogWalkAlert $tsqlQuery = @" IF NOT EXISTS (select * from msdb.dbo.sysjobs as j where j.name = 'DBA - Process - WhoIsActiveQueue') SELECT 0 as [Exists] ELSE SELECT 1 as [Exists] "@; $runningCode = $null; $friendlyErrorMessage = "Kindly make sure pre-requisites of New Log Walk Alert with ServiceBroker is established using 'Setup-ServiceBroker_4_LogWalkAlert' cmdlet."; $exists = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery | Select-Object -ExpandProperty Exists; if([string]::IsNullOrEmpty($exists) -eq $true -or $exists -eq 0) { $abort = $true; } if ($abort) { if ([string]::IsNullOrEmpty($ErrorMessage) -eq $false) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; } else {$returnMessage = '';} if ([string]::IsNullOrEmpty($runningCode) -eq $false) { $rcMessage = "RunningCode => $runningCode" + $NewLine_Single; } else {$rcMessage = '';} if ([string]::IsNullOrEmpty($friendlyErrorMessage) -eq $false) { $feMessage = "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double; } else {$feMessage = '';} $returnMessage = $rcMessage + $feMessage + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } # Check 06 => Create required procs [usp_DBAApplyTLogs],[usp_DBADropDbSnaphot],[usp_DBAKillInactiveUser] $tsqlQuery = @" IF OBJECT_ID('DBA..usp_DBAApplyTLogs') IS NULL OR OBJECT_ID('DBA..usp_DBADropDbSnaphot') IS NULL OR OBJECT_ID('DBA..usp_DBAKillInactiveUser') IS NULL SELECT 0 as [Exists]; ELSE SELECT 1 as [Exists]; "@; $runningCode = $null; $friendlyErrorMessage = "Kindly make sure required procedures [usp_DBAApplyTLogs],[usp_DBADropDbSnaphot],[usp_DBAKillInactiveUser] are created in DBA database."; $exists = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery | Select-Object -ExpandProperty Exists; if([string]::IsNullOrEmpty($exists) -eq $true -or $exists -eq 0) { $LogWalk_usp_DBAKillInactiveUser_File = "$((Get-ItemProperty $PSScriptRoot).Parent.FullName)\SQLQueries\LogWalk_usp_DBAKillInactiveUser.sql"; $LogWalk_usp_DBADropDbSnaphot_File = "$((Get-ItemProperty $PSScriptRoot).Parent.FullName)\SQLQueries\LogWalk_usp_DBADropDbSnaphot.sql"; $LogWalk_usp_DBAApplyTLogs_File = "$((Get-ItemProperty $PSScriptRoot).Parent.FullName)\SQLQueries\LogWalk_usp_DBAApplyTLogs.sql"; Invoke-DbaQuery -SqlInstance $destinationSrvToken -File $LogWalk_usp_DBAKillInactiveUser_File -ErrorAction SilentlyContinue; Invoke-DbaQuery -SqlInstance $destinationSrvToken -File $LogWalk_usp_DBADropDbSnaphot_File -ErrorAction SilentlyContinue; Invoke-DbaQuery -SqlInstance $destinationSrvToken -File $LogWalk_usp_DBAApplyTLogs_File -ErrorAction SilentlyContinue; Write-Host "Log Walk job dependent procedures [usp_DBAApplyTLogs],[usp_DBADropDbSnaphot] & [usp_DBAKillInactiveUser] are created in DBA database." -ForegroundColor Yellow; } # Check 07 => Create table DBA..DBALastFileApplied $tsqlQuery = @" IF OBJECT_ID('DBA..DBALastFileApplied') IS NOT NULL SELECT 1 AS [Exists]; ELSE SELECT 0 AS [Exists]; "@; $runningCode = $null; $friendlyErrorMessage = "Kindly make sure table [DBA]..[DBALastFileApplied] exists."; $exists = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery | Select-Object -ExpandProperty Exists; if([string]::IsNullOrEmpty($exists) -eq $true -or $exists -eq 0) { $LogWalk_tbl_DBALastFileApplied_File = "$((Get-ItemProperty $PSScriptRoot).Parent.FullName)\SQLQueries\LogWalk_tbl_DBALastFileApplied.sql"; Invoke-DbaQuery -SqlInstance $destinationSrvToken -File $LogWalk_tbl_DBALastFileApplied_File; Write-Host "Table table [DBA]..[DBALastFileApplied] created successfully." -ForegroundColor Yellow; } } # Step 08 => Script out RESTORE database tsql code $runningCode = "Get-DbaBackupHistory -SqlInstance $SourceServer -Database $SourceDbName -Last -DeviceType Disk"; $friendlyErrorMessage = "Kindly make Full+Log backup jobs are configured on source [$SourceServer] for database [$SourceDbName]."; $latestBackups = Get-DbaBackupHistory -SqlInstance $sourceSrvToken -Database $SourceDbName -Last -DeviceType Disk; if([string]::IsNullOrEmpty($latestBackups)) { $abort = $true; } if ($abort) { if ([string]::IsNullOrEmpty($ErrorMessage) -eq $false) { $returnMessage = if([string]::IsNullOrEmpty($FailedItem)){$ErrorMessage}else{"$FailedItem => $ErrorMessage"}; } else {$returnMessage = '';} if ([string]::IsNullOrEmpty($runningCode) -eq $false) { $rcMessage = "RunningCode => $runningCode" + $NewLine_Single; } else {$rcMessage = '';} if ([string]::IsNullOrEmpty($friendlyErrorMessage) -eq $false) { $feMessage = "FriendlyErrorMessage => $friendlyErrorMessage" + $NewLine_Double; } else {$feMessage = '';} $returnMessage = $rcMessage + $feMessage + $returnMessage; Write-Host "$returnMessage" -ForegroundColor Red; return; } $existingFiles = $null; if($destinationDbExists -eq 1) { $tsqExistingFiles = @" select CASE WHEN mf.type_desc = 'ROWS' THEN 'D' ELSE 'L' END as FileType, mf.name as LogicalName, mf.physical_name as PhysicalName from sys.master_files as mf where mf.database_id = db_id('$DestinationDbName') "@; $existingFiles = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqExistingFiles; } $commentHeader = if ($SourceDbName -eq $DestinationDbName) {"[$SourceServer]"} else {"[$SourceDbName] => [$DestinationDbName]"}; $restoreText_Full = ''; $restoreText_Differential = ''; $restoreText_Log = ''; $restoreText_RoboCopy = ''; $restoreText_Final = ""; $isFirstLogBackupApplied = $false; $LastFileApplied = $null; $bkpFileSize_Larger = $false; $logBackupFiles_Counter = 0; $isLastLogBackupFile = $false; $logBackupFiles_Count = @($latestBackups | Where-Object {$_.Type -eq 'LOG'}).Count; if($ProcessAllLogBackups -eq $false) { if($logBackupFiles_Count -ge 2) { $logBackupFiles_Count = 1; } } foreach($bkp in $latestBackups) { if($bkp.Type -eq 'LOG'){$logBackupFiles_Counter += 1} if($logBackupFiles_Counter -gt $logBackupFiles_Count) { break; } if($logBackupFiles_Counter -eq $logBackupFiles_Count){$isLastLogBackupFile = $true} $bkpFileSize_GB = $null; $bkpFileSize_GB = $bkp.CompressedBackupSize.Gigabyte; if([string]::IsNullOrEmpty($bkpFileSize_GB)){$bkpFileSize_GB = $bkp.TotalSize.Gigabyte} if($bkpFileSize_GB -gt $SizeThreshold_GB -and $bkpFileSize_Larger -eq $false){$bkpFileSize_Larger = $true} if($bkp.Path -match ":") { $backupPath = "\\$SourceServer\" + ($($bkp.Path) -replace ':\\','$\'); }else { $backupPath = $bkp.Path; } $backupFile_BaseName = if($($bkp.Path) -match ".+\\(?'fileName'\w+\.[a-zA-Z]{1,3})") {$Matches['fileName']} else {$null}; if($bkpFileSize_GB -gt $SizeThreshold_GB) { $backupFile_NetworkPathDirectory = (Get-ItemProperty $backupPath | Select-Object -ExpandProperty DirectoryName)+'\'; $src = $backupFile_NetworkPathDirectory; $dst = 'Local\Path\On\Destination\'; $restoreText_RoboCopy += "Invoke-Command -ComputerName $DestinationServer -ScriptBlock { robocopy '$src' '$dst' $backupFile_BaseName /it}"; } if($bkp.Type -eq 'Full') #'Log','Differential' { $restoreText_Full = @" -- $commentHeader - Full restore of $($bkp.TotalSize) total size from $($bkp.CompressedBackupSize) backup of '$($bkp.Start)' RESTORE DATABASE [$DestinationDbName] FROM DISK = N'$(if($bkpFileSize_GB -gt $SizeThreshold_GB){$dst+$backupFile_BaseName}else{$backupPath})' WITH NORECOVERY, STATS = 5 $(if($destinationDbExists -eq 1){',REPLACE'}) "@; # Add WITH MOVE option in Full Restore $fileList = if($destinationDbExists -eq 1){$existingFiles}else{$bkp.FileList}; foreach ($file in $fileList) { $restoreText_Full += @" `r`n ,MOVE N'$($file.LogicalName)' TO N'$($file.PhysicalName)' "@; } $restoreText_Full += @" GO "@; } if($bkp.Type -eq 'Differential') { $restoreText_Differential = @" -- $commentHeader - Differential restore of $($bkp.TotalSize) total size from $($bkp.CompressedBackupSize) backup of '$($bkp.Start)' RESTORE DATABASE [$DestinationDbName] FROM DISK = N'$(if($bkpFileSize_GB -gt $SizeThreshold_GB){$dst}else{$backupPath})' WITH NORECOVERY, STATS = 5 "@; $restoreText_Differential += @" GO "@; } if($bkp.Type -eq 'Log') { $restoreText_Log += @" -- $commentHeader - Log restore of $($bkp.TotalSize) total size from backup of '$($bkp.Start)' RESTORE LOG [$DestinationDbName] FROM DISK = N'$backupPath' WITH STATS = 5, $(if($isLastLogBackupFile -and $RecoverDatabase){'RECOVERY'}else{'NORECOVERY'}) "@; $restoreText_Log += @" GO "@; if($isLastLogBackupFile) { $LastFileApplied = $backupFile_BaseName; } } } $tsqlUpdateLastFileApplied = @" USE DBA; GO IF EXISTS (SELECT * FROM [dbo].[DBALastFileApplied] WHERE [dbname] = '$DestinationDbName') UPDATE [dbo].[DBALastFileApplied] SET [LastFileApplied] = '$LastFileApplied', [PointerResetFile] = '$LastFileApplied' WHERE [dbname] = '$DestinationDbName'; ELSE INSERT [dbo].[DBALastFileApplied] ([dbname],[LastFileApplied],[PointerResetFile]) SELECT '$DestinationDbName','$LastFileApplied','$LastFileApplied'; GO "@; $tsqlKillDatabaseSessions = ''; if($destinationDbOnline -eq 1) { $tsqlKillDatabaseSessions = @" USE master GO EXEC sp_Kill @p_DbName = '$DestinationDbName', @p_Force = 1; GO "@; } $restoreText_Final = $(if($bkpFileSize_Larger){$restoreText_RoboCopy}else{''}) + $tsqlKillDatabaseSessions + $restoreText_Full + $restoreText_Differential + $restoreText_Log + $(if($RecoverDatabase){''}else{$tsqlUpdateLastFileApplied}); $scriptOutRESTORE_SmallDbs_File = "$($DbaLogWalk_ScriptPath)02) RESTORE Database - ScriptOut - SmallDbs.sql"; $scriptOutRESTORE__LargeDbs_File = "$($DbaLogWalk_ScriptPath)02) RESTORE Database - ScriptOut - LargeDbs.sql"; $scriptOutRESTORE_File = if($bkpFileSize_Larger){$scriptOutRESTORE__LargeDbs_File}else{$scriptOutRESTORE_SmallDbs_File} Write-Host "Scripting out RESTORE code for database [$DestinationDbName] in file '$scriptOutRESTORE_File'" -ForegroundColor Yellow; $restoreText_Final | Out-File -FilePath $scriptOutRESTORE_File -Append; #Write-Host "Opening the scripted DATABASE RESTORE tsql code in notepad.." -ForegroundColor Green; #notepad "$scriptOutRESTORE_File"; if($RecoverDatabase -eq $false -and $GenerateRESTOREScriptOnly -eq $false) { $logWalkJobText = Get-Content "$((Get-ItemProperty $PSScriptRoot).Parent.FullName)\SQLQueries\LogWalk_Create_Job.sql"; $tsqlLogWalkJobCreation = $logWalkJobText -replace 'AjayDwivediSourceDatabase',"$SourceDbName"; $tsqlLogWalkJobCreation = $tsqlLogWalkJobCreation -replace 'AjayDwivediDestinationDatabase',"$DestinationDbName"; $SourceLogFilesNetworkPath = (Get-ItemProperty $backupPath | Select-Object -ExpandProperty DirectoryName)+'\'; $tsqlLogWalkJobCreation = $tsqlLogWalkJobCreation -replace 'SourceLogFilesNetworkPath',"$SourceLogFilesNetworkPath"; $tsqlLogWalkJobCreation = $tsqlLogWalkJobCreation -replace 'TufFilesLocation','f:\LogWalk_TUF_Files\'; $tsqlQuery = @" IF NOT EXISTS (select * from msdb.dbo.sysjobs as j where j.name = 'DBA Log Walk - Restore $SourceDbName as $DestinationDbName') SELECT 0 as [Exists] ELSE SELECT 1 as [Exists] "@; $scriptOutLogWalkJobCreation_File = "$($DbaLogWalk_ScriptPath)03) DBA Log Walk - Restore $SourceDbName as $DestinationDbName.sql"; $tsqlLogWalkJobCreation | Out-File -FilePath $scriptOutLogWalkJobCreation_File -Append; $exists = Invoke-DbaQuery -SqlInstance $destinationSrvToken -Query $tsqlQuery | Select-Object -ExpandProperty Exists; if([string]::IsNullOrEmpty($exists) -eq $true -or $exists -eq 0) { Write-Host "Creating job [DBA Log Walk - Restore $SourceDbName as $DestinationDbName] on $DestinationServer" -ForegroundColor Yellow; Invoke-DbaQuery -SqlInstance $destinationSrvToken -File $scriptOutLogWalkJobCreation_File; Write-Host "Job [DBA Log Walk - Restore $SourceDbName as $DestinationDbName] is created." -ForegroundColor Green; } else { Write-Host "Job named [DBA Log Walk - Restore $SourceDbName as $DestinationDbName] already exists. So simply scripting job creation code in file '$scriptOutLogWalkJobCreation_File'." -ForegroundColor Green; } } } |