Prashanth Jayaram

Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio

July 19, 2018 by

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

This article covers the following topics:

  1. Overview of SQL Operations Studio (SOS)
  2. How to use SQL Ops 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 SQL Ops Studio interface
  5. And more…

SQL Ops Studio

Microsoft release of new light-weight cross-platform GUI tool into the SQL Server Management umbrella is called SQL Operations Studio. SQL Operations 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 SQL Operations 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. SQL Operations 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 SQL Ops 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 SQL Ops 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 SQL Ops 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

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


Prashanth Jayaram

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
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

1,666 Views