将数据库还原到 SQL Server 大数据群集主实例Restore a database into the SQL Server big data cluster master instance

适用于:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x)适用于:Applies to: 是SQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x)

本文介绍如何将现有数据库还原到 SQL Server 2019 大数据群集SQL Server 2019 Big Data Clusters 的主实例中。This article describes how to restore an existing database into the master instance of a SQL Server 2019 大数据群集SQL Server 2019 Big Data Clusters. 建议使用备份、复制和还原。The recommended method is to use a backup, copy, and restore approach.

备份现有数据库Backup your existing database

首先,从 Windows 或 Linux 上的 SQL Server 备份现有 SQL Server 数据库。First, backup your existing SQL Server database from either SQL Server on Windows or Linux. 将标准备份技术与 Transact-SQL 或 SQL Server Management Studio (SSMS) 等工具结合使用。Use standard backup techniques with Transact-SQL or with a tool like SQL Server Management Studio (SSMS).

本文演示如何还原 AdventureWorks 数据库,但可以使用任何数据库备份。This article shows how to restore the AdventureWorks database, but you can use any database backup.

提示

下载 AdventureWorks 备份Download the AdventureWorks backup.

复制备份文件Copy the backup file

将备份文件复制到 Kubernetes 群集的主实例 Pod 中的 SQL Server 容器。Copy the backup file to the SQL Server container in the master instance pod of the Kubernetes cluster.

kubectl cp <path to .bak file> master-0:/var/tmp/<.bak filename> -c mssql-server -n <name of your big data cluster>

示例:Example:

kubectl cp ~/Downloads/AdventureWorks2016CTP3.bak master-0:/var/tmp/AdventureWorks2016CTP3.bak -c mssql-server -n clustertest

然后,验证是否已将备份文件复制到 Pod 容器。Then, verify that the backup file was copied to the pod container.

kubectl exec -it master-0 -n <name of your big data cluster> -c mssql-server -- bin/bash
cd /var/
ls /tmp
exit

示例:Example:

kubectl exec -it master-0 -n clustertest -c mssql-server -- bin/bash
cd /var/
ls /tmp
exit

还原备份文件Restore the backup file

接下来,将数据库备份还原到主实例 SQL Server。Next, restore the database backup to master instance SQL Server. 如果要还原在 Windows 上创建的数据库备份,则需要获取文件的名称。If you are restoring a database backup that was created on Windows, you will need to get the names of the files. 在 Azure Data Studio 中,连接到主实例并运行以下 SQL 脚本:In Azure Data Studio, connect to the master instance and run this SQL script:

RESTORE FILELISTONLY FROM DISK='/tmp/<db file name>.bak'

示例:Example:

RESTORE FILELISTONLY FROM DISK='/tmp/AdventureWorks2016CTP3.bak'

备份文件列表

现在,还原数据库。Now, restore the database. 以下脚本是一个示例。The following script is an example. 根据需要替换名称/路径,具体取决于数据库备份。Replace the names/paths as needed depending on your database backup.

RESTORE DATABASE AdventureWorks2016CTP3
FROM DISK='/tmp/AdventureWorks2016CTP3.bak'
WITH MOVE 'AdventureWorks2016CTP3_Data' TO '/var/opt/mssql/data/AdventureWorks2016CTP3_Data.mdf',
        MOVE 'AdventureWorks2016CTP3_Log' TO '/var/opt/mssql/data/AdventureWorks2016CTP3_Log.ldf',
        MOVE 'AdventureWorks2016CTP3_mod' TO '/var/opt/mssql/data/AdventureWorks2016CTP3_mod'

配置数据池和 HDFS 访问Configure data pool and HDFS access

现在,针对访问数据池和 HDFS 的 SQL Server 主实例,请运行数据池和存储池存储过程。Now, for the SQL Server master instance to access data pools and HDFS, run the data pool and storage pool stored procedures. 针对新还原的数据库运行以下 Transact-SQL 脚本:Run the following Transact-SQL scripts against your newly restored database:

USE AdventureWorks2016CTP3
GO
-- Create the SqlDataPool data source:
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlDataPool')
  CREATE EXTERNAL DATA SOURCE SqlDataPool
  WITH (LOCATION = 'sqldatapool://controller-svc/default');

-- Create the SqlStoragePool data source:
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlStoragePool')
   CREATE EXTERNAL DATA SOURCE SqlStoragePool
   WITH (LOCATION = 'sqlhdfs://controller-svc/default');
GO

备注

只有针对从旧版 SQL Server 还原的数据库才必须运行这些安装脚本。You will have to run through these setup scripts only for databases restored from older versions of SQL Server. 如果在 SQL Server 主实例中创建新数据库,则系统已为你配置数据池和存储池存储过程。If you create a new database in SQL Server master instance, data pool and storage pool store procedures are already configured for you.

后续步骤Next steps

若要了解有关 SQL Server 大数据群集SQL Server Big Data Clusters 的详细信息,请参阅以下概述:To learn more about the SQL Server 大数据群集SQL Server Big Data Clusters, see the following overview: