SQL log-shipping, but only on certain days - find out how

In a customer project I just stumbled across the requirement to do SQL log-shipping with VMs. A simple task, I thought at first. But this time the request included a so-called division by zero - at least from a VBR perspective. 

With VBR (at least until V12), it is not possible to restrict log-shipping to certain days of the week. You can only set the overall frequency of log backups: Microsoft SQL Server Transaction Log Settings - User Guide for VMware vSphere (veeam.com)

You can set a schedule for image-level backups of course. But in between, log backups are performed continuously as defined by the above setting.

It came in very handy that @SteveHeart recently published another script that can determine which VBR backup job a PS script was started out of. The code in mention to actually determine the process ID of the VBR job above goes back to @tsightler. So the script can be used flexibly as you don’t have to hard code job names etc. So I “borrowed” this code and enriched it with toggling log-shipping.

The script has to be run as a pre-script from inside a VBR backup job. It has to be the pre- and not the post-script, because you always need a full backup after having enabled log-shipping to define the starting point for the logs. Make sure not to use pre-freeze or post-thaw scripting inside AAP.

The days of the week to switch on or off at can be defined by two parameters in the command line. 

So the full command might e.g. look like this: 

E:\Scripts\sql-t-log-toggle.ps1 -DayToDisable "Thursday" -DayToEnable "Monday"

Make sure to run the script after every backup session. It’ll then determine the day of the week and toggle log-shipping as needed.

I tested it on vSphere only so far, but I would expect it to work with Hyper-V as well.

Finally for the code:

<# 
.NAME
    Veeam Backup & Replication - SQL Log-Shipping (T-Log) Toggle
.DESCRIPTION
    This script enables or disables SQL log-shipping depending on the day of the week 
    Days to switch them on or off can be configured via mandatory parameters.

    This script is for use as a pre-job script within a backup job. A message is generated in the corresponding backup job 
    about the action taken.

.NOTES  
    File Name  : sql-t-log-toggle.ps1
    Author     : Michael Melter
    Requires   : PowerShell, Veeam Backup & Replication v11+
.VERSION
    0.9
#>

Param(
    [Parameter(Mandatory=$true)]
    [string]$DayToEnable,
    [Parameter(Mandatory=$true)]
    [string]$DayToDisable
     )

# Function to get Process ID - Credits to Tom Sightler
function Get-VbrJobSessionFromPID {
$parentpid = (Get-WmiObject Win32_Process -Filter "processid='$pid'").parentprocessid.ToString()
$parentcmd = (Get-WmiObject Win32_Process -Filter "processid='$parentpid'").CommandLine
$job       = Get-VBRJob -WarningAction SilentlyContinue | ?{$parentcmd -like "*"+$_.Id.ToString()+"*"}
$session   = Get-VBRBackupSession | ?{($_.OrigJobName -eq $job.Name) -and ($parentcmd -like "*"+$_.Id.ToString()+"*")}
    return $session
}

# Function to get JobName from running process
function Get-VbrJobNameFromPID {
    $parentPid = (Get-WmiObject Win32_Process -Filter "processid='$pid'").parentprocessid.ToString()
    $parentCmd = (Get-WmiObject Win32_Process -Filter "processid='$parentPid'").CommandLine
    $cmdArgs = $parentCmd.Replace('" "','","').Replace('"','').Split(',')
    $jobName = (Get-VBRJob | ? {$cmdArgs[4] -eq $_.Id.ToString()}).Name
    return $jobName
}

#Variables
$finalResult     = @()
$bkpJobName      = Get-VbrJobNameFromPID

$bkpJob          = Get-VBRJob -Name $bkpJobName -WarningAction SilentlyContinue
$bkpSession      = Get-VBRBackupSession| Where-Object {$_.jobId -eq $bkpJob.Id.Guid} | Where-Object  {$_.sessioninfo.SessionAlgorithm -eq "Increment"} | Sort-Object EndTimeUTC -Descending
$bkpJobObject    = Get-VBRJobObject -Job $bkpJob

# Get Backup Session
$BackupSession   = Get-VbrJobSessionFromPID

# Determine if day to be toggled is the case
if ((Get-Date).DayOfWeek -eq $DayToEnable) { 
    foreach ($JobObject in $bkpJobObject) { 
        $JobObjectVss = Get-VBRJobObjectVssOptions -ObjectInJob $JobObject
        $JobObjectVss.SqlBackupOptions.TransactionLogsProcessing ="Backup"
        Set-VBRJobObjectVssOptions -Object $JobObject -Options $JobObjectVss
        $message = "T-Log activated for VM: "+$JobObject.Name
        $BackupSession.Logger.AddSuccess($message)
        }
    }
elseif ((Get-Date).DayOfWeek -eq $DayToDisable) { 
    foreach ($JobObject in $bkpJobObject) { 
        $JobObjectVss = Get-VBRJobObjectVssOptions -ObjectInJob $JobObject
        $JobObjectVss.SqlBackupOptions.TransactionLogsProcessing ="TruncateOnlyOnSuccessJob"
        Set-VBRJobObjectVssOptions -Object $JobObject -Options $JobObjectVss
        $message = "T-Log de-activated for VM: "+$JobObject.Name
        $BackupSession.Logger.AddSuccess($message)
    }
 }
 else {
        $BackupSession.Logger.AddSuccess("Not a day to switch T-Log status")
 }

I shall maybe put it on github one day… 😎