Posted on Leave a comment

Test your backup mechanism – Automated restore for MS SQL using Azure DevOps

Most organizations rely on their backup solutions for application faults or data corruptions. However the backup is not frequently tested in order to verify that restore would be successful. In this post I implement a backup testing mechanism.

Lets examine a scenario for an MS SQL database server. The server will output a backup file (.bak) on a storage account based on a retention policy. This backup will be automatically restored on a SQL server through a pipeline and a result will be written as an output. The result can be then reported on the monitoring solution.

The flow is depicted below. An azure devops agent should be installed on the server on which the database will be restored. The pipeline will fetch the backup file from the storage account and store it on a data disk (in my case R:\files). Then sqlcmd command will be used to restore the .bak file and record the result. The backup file is provided by a parameter on the pipeline. Also a service connection should be created with your subscription on which the storage account is located.

Pipeline code:

trigger: none
pr: none
pool:
vmImage: windows-latest
parameters:
– name: backupfile
type: string
jobs:
– job: download
displayName: Download DB backup file
steps:
– task: AzureCLI@2
displayName: az cli download backup file from storage account
inputs:
azureSubscription: 'Azure-Service-Connection'
scriptType: 'ps'
scriptLocation: 'inlineScript'
inlineScript: |
$container_name_input = "container_name"
$saccount_name = "storage_account_name"
#$json = az storage blob list –container-name $container_name_input –account-name $saccount_name
az storage blob download –file "R:\files\${{parameters.backupfile}}" –name "${{parameters.backupfile}}" –container-name $container_name_input –account-name $saccount_name –auth-mode login
– job: restore
displayName: Restore SQL backup
dependsOn: download
steps:
– task: PowerShell@2
displayName: sqlcmd restore backup
inputs:
targetType: 'inline'
script: |
sqlcmd -q "RESTORE DATABASE [Database_Name] FROM DISK=N'R:\files\${{parameters.backupfile}}' WITH REPLACE,RECOVERY" -o R:\files\result.txt;
[string]$result = Get-Content R:\files\result.txt
if ($result.contains('successfully')) {
Write-Host "Restore was succesfull…"
}
elseif ($result.contains('terminating')) {
Write-Host "Terminating…"
}

Executing pipeline:

Result:

Important:

Azure DevOps agent service is configured to run with a specific account (in my case NT/ Local System). This account should have the appropriate permissions on the SQL server for the restore procedure. The easier way would be to make this account a database sysadmin.

Adding the NT Authority\System on SQL server sysadmins

Posted on Leave a comment

Veeam Backup fails with log message: CBT Data is Invalid

Having your backup jobs fail is really a frustrating experience. 99,5% of the time, i’m experiencing successful backups, either over TSM, or Veeam Backup.

However, there were more than a handful of times that the CBT (Change Block Tracking) stopped working, generally causing backup jobs to fail.

The message listed in the Action log is the following:

CBT data is invalid, failing over to legacy incremental backup. No action is required, next job run should start using CBT again.
If CBT data remains invalid follow KB1113 to perform CBT reset. Usual cause is power loss.

I’ve tried to narrow the reasons this happens, to mostly being Datastore capacity outage or the speed of snapshots being removed during the backup process.

Luckily, there is a solution and it lurks in the VMWare KB2139574 (Resetting Changed Block Tracking for VMware vSphere virtual machines) .

You can generally follow one of the two recommended actions according to VMWare:

  • Power off the VM and reset CBT
  • Run the provided script to reset CBT on all VMs, which uses a snapshot method to save having to power the VMs off.

Personally, although i have tried both actions, i prefer the second approach, as most of the virtual Infrastructures contain VMs that are not easy to power off, or have different maintenance windows.

In any case, after reattempting to execute the Veeam backup job, the error is no longer there, and Change Block Tracking is back in use.