SQL Log Backup doesn't start

[sandrola]

I schedule a new Job with multiple Tagged VM and SQL Log backup as follow

Code: Select all

$o = New-VBRJobVSSoptions
$o.Enabled = $true
$o.WinCredsId = $vbrcred.id
$o.VssSnapshotOptions.Enabled = $true
$o.IgnoreErrors = $false
#set sql options
$o.SqlBackupOptions.TransactionLogsProcessing.TruncateOnlyOnSuccessJob
$o.SqlBackupOptions.TransactionLogsProcessing = "Backup"
$o.SqlBackupOptions.BackupLogsFrequencyMin = 15
$o.SqlBackupOptions.UseDbBackupRetention = $true
$o.SqlBackupOptions.RetainDays = 15
$o.SqlBackupOptions.ProxyAutoSelect = $true
#set exchange options
$o.ExchangeBackupOptions.TransactionLogsProcessing.TruncateOnlyOnSuccessJob
Get-VBRJob -Name $job | Get-VBRJobObject -Name $tag | Set-VBRJobObjectVssOptions -Options $o

The Job VSS Options looks like this:
SqlEnabled : False
.....
VssOptions : VssSnapshotOptions: [Enabled: [True], IgnoreErrors: [False], IsCopyOnly: [False]],
WinGuestFSIndexingOptions: [Type: [None]], LinGuestFSIndexingOptions: [Type: [None]],
SqlBackupOptions: [TransactionLogsProcessing: [Backup], BackupLogsFrequencyMin: [15],
UseDbBackupRetention: [True], RetainDays: [15], ProxyAutoSelect: [True]],
ExchangeBackupOptions: [TransactionLogsProcessing: [TruncateOnlyOnSuccessJob]],
WinCredsId: [313bd032-3a08-4dc2-8cb7-aa72274c1e47], LinCredsId:
[00000000-0000-0000-0000-000000000000], Old Properties: [Enabled: [True], IgnoreErrors:
[False], GuestFSIndexingType: [None], IncludedIndexingFolders: [],
ExcludedIndexingFolders: [], LinGuestFSIndexingType: [None],
LinIncludedIndexingFolders: [], LinExcludedIndexingFolders: [], IsFirstUsage: [True]]
[/code]

The Job is setup correctly but the SQL log backup doesn't start.
If I right-click the Job in the GUI --> Edit --> Finish, the SQL log Backup start. The Job looks like this:

SqlEnabled : True
....
VssOptions : VssSnapshotOptions: [Enabled: [True], IgnoreErrors: [False], IsCopyOnly: [False]],
WinGuestFSIndexingOptions: [Type: [None]], LinGuestFSIndexingOptions: [Type: [None]],
SqlBackupOptions: [TransactionLogsProcessing: [TruncateOnlyOnSuccessJob],
BackupLogsFrequencyMin: [15], UseDbBackupRetention: [True], RetainDays: [15],
ProxyAutoSelect: [True]], ExchangeBackupOptions: [TransactionLogsProcessing:
[TruncateOnlyOnSuccessJob]], WinCredsId: [313bd032-3a08-4dc2-8cb7-aa72274c1e47],
LinCredsId: [00000000-0000-0000-0000-000000000000], Old Properties: [Enabled: [True],
IgnoreErrors: [False], GuestFSIndexingType: [None], IncludedIndexingFolders: [],
ExcludedIndexingFolders: [], LinGuestFSIndexingType: [None],
LinIncludedIndexingFolders: [], LinExcludedIndexingFolders: [], IsFirstUsage: [True]]

SqlBackupOptions.TransactionLogsProcessing = "Backup" is the only Way to let the Job backup the SQL Log.

[sandrola]

Version is 9.5 U1 on Windows 2016
I manage to set different VSS Options for the different Objects in the Job.
job = get-VBRJob -name "<job name>"
$objects = $job.GetObjectsInJob()
$object[0].VssOptions.SqlBackupOptions --> VSS options for the particular objects
For the Job itself, I can set other SQL Log settings as on create the Job in the GUI.

