Tag: Backup

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…"
	}

view raw azure-devops-sql-restore hosted with ❤ by GitHub

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