使用Azure Data Studio在Docker容器上使用SQL Server 2017进行备份和还原操作

程序员文章站 2022-06-10 18:57:45

...

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.

在本系列的^第 18篇文章中，我们将讨论使用Azure Data Studio进行SQL Server Docker容器的数据库备份和还原的概念。在继续之前，您需要在主机上安装Docker引擎并配置Azure Data Studio。

This article covers the following topics:

本文涵盖以下主题：

Overview of Azure Data Studio (ADS)
Azure Data Studio（ADS）概述
How to use Azure Data Studio integrated terminal
如何使用Azure Data Studio集成终端
Definition of Docker containers
Docker容器的定义
Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface
分步说明以使用Azure Data Studio界面启动SQL Server 2017 Docker容器的备份和还原
And more…
和更多…

Azure数据工作室 (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.

Microsoft在SQL Server管理工具中发布了新的轻量级跨平台GUI工具，称为Azure Data Studio。 Azure Data Studio是一个跨平台的图形用户界面，用于处理SQL Server实例。

Feature Highlights

功能亮点

It provides a cross-platform support to manage SQL Server databases for Windows, mac, and Linux or Docker containers on any platform
它提供了跨平台支持，可以在任何平台上管理Windows，Mac和Linux或Docker容器SQL Server数据库。
SQL Server Connection Management that supports
- Connection Dialog
- Server Groups creation
- Azure Integration
- Create Registered Servers list
支持SQL Server连接管理
- 连接对话框
- 服务器组创建
- Azure集成
- 创建注册服务器列表
It can also be used to connect to Microsoft’s cloud databases, including Azure SQL Database and Azure SQL Data Warehouse.
它还可以用于连接到Microsoft的云数据库，包括Azure SQL数据库和Azure SQL数据仓库。
In-built Object Explorer support
内置对象资源管理器支持
Advanced T-SQL query editor support
先进的T-SQL查询编辑器支持
New Query Results Viewer with data grid support
带有数据网格支持的新查询结果查看器
The result-set can be exported to JSON\CSV\Excel
结果集可以导出到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
内置Git集成
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容器 (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

Docker将一个正在运行的系统分割成多个小容器，每个小容器都用自己的程序密封，分段，并与其他任何对象隔离。 Docker的使命是跨任何地方，在任何平台上构建，交付和运行分布式应用程序。它可以在您的本地笔记本电脑上，也可以在云上，或在本地服务器上

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.
创建Docker映像，然后将其移动或复制到另一个映像并确保它仍然可以以相同的方式工作非常容易。
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
在Docker容器中运行SQL Server
- Docker Engine 1.8或更高版本
- 至少有2 GB的硬盘空间来存储容器映像，以及2 GB的RAM

入门 (Getting started)

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

让我们启动Azure Data Studio并打开交互式终端。

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.

首先，让我们从Docker中心下载最新SQL Server 2017摘录。要提取数据，请运行带有SQL Server 2017 image-tag的docker pull命令 。还可以从Docker Hub存储库中提取特定的Docker容器映像。要获取最新SQL映像，请在冒号后键入单词“ latest”（即标记）。这为我们提供了最新SQL Server 2017映像。

[aaa@qq.com thanvitha]# docker pull microsoft/mssql-server-linux:latest

Now, run the docker image using docker run command.

现在，使用docker run命令运行docker映像。

[aaa@qq.com thanvitha]# docker run -e 'ACCEPT_EULA=Y' -e 'aaa@qq.com' -p 1401:1433 --name SQLOpsBackupDemo -d microsoft/mssql-server-linux:latest

使用Azure Data Studio在Docker容器上使用SQL Server 2017进行备份和还原操作

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.

SQL实例已准备就绪，可以接受连接。接下来，要连接到SQL实例，请单击窗口左上角的“服务器”图标。

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.
输入主机的IP地址（10.2.6.50），然后输入进入的端口号，在本例中为1401。
Enter the SA login credentials
输入SA登录凭据
Click Connect
点击连接

Next, follow the below steps for backing up the databases

接下来，请按照以下步骤备份数据库

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

要完成备份任务，请右键单击数据库，然后选择“ 数据库管理”窗口。
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

例如，右键单击SQLShackDemo数据库，然后选择manage 。在数据库仪表板窗格中，我们提供了一些有用的信息，包括当前的恢复模型，上次对数据库和日志备份执行备份的时间以及数据库的所有者帐户
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.
现在，让我们继续，然后单击备份图标。将会弹出一个新窗口，我们可以在其中指定备份名称。 Azure Data Studio建议引用今天日期和时间的数据库名称。
Let’s go ahead and choose the type of backup, in this case, its full backup type.
让我们继续选择备份类型，在这种情况下，选择其完整备份类型。
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.
在这种情况下，备份文件位置将显示相对于Docker容器的完整路径。我们还可以使用高级配置选项来设置设置。
Press Backup button to initiate the backup task.

按“备份”按钮启动备份任务。
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.

现在，您可以看到侧边栏更改为左侧的任务历史视图。您可以在此处检查备份作业的状态。
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.
看完之后，您可以切换回服务器侧边栏。连接到SQL容器，并使用docker命令打开交互式bash终端，以验证使用Azure Data Studio备份对话框创建的备份文件。

[aaa@qq.com thanvitha]# docker exec -it SQLOpsBackupDemo bash
 
aaa@qq.com:/# ls -l /var/opt/mssql/data/*.bak
-rw-r-----. 1 root root 434176 May 25 14:26 /var/opt/mssql/data/SQLShackDemo-2018525-10-24-39.bak

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.

为了执行数据库还原，我将使用以下docker run命令实例化一个新SQL实例SQLOpRestoreDemo 。

[aaa@qq.com thanvitha]# docker run -e 'ACCEPT_EULA=Y' -e 'aaa@qq.com' -p 1402:1433 --name SQLOpRestoreDemo -d microsoft/mssql-server-linux:latest

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.

让我们通过导航到备份文件目录将备份文件复制到主机。现在，使用以下docker cp命令将备份文件从主机复制到另一个SQL docker容器。

[aaa@qq.com thanvitha]# docker cp SQLOpsBackupDemo:/var/opt/mssql/data/SQLShackDemo-2018525-10-24-39.bak /tmp
 
[aaa@qq.com thanvitha]# docker cp /tmp/SQLShackDemo-2018525-10-24-39.bak SQLOpRestoreDemo:/var/opt/mssql/data/

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.

现在，通过输入所需的详细信息连接到SQL实例。在这里，您可以看到输入IP地址和端口号以连接到实例。

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.

任务历史记录显示在Azure Data Studio的右侧。得出结论，数据库SQLShackDemo已成功还原。

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

您还可以浏览SQL实例以验证数据库。

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.

到目前为止，我们看到了逐步说明以使用Azure Data Studio界面启动数据库备份和还原SQL Docker容器。

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.

我们可以说它是SQL Server Management Studio（SSMS）的轻量级版本。界面非常简单，简单明了。它具有多个选项，并且布局合理，可引导您完成常见的过程。