在 Azure SQL Edge 中备份和还原数据库Back up and restore databases in Azure SQL Edge

Azure SQL Edge 基于最新版本的 Microsoft SQL 数据库引擎而构建。Azure SQL Edge is built on the latest versions of the Microsoft SQL Database Engine. 它提供的备份和还原数据库功能与 Linux 上的 SQL Server 以及在容器中运行的 SQL Server 提供的备份和还原数据库功能类似。It provides similar backup and restore database capabilities as those available in SQL Server on Linux and SQL Server running in containers. 备份和还原组件为保护在 Azure SQL Edge 数据库中存储的数据提供了基本安全保障。The backup and restore component provides an essential safeguard for protecting data stored in your Azure SQL Edge databases.

为了尽量降低灾难性数据丢失的风险,你应当定期备份数据库,以便定期保存对数据的修改。To minimize the risk of catastrophic data loss, you should back up your databases periodically to preserve modifications to your data on a regular basis. 计划良好的备份和还原策略有助于保护数据库,使之免受各种故障导致的数据丢失的威胁。A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. 请测试策略,方法是先还原一组备份,然后恢复数据库,以便准备好对灾难进行有效的响应。Test your strategy by restoring a set of backups and then recovering your database, to prepare you to respond effectively to a disaster.

若要详细了解备份的重要性,请参阅 SQL Server 数据库的备份和还原To read more about why backups are important, see Backup and restore of SQL Server databases.

使用 Azure SQL Edge 可以备份到本地存储和 Azure blob,并可以从本地存储和 Azure blob 中进行还原。Azure SQL Edge enables you to back up to and restore from both local storage and Azure blobs. 有关详细信息,请参阅使用 Azure Blob 存储执行 SQL Server 备份和还原到 URL 的 SQL Server 备份For more information, see SQL Server backup and restore with Azure Blob storage and SQL Server backup to URL.

在 Azure SQL Edge 中备份数据库Back up a database in Azure SQL Edge

Azure SQL Edge 支持的备份类型与 SQL Server 相同。Azure SQL Edge supports the same backup types as SQL Server. 有关完整列表,请参阅备份概述For a complete list, see Backup overview.

重要

默认情况下,在 Azure SQL Edge 中创建的数据库使用简单恢复模式。Databases created in Azure SQL Edge use the simple recovery model by default. 因此,不能在这些数据库上执行日志备份。As such, you can't perform log backups on these databases. 如果你需要执行此操作,则需要管理员将数据库恢复模式更改为完整恢复模式。If you need to do this, you'll need an administrator to change the database recovery model to the full recovery model. 有关 SQL Server 支持的恢复模式的完整列表,请参阅恢复模式概述For a complete list of recovery models supported by SQL Server, see Recovery model overview.

备份到本地磁盘Back up to local disk

在下面的示例中,你将使用 Transact-SQL 命令 BACKUP DATABASE 在容器中创建数据库备份。In the following example, you use the BACKUP DATABASE Transact-SQL command to create a database backup in the container. 出于此示例的目的,你将创建一个名为 backup 的新文件夹来存储备份文件。For the purpose of this example, you create a new folder called backup to store the backup files.

  1. 创建用于备份的文件夹。Create a folder for the backups. 在运行 Azure SQL Edge 容器的主机上运行此命令。Run this command on the host where your Azure SQL Edge container is running. 在下面的命令中,将 <AzureSQLEdge_Container_Name> 替换为你的部署中的 Azure SQL Edge 容器的名称。In the following command, replace <AzureSQLEdge_Container_Name> with the name of the Azure SQL Edge container in your deployment.

    sudo docker exec -it <AzureSQLEdge_Container_Name> mkdir /var/opt/mssql/backup
    
  2. 使用 SQL Server Management Studio (SSMS) 或使用 Azure Data Studio 连接到 Azure SQL Edge 实例。Connect to the Azure SQL Edge instance by using SQL Server Management Studio (SSMS), or by using Azure Data Studio. 运行 BACKUP DATABASE 命令来备份用户数据库。Run the BACKUP DATABASE command to take the backup of your user database. 在下面的示例中,你将对 IronOreSilicaPrediction 数据库进行备份。In the following example, you're taking the backup of the IronOreSilicaPrediction database.

    BACKUP DATABASE [IronOreSilicaPrediction] 
    TO DISK = N'/var/opt/mssql/backup/IronOrePredictDB.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'IronOreSilicaPrediction-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
    GO
    
  3. 运行该命令后,如果数据库备份成功,将在 SSMS 或 Azure Data Studio 的“结果”部分中看到类似于以下内容的消息。After you run the command, if the backup of the database is successful, you'll see messages similar to the following in the results section of SSMS or Azure Data Studio.

    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
    60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    100 percent processed.
    Processed 51648 pages for database 'IronOreSilicaPrediction', file 'IronOreSilicaPrediction' on file 1.
    Processed 2 pages for database 'IronOreSilicaPrediction', file 'IronOreSilicaPrediction_log' on file 1.
    BACKUP DATABASE successfully processed 51650 pages in 3.588 seconds (112.461 MB/sec).
    
    Completion time: 2020-04-09T23:54:48.4957691-07:00
    

