We recently published a blog post by Shand Glenn titled, “Your Disaster Recovery Strategy Is Useless If Your SQL Server Backups Haven’t Been Tested” that discusses the need for testing your backups and outlines a process to assist in automating that testing. It got me thinking about how SQL Server on Linux containers can be used along with Docker and Azure DevOps to create an automated process for testing your backups.
This idea is still relatively rough and isn’t a replacement for testing on an equivalent production system. However, this process would help you detect any corruption occurring in your backups and provide you with additional confidence that your backups are in a reliable state.
We will leverage my previous blog post, “Restoring an AdventureWorks Database on a SQL Server on Linux Container,” to bootstrap this idea. At the end of that blog post, there is a section called “An Alternative Approach to Building the Container” that shows how to download the backup into the container to restore it. That example will be the base Dockerfile for demonstrating how this could work. If your backups aren’t available over HTTP, I will discuss an alternative approach for that.
Organizing Your Dockerfiles
You don’t have to leverage Azure DevOps to create a similar setup; that is just what I am going to demonstrate. This approach should work for any other system that can run tasks and store your Dockerfiles.
Let’s create a directory called backup-tests, and inside of that folder create two additional directories, adventureworks2019 and adventureworks2017. Inside each of those directories, let’s make a Dockerfile. You should now have a directory structure that looks like the following:
We are placing the Dockerfiles in their own directory because of how Docker loads the context. You can read more about that here. This is a fairly standard approach that you will see often.
Creating Our Dockerfiles
Now that we have our layout in place, let’s open the Dockerfile in the adventureworks2019 directory and paste in the following code:
This example shows how to pull a backup file over HTTP into your container to restore it. If the restore fails, then you know that your backup might be corrupt.
Let’s turn to pulling a backup that isn’t available over HTTP. This is a more difficult one to discuss due to potentially needing extra plugins and libraries installed to enable Docker to mount shared drives. Here are some resources for mounting a share if that is the direction you want to take.
We are going to take an approach that isn’t as elegant. We will provide a mechanism outside of Docker to download the backup and place it inside the same directory as the Dockerfile. Then, we can use the approach of just copying the backup into the container as part of the container build. Let’s open the Dockerfile in the adventureoworks2017 directory and add the following.
Orchestrating All of These Steps
Okay, we have all parts in place, so we can now set up some automation. I prefer to create a script to perform my tasks locally to use with Azure DevOps. This gives me the ability to test locally and keeps my build YAML small. Let’s create our build.ps1 to start.
Now that we have our build.ps1, let’s run it locally to make sure that it works.
The final step is to make this up as a pipeline in Azure DevOps, which will make it easy to exercise and execute the building of your containers. Create an azure-pipelines.yml next to your build.ps1 with the following content.
Next Steps
We now have a good foundation to start extending. Additional Dockerfiles could be added for more backups and other types of checks that will build confidence that your backups are healthy.
Thank you for reading! I hope this helps you generate ideas for how to make it easier to test your backups.
Jamie (@phillipsj73) is a Senior Cloud Engineer at SentryOne and working remotely in East Tennessee. He has been working with .NET since 2007 after discovering .NET development in graduate school. His Geology degree has given him an appreciation for large systems and processes which has created keen interest in Solutions Architecture, DevOps, and the Cloud. During the day he work on Windows, but at night he is all about Linux.