The Question is: How to get the SqlEnabled : true in the Job created by powershell.

[sandrola]

I have found followings:
The "SQLLogBackup" Job is not created if the "main" job is created by powershell. The "SQLLogBackup" will be created automatically if the job is created in the GUI.
If I edit the job created with powershell and I save it again the "SQLLogBackup" is created.
Must this job be created manually in powershell?

[tomas.olsen]

I am experiencing the same issue...
This is my test script for my lab environment:

#Setting VSS info on SQL Jobs with backup log
$jobs = Get-VBRJob -Name *sql*

foreach($entity in $jobs)
{
$vssoptions = Get-VBRJob -Name $entity.Name | Get-VBRJobVSSOptions
#$vssoptions.Enabled = $true
$vssoptions.SqlBackupOptions.BackupLogsEnabled = $true
$vssoptions.SqlBackupOptions.TransactionLogsProcessing = "Backup"
$vssoptions.SqlBackupOptions.BackupLogsFrequencyMin = "120"
$vssoptions.SqlBackupOptions.UseDbBackupRetention = $false
$vssoptions.SqlBackupOptions.RetainDays = "2"
Get-VBRJob -Name $entity.Name | Set-VBRJobVssOptions -Options $vssoptions | Out-Null
Set-VBRJobVssOptions -Job $entity.Name -Credentials $vsscredentials | Out-Null
Write-Host "Setting SQL VSS options for job: " $entity.Name -ForegroundColor Yellow
}

I have changed all the default settings. changed the 15min interval to 120, not to use db backup retention and I am setting the retaindays to two.

If I run this script, and run the line: $vssoptions = Get-VBRJob -Name $entity.Name | Get-VBRJobVSSOptions just after, I can see that all my settings have been applied.
If I edit the job in the GUI, and presses finish as suggested. all the settings set back to default.

This script will run through all jobs containing SQL in them and sett the correct VSS settings for my lab environment.

Anyone have any luck finding a solution to this problem?

[veremin]

Am I right that you're looking for a way not only to enable SQL transaction log backup job, but also to start it immediately (basically, simulate GUI behavior)? We will try to confirm the experienced issue. I will let you know, once we have more information. Thanks.

[tomas.olsen]

that is correct. to simulate the bevaior of the gui.

the script I posted should basically change all the default values and enable transaction log backup. If it don't start until the next scheduled backup of the server, thats ok. But the way it is now, it seems I can not edit any of the settings in the gui.
If you look at my script: If I type for instance:"$vssoptions.SqlBackupOptions.RetainDays" and hit enter, it shows Two days in powershell, after the script have been run, but not in the gui.

The script I posted is just a little thing looping through all jobs named *sql*, enabling Guest Processing and changing all the default values.

[veremin]

We will confirm the experienced behaviour; I will let you know the results of our findings.

As to settings modification, if you'd like to correct object specific options (such as SQL VM transaction log backup, etc.), you should edit them on object level, not on job one.

Something like this:

Code: Select all

Job = get-VBRJob -name "<job name>"
$object = $job.GetObjectsInJob() | where {$_.name -eq "VM Name"}
$object.VssOptions.SqlBackupOptions

Thanks.

[veremin]

To start SQL log backup job try to leverage the scripts below.

First:

Code: Select all

$Job = Get-VBRJob -name "Name of backup job"
$VssOptions = $Job.VssOptions
$VssOptions.Enabled = $True
$VssOptions.SqlBackupOptions.TransactionLogsProcessing = "Backup"
$Job.SetVssOptions($VssOptions)
$Object = $Job | Get-VBRJobObject –name “Name of SQL VM”
$ObjectVSS = $Object.VssOptions
$ObjectVSS.SqlBackupOptions.TransactionLogsProcessing = "Backup"
$Object.SetVssOptions($ObjectVSS)

Second:

Code: Select all

$Job.Info.SQLEnabled = $true
Enable-VBRJobSchedule -Job $Job