备份到 URLBack up to URL

Azure SQL Edge 支持备份到页 blob 和块 blob。Azure SQL Edge supports backups to both page blobs and block blobs. 有关详细信息,请参阅备份到块 blob 与备份到页 blobFor more information, see Back up to block blob vs page blob. 在下面的示例中,数据库 IronOreSilicaPrediction 将备份到块 blob。In the following example, the database IronOreSilicaPrediction is being backed up to a block blob.

  1. 若要配置备份到块 blob,请首先生成可用于在 Azure SQL Edge 上创建 SQL Server 凭据的共享访问签名 (SAS) 令牌。To configure backups to block blobs, first generate a shared access signature (SAS) token that you can use to create a SQL Server credential on Azure SQL Edge. 该脚本创建与存储访问策略关联的共享访问签名。The script creates a SAS that is associated with a stored access policy. 有关详细信息,请参阅共享访问签名,第 1 部分:了解 SAS 模型,详细了解 SAS 以及 SAS 使用方面的最佳做法。For more information, see Shared access signatures, part 1: Understanding the SAS model. 此脚本还编写在 SQL Server 上创建凭据时所需的 T-SQL 命令。The script also writes the T-SQL command required to create the credential on SQL Server. 以下脚本假设你已有一个带有存储帐户的 Azure 订阅,并有一个用于备份的存储容器。The following script assumes that you already have an Azure subscription with a storage account, and a storage container for the backups.

    # Define global variables for the script  
    $subscriptionName='<your subscription name>'   # the name of subscription name you will use
    $resourcegroupName = '<your resource group name>' # the name of resource group you will use
    $storageAccountName= '<your storage account name>' # the storage account name you will use for backups
    $containerName= '<your storage container name>'  # the storage container name to which you will attach the SAS policy with its SAS token  
    $policyName = 'SASPolicy' # the name of the SAS policy  
    
    # adds an authenticated Azure account for use in the session
    Login-AzAccount -Environment AzureChinaCloud
    
    # set the tenant, subscription and environment for use in the rest of
    Select-AzSubscription -Subscription $subscriptionName
    
    # Generate the SAS token
    $sa = Get-AzStorageAccount -ResourceGroupName $resourcegroupName -Name $storageAccountName 
    $storagekey = Get-AzStorageAccountKey -ResourceGroupName $resourcegroupName -Name $storageAccountName 
    $storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storagekey[0].Value
    $cbc = Get-AzStorageContainer -Name $containerName -Context $storageContext
    $policy = New-AzStorageContainerStoredAccessPolicy -Container $containerName -Policy $policyName -Context $storageContext -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(10) -Permission "rwld"
    $sas = New-AzStorageContainerSASToken -Policy $policyName -Context $storageContext -Container $containerName
    Write-Host 'Shared Access Signature= '$($sas.Substring(1))''
    
    # Outputs the Transact SQL to the clipboard and to the screen to create the credential using the Shared Access Signature  
    Write-Host 'Credential T-SQL'  
    $tSql = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.CloudBlobContainer.Uri.AbsoluteUri,$sas.Substring(1)
    $tSql | clip  
    Write-Host $tSql
    

    成功运行脚本后,请将 CREATE CREDENTIAL 命令复制到查询工具。After successfully running the script, copy the CREATE CREDENTIAL command to a query tool. 然后,连接到 SQL Server 实例,并运行命令以使用 SAS 创建凭据。Then connect to an instance of SQL Server, and run the command to create the credential with the SAS.

  2. 使用 SSMS 或 Azure Data Studio 连接到 Azure SQL Edge 实例,并使用上一步骤中的命令创建凭据。Connect to the Azure SQL Edge instance by using SSMS or Azure Data Studio, and create the credential by using the command from the previous step. 确保将 CREATE CREDENTIAL 命令替换为上一步骤中的实际输出。Make sure to replace the CREATE CREDENTIAL command with the actual output from the previous step.

    IF NOT EXISTS  
    (SELECT * FROM sys.credentials
    WHERE name = 'https://<mystorageaccountname>.blob.core.chinacloudapi.cn/<mystorageaccountcontainername>')  
    CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.chinacloudapi.cn/<mystorageaccountcontainername>]
       WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
       SECRET = '<SAS_TOKEN>';
    
  3. 以下命令会将 IronOreSilicaPrediction 备份到 Azure 存储容器。The following command takes a backup of the IronOreSilicaPrediction to the Azure storage container.

    BACKUP DATABASE IronOreSilicaPrediction
    TO URL = 'https://<mystorageaccountname>.blob.core.chinacloudapi.cn/<mycontainername>/IronOreSilicaPrediction.bak'
    With MAXTRANSFERSIZE = 4194304,BLOCKSIZE=65536;  
    GO
    

