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.
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