In this 18^th article of the series, we will discuss the concepts of database backup-and-restore of SQL Server Docker containers using Azure Data Studio. Before proceeding, you need to have Docker engine installed and Azure Data Studio configured on your host machine.

This article covers the following topics:

1. Overview of Azure Data Studio (ADS)
2. How to use Azure Data Studio integrated terminal
3. Definition of Docker containers
4. Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface
5. And more…

Azure Data Studio

Microsoft release of new light-weight cross-platform GUI tool into the SQL Server Management umbrella is called Azure Data Studio. Azure Data Studio is a cross-platform graphical user interface for working with SQL Server instances. 

Feature Highlights

• It provides a cross-platform support to manage SQL Server databases for Windows, mac, and Linux or Docker containers on any platform
• SQL Server Connection Management that supports
  • Connection Dialog
  • Server Groups creation
  • Azure Integration
  • Create Registered Servers list
• It can also be used to connect to Microsoft’s cloud databases, including Azure SQL Database and Azure SQL Data Warehouse.
• In-built Object Explorer support
• Advanced T-SQL query editor support
• New Query Results Viewer with data grid support
• The result-set can be exported to JSON\CSV\Excel
• Derive custom charts
• Manage Dashboard using standard and customizable widgets and insights
• Backup and Restore database dialog
• Task History window to view the task execution status
• In-house database scripting options
• In-built Git integration
• In-built shell support using an integrated terminal options
• And more…

The list continues…

I would recommend go ahead and download the version for your platform of choice to see how it works.

Docker containers

Docker carves up a running system into small containers, each of which is sealed, segmented, with its own programs and isolated from anything else. Docker’s mission is to build, ship, and run distributed applications across anywhere and on any platform. It can be on your Local Laptop, or on the Cloud, or On-premise servers

• Containers are highly portable programs and it is kept as small as possible, and don’t have any external dependencies
• It is that easy to create a Docker image and then move or copy it to another and be certain that it’ll still work in the same way.
• To run SQL Server in a Docker container
  • Docker Engine 1.8 or higher
  • Minimum of two gigabytes of hard disk space to store the container image, as well as two gigabytes of RAM

Getting started

Let’s start Azure Data Studio and open the interactive terminal.

First, let’s download the latest SQL Server 2017 extract from the docker hub. To extract, run the docker pull command with the SQL Server 2017 image-tag. It is also possible to extract the specific Docker container images from the docker hub repository. To get the latest SQL image, type in the word “latest”, the tag, after the colon. This gives us the most recent SQL Server 2017 image.

Now, run the docker image using docker run command.

The SQL Instance is ready to accept the connections. Next, to connect to the SQL Instance, click on the Server icon on the left corner of the window.

Add the connection details

• Enter the IP address (10.2.6.50) of the host machine followed by the incoming port number, in this case, 1401.
• Enter the SA login credentials
• Click Connect

Next, follow the below steps for backing up the databases

1. To accomplish the backup task, right-click the database and select databases manage window.

   

2. For instance, right-click on SQLShackDemo database and choose manage. In the database dashboard pane, we have some useful information including the current recovery model, the last time backups were performed on the database and the log backup, and the database’s owner account

   

3. Now, let’s go ahead and click the backup icon. A new window would pop up where we can specify a backup name. Azure Data Studio suggests the name of the database that reference today’s date and time.
4. Let’s go ahead and choose the type of backup, in this case, its full backup type.
5. The backup file location, in this case, it’s displaying the full path that is relative to the Docker container. We can also set the settings using advanced configuration options.

6. Press Backup button to initiate the backup task.

   

7. Now, you can see, the sidebar is changed to the task-history view on the left. You can check the statuses of the backup job here.

   

8. When you’re done taking a look at that, you can switch back over to your server sidebar. Connect to the SQL Container and open the interactive bash terminal using docker command to verify the backup file that got created using the Azure Data Studio backup dialog.

Now, let’s dig into the second part of the process.

To perform database restore, I will be instantiating a new SQL instance SQLOpRestoreDemo using the following docker run command.

Let’s copy the backup file to host machine by navigating to a backup file directory. Now, copy the backup file from the host machine to the other SQL docker container using the following docker cp command.

Now, connect to the SQL instance by entering the required details. Here, you can see that IP address followed by a port number is entered to connect to an instance.

Next, click the restore icon on the dashboard.

In the restore database screen, select the general section; choose the backup file by navigating to the backup directory.

In the files tab, specify the location to relocate the data and log files.

In the options tab, choose the overwrite options.

You can also generate a script and run it or just press the restore button to complete the restore process.

The task history appears on the right part of the Azure Data Studio. This concludes that the database SQLShackDemo restored successfully.

You can also browse the SQL instance to verify the database.

That’s all for now…

Wrapping Up

Thus far, we see the step by step instructions to initiate a database backup and restore SQL Docker containers using Azure Data Studio interface.

We can say that it’s a light-weight version of SQL Server Management Studio (SSMS). The interface is very simple, straight-forward and self-explanatory. It is built with several options and is very well laid-out to walk you through common procedures.

Table of contents

References

• Join GitHub today
• docker cp
• Docker run reference

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021