在 Azure SQL Edge 中还原数据库Restore a database in Azure SQL Edge

在 Azure SQL Edge 中,可以从本地磁盘、网络位置或 Azure Blob 存储帐户进行还原。In Azure SQL Edge, you can restore from a local disk, a network location, or an Azure Blob storage account. 有关 SQL Server 中的还原和恢复的详细信息,请参阅还原和恢复概述For more information about restore and recovery in SQL Server, see Restore and recovery overview. 若要大致了解 SQL Server 中的简单恢复模式,请参阅完整数据库还原(简单恢复模式)For an overview of the simple recovery model in SQL Server, see Complete database restores (simple recovery model).

重要

在 Azure SQL Edge 中创建的数据库无法在 Microsoft SQL Server 或 Azure SQL 的实例上还原。Databases created in Azure SQL Edge cannot be restored on an instance of Microsoft SQL Server or Azure SQL. 此外,在 Microsoft SQL Server 或 Azure SQL 上创建的数据库可以在 Azure SQL Edge 上还原,前提是该数据库不包含 Azure SQL Edge 不支持的任何功能。Additionally, a database created on Microsoft SQL Server or Azure SQL can be restored on Azure SQL Edge, provided the database does not contain any of the features not supported by Azure SQL Edge.

从本地磁盘还原Restore from a local disk

此示例使用你在前面的示例中创建的 IronOreSilicaPrediction 备份。This example uses the IronOreSilicaPrediction backup that you made in the previous example. 现在,你将使用一个不同的名称将该备份还原为新数据库。Now, you'll restore it as a new database with a different name.

  1. 如果容器中还没有该数据库备份文件,可以使用下面的命令将该文件复制到容器中。If the database backup file isn't already present in the container, you can use the following command to copy the file into the container. 下面的示例假定该备份文件存在于本地主机上,并且被复制到名为 sql1 的 Azure SQL Edge 容器的 /var/opt/mssql/backup 文件夹中。The following example assumes that the backup file is present on the local host, and is being copied to the /var/opt/mssql/backup folder into an Azure SQL Edge container named sql1.

    sudo docker cp IronOrePredictDB.bak sql1:/var/opt/mssql/backup
    
  2. 使用 SSMS 或 Azure Data Studio 连接到 Azure SQL Edge 实例,以运行还原命令。Connect to the Azure SQL Edge instance by using SSMS or Azure Data Studio to run the restore command. 在下面的示例中,将还原 IronOrePredictDB.bak 以创建新数据库 IronOreSilicaPrediction_2。In the following example, IronOrePredictDB.bak is restored to create a new database, IronOreSilicaPrediction_2.

    Restore FilelistOnly from disk = N'/var/opt/mssql/backup/IronOrePredictDB.bak'
    
    Restore Database IronOreSilicaPrediction_2
    From disk = N'/var/opt/mssql/backup/IronOrePredictDB.bak'
    WITH MOVE 'IronOreSilicaPrediction' TO '/var/opt/mssql/data/IronOreSilicaPrediction_Primary_2.mdf',
    MOVE 'IronOreSilicaPrediction_log' TO '/var/opt/mssql/data/IronOreSilicaPrediction_Primary_2.ldf'
    
  3. 运行还原命令后,如果还原操作成功,则会在输出窗口中看到类似于以下内容的消息。After you run the restore command, if the restore operation was successful, you'll see messages similar to the following in the output window.

    Processed 51648 pages for database 'IronOreSilicaPrediction_2', file 'IronOreSilicaPrediction' on file 1.
    Processed 2 pages for database 'IronOreSilicaPrediction_2', file 'IronOreSilicaPrediction_log' on file 1.
    RESTORE DATABASE successfully processed 51650 pages in 6.543 seconds (61.670 MB/sec).
    
    Completion time: 2020-04-13T23:49:21.1600986-07:00
    

从 URL 还原Restore from URL

Azure SQL Edge 还支持从 Azure 存储帐户还原数据库。Azure SQL Edge also supports restoring a database from an Azure Storage account. 可以从块 blob 或页 blob 备份进行还原。You can restore from either the block blobs or page blob backups. 在下面的示例中,将还原块 blob 上的 IronOreSilicaPrediction_2020_04_16.bak 数据库备份文件,以创建数据库 IronOreSilicaPrediction_3。In the following example, the IronOreSilicaPrediction_2020_04_16.bak database backup file on a block blob is restored to create the database, IronOreSilicaPrediction_3.

RESTORE DATABASE IronOreSilicaPrediction_3
FROM URL = 'https://mystorageaccount.blob.core.chinacloudapi.cn/mysecondcontainer/IronOreSilicaPrediction_2020_04_16.bak'
WITH MOVE 'IronOreSilicaPrediction' TO '/var/opt/mssql/data/IronOreSilicaPrediction_Primary_3.mdf', 
MOVE 'IronOreSilicaPrediction_log' TO '/var/opt/mssql/data/IronOreSilicaPrediction_Primary_3.ldf',
STATS = 10;