教學課程:搭配使用 Azure Blob 儲存體服務和 SQL Server 2016Tutorial: Use Azure Blob storage service with SQL Server 2016

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

歡迎使用在 Microsoft Azure Blob 儲存體服務教學課程中使用 SQL Server 2016。Welcome to the Working with SQL Server 2016 in Microsoft Azure Blob Storage service tutorial. 本教學課程可協助您了解如何將 Microsoft Azure Blob 儲存體服務用於 SQL Server 資料檔案和 SQL Server 備份。This tutorial helps you understand how to use the Microsoft Azure Blob storage service for SQL Server data files and SQL Server backups.

Microsoft Azure Blob 儲存體服務的 SQL Server 整合支援一開始是 SQL Server 2012 Service Pack 1 CU2 增強功能,並且已使用 SQL Server 2014 和 SQL Server 2016 進一步加強。SQL Server integration support for the Microsoft Azure Blob storage service began as a SQL Server 2012 Service Pack 1 CU2 enhancement, and has been enhanced further with SQL Server 2014 and SQL Server 2016. 如需功能概觀以及使用此功能的優點,請參閱 Microsoft Azure 中的 SQL Server 資料檔案For an overview of the functionality and benefits of using this feature, see SQL Server Data Files in Microsoft Azure. 如需即時的示範,請參閱 File-Snapshot Backups Demo (檔案快照集備份示範)For a live demo, see Demo of Point in Time Restore.

本教學課程會在多個章節中示範如何在 Microsoft Azure Blob 儲存體服務中使用 SQL Server 資料檔案。This tutorial shows you how to work with SQL Server Data Files in Microsoft Azure Blob storage service in multiple sections. 每個章節都著重在特定工作,並且應該依序完成章節。Each section is focused on a specific task and the sections should be completed in sequence. 首先,您將學習如何使用預存存取原則和共用存取簽章在 Blob 儲存體中建立新的容器。First, you will learn how to create a new container in Blob storage with a stored access policy and a shared access signature. 然後,您將學習如何建立 SQL Server 認證,以便整合 SQL Server 與 Azure Blob 儲存體。Then, you will learn how to create a SQL Server credential to integrate SQL Server with Azure Blob storage. 接下來,您會將資料庫備份至 Blob 儲存體,並將它還原至 Azure 虛擬機器。Next, you will back up a database to Blob storage and restore it to an Azure virtual machine. 您接著將使用 SQL Server 2016 檔案快照集交易記錄備份還原至某個時間點和新的資料庫。You will then use SQL Server 2016 file-snapshot transaction log backup to restore to a point in time and to a new database. 最後,本教學課程將示範如何使用中繼資料系統預存程序和函數,協助您了解和使用檔案快照集備份。Finally, the tutorial will demonstrate the use of meta data system stored procedures and functions to help you understand and work with file-snapshot backups.

PrerequisitesPrerequisites

若要完成本教學課程,您必須熟悉 SQL ServerSQL Server 備份與還原概念以及 T-SQL 語法。To complete this tutorial, you must be familiar with SQL ServerSQL Server backup and restore concepts and T-SQL syntax. 若要使用本教學課程,您需要 Azure 儲存體帳戶、SQL Server Management Studio (SSMS)、存取 SQL Server 內部部署執行個體的權限、存取執行 SQL Server 2016 Azure 虛擬機器 (VM) 的權限,以及 AdventureWorks2016 資料庫。To use this tutorial, you need an Azure storage account, SQL Server Management Studio (SSMS), access to an instance of SQL Server on-premises, access to an Azure virtual machine (VM) running SQL Server 2016, and an AdventureWorks2016 database. 此外,用來發出 BACKUP 或 RESTORE 命令的帳戶,應該位於擁有改變任何認證權限的 db_backupoperator 資料庫角色中。Additionally, the account used to issue the BACKUP and RESTORE commands should be in the db_backupoperator database role with alter any credential permissions.

1 - 建立預存存取原則和共用存取儲存體1 - Create stored access policy and shared access storage

在本節中,您將使用 Azure PowerShell 指令碼,在 Azure Blob 容器上建立使用預存存取原則的共用存取簽章。In this section, you will use an Azure PowerShell script to create a shared access signature on an Azure Blob container using a stored access policy.

注意

此指令碼是使用 Azure PowerShell 5.0.10586 所撰寫。This script is written using Azure PowerShell 5.0.10586.

共用存取簽章是一個 URI,會將有限的存取權限授與容器、Blob、佇列或資料表。A shared access signature is a URI that grants restricted access rights to containers, blobs, queues, or tables. 預存存取原則可對伺服器端的共用存取簽章提供一層額外的控制,包括撤銷、逾期或延伸存取權。A stored access policy provides an additional level of control over shared access signatures on the server side including revoking, expiring, or extending access. 使用這項新的增強功能時,您需要在容器上建立具有最低讀取、寫入和列出權限的原則。When using this new enhancement, you need to create a policy on a container with at least read, write, and list rights.

您可以使用 Azure PowerShell、Azure 儲存體 SDK、Azure REST API 或協力廠商公用程式,建立預存存取原則和共用存取簽章。You can create a stored access policy and a shared access signature by using Azure PowerShell, the Azure Storage SDK, the Azure REST API, or a third-party utility. 本教學課程示範如何使用 Azure PowerShell 指令碼來完成這項工作。This tutorial demonstrates how to use an Azure PowerShell script to complete this task. 此指令碼會使用資源管理員部署模型,並建立下列新的資源The script uses the Resource Manager deployment model and creates the following new resources

  • 資源群組Resource group
  • 儲存體帳戶Storage account
  • Azure Blob 容器Azure blob container
  • SAS 原則SAS policy

此指令碼一開始會宣告一些變數,來指定上述資源的名稱,以及下列必要輸入值的名稱:This script starts by declaring a number of variables to specify the names for the above resources and the names of the following required input values:

  • 用來命名其他資源物件的前置名稱A prefix name used in naming other resource objects
  • 訂閱名稱Subscription name
  • 資料中心位置Data center location

此指令碼最後會產生適當的 CREATE CREDENTIAL 陳述式,您將在 2 - 使用共用存取簽章建立 SQL Server 認證中使用此陳述式。The script completes by generating the appropriate CREATE CREDENTIAL statement that you will use in 2 - Create a SQL Server credential using a shared access signature. 系統會為您將此陳述式複製到剪貼簿,並輸出到主控台以供檢視。This statement is copied to your clipboard for you and is output to the console for you to see.

若要在容器上建立原則並產生共用存取簽章 (SAS) 金鑰,請遵循下列步驟執行:To create a policy on the container and generate a Shared Access Signature (SAS) key, follow these steps:

  1. 開啟 Window PowerShell 或 Windows PowerShell ISE (請參閱上述的版本需求)。Open Window PowerShell or Windows PowerShell ISE (see version requirements above).

  2. 編輯並執行下面的指令碼:Edit and then execute the below script:

    # Define global variables for the script  
    $prefixName = '<a prefix name>'  # used as the prefix for the name for various objects  
    $subscriptionID = '<your subscription ID>'   # the ID  of subscription name you will use  
    $locationName = '<a data center location>'  # the data center region you will use  
    $storageAccountName= $prefixName + 'storage' # the storage account name you will create or use  
    $containerName= $prefixName + 'container'  # the storage container name to which you will attach the SAS policy with its SAS token  
    $policyName = $prefixName + 'policy' # the name of the SAS policy 
    
    # Set a variable for the name of the resource group you will create or use  
    $resourceGroupName=$prefixName + 'rg'   
    
    # Add an authenticated Azure account for use in the session   
    Connect-AzAccount    
    
    # Set the tenant, subscription and environment for use in the rest of   
    Set-AzContext -SubscriptionId $subscriptionID   
    
    # Create a new resource group - comment out this line to use an existing resource group  
    New-AzResourceGroup -Name $resourceGroupName -Location $locationName   
    
    # Create a new Azure Resource Manager storage account - comment out this line to use an existing Azure Resource Manager storage account  
    New-AzStorageAccount -Name $storageAccountName -ResourceGroupName $resourceGroupName -Type Standard_RAGRS -Location $locationName   
    
    # Get the access keys for the Azure Resource Manager storage account  
    $accountKeys = Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName  
    
    # Create a new storage account context using an Azure Resource Manager storage account  
    $storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].Value
    
    # Creates a new container in blob storage  
    $container = New-AzStorageContainer -Context $storageContext -Name $containerName  
    
    # Sets up a Stored Access Policy and a Shared Access Signature for the new container  
    $policy = New-AzStorageContainerStoredAccessPolicy -Container $containerName -Policy $policyName -Context $storageContext -StartTime $(Get-Date).ToUniversalTime().AddMinutes(-5) -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(10) -Permission rwld
    
    # Gets the Shared Access Signature for the policy  
    $sas = New-AzStorageContainerSASToken -name $containerName -Policy $policyName -Context $storageContext
    Write-Host 'Shared Access Signature= '$($sas.Substring(1))''  
    
    # Sets the variables for the new container you just created
    $container = Get-AzStorageContainer -Context $storageContext -Name $containerName
    $cbc = $container.CloudBlobContainer 
    
    # 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.Uri,$sas.Substring(1)   
    $tSql | clip  
    Write-Host $tSql 
    
    # Once you're done with the tutorial, remove the resource group to clean up the resources. 
    # Remove-AzResourceGroup -Name $resourceGroupName  
    
  3. 指令碼完成之後,會將 CREATE CREDENTIAL 陳述式放在剪貼簿中以供下一節使用。After the script completes, the CREATE CREDENTIAL statement will be in your clipboard for use in the next section.

2 - 使用共用存取簽章建立 SQL Server 認證2 - Create a SQL Server credential using a shared access signature

在本節中,您將建立用來儲存安全性資訊的認證,這些資訊可供 SQL Server 用來寫入及讀取您在先前步驟中所建立的 Azure 容器內容。In this section, you will create a credential to store the security information that will be used by SQL Server to write to and read from the Azure container that you created in the previous step.

SQL Server 認證是用來儲存連接到 SQL Server 外部資源所需之驗證資訊的物件。A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. 認證會儲存儲存體容器的 URI 路徑,以及該容器的共用存取簽章金鑰值。The credential stores the URI path of the storage container and the shared access signature for this container.

若要建立 SQL Server 認證,請依照下列步驟進行:To create a SQL Server credential, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟新的查詢視窗,並連線到內部部署環境中資料引擎的 SQL Server 執行個體。Open a new query window and connect to the SQL Server instance of the database engine in your on-premises environment.

  3. 在新的查詢視窗中,貼上第 1 節的共用存取簽章及 CREATE CREDENTIAL 陳述式,然後執行該指令碼。In the new query window, paste the CREATE CREDENTIAL statement with the shared access signature from section 1 and execute that script.

    指令碼看起來類似下列程式碼。The script will look like the following code.

    /* Example:
    USE master  
    CREATE CREDENTIAL [https://msfttutorial.blob.core.windows.net/containername] 
    WITH IDENTITY='SHARED ACCESS SIGNATURE'   
    , SECRET = 'sharedaccesssignature' 
    GO */
    
    USE master  
    CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] 
      -- this name must match the container path, start with https and must not contain a forward slash at the end
    WITH IDENTITY='SHARED ACCESS SIGNATURE' 
      -- this is a mandatory string and should not be changed   
     , SECRET = 'sharedaccesssignature' 
       -- this is the shared access signature key that you obtained in section 1.   
    GO    
    
  4. 若要查看所有可用的認證,您可以在已連接到執行個體的查詢視窗中執行下列陳述式:To see all available credentials, you can run the following statement in a query window connected to your instance:

    SELECT * from sys.credentials  
    
  5. 開啟新的查詢視窗,並連線到您 Azure 虛擬機器中資料庫引擎的 SQL Server 執行個體。Open a new query window and connect to the SQL Server instance of the database engine in your Azure virtual machine.

  6. 在新的查詢視窗中,貼上第 1 節的共用存取簽章及 CREATE CREDENTIAL 陳述式,然後執行該指令碼。In the new query window, paste the CREATE CREDENTIAL statement with the shared access signature from section 1 and execute that script.

  7. 如果您想要讓任何其他 SQL Server 執行個體擁有 Azure 容器的存取權,請重複步驟 5 和 6。Repeat steps 5 and 6 for any additional SQL Server instances that you wish to have access to the Azure container.

3 - 資料庫備份至 URL3 - Database backup to URL

在本節中,您會將內部部署 SQL Server 2016 執行個體中的 AdventureWorks2016 資料庫備份到您在第 1 節建立的 Azure 容器。In this section, you will back up the AdventureWorks2016 database in your on-premises SQL Server 2016 instance to the Azure container that you created in Section 1.

注意

如果您想要將 SQL Server 2012 SP1 CU2 或更新版本的資料庫或 SQL Server 2014 資料庫備份至此 Azure 容器,您可以使用 這裡 所記載之已被取代的語法,透過 WITH CREDENTIAL 語法備份至 URL。If you wish to backup a SQL Server 2012 SP1 CU2 or later database or a SQL Server 2014 database to this Azure container, you can use the deprecated syntax documented here to backup to URL using the WITH CREDENTIAL syntax.

若要將資料庫備份至 Blob 儲存體,請遵循下列步驟:To back up a database to Blob storage, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟新的查詢視窗,並連接到您 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體。Open a new query window and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine.

  3. 將下列 Transact-SQL 指令碼複製並貼入 [查詢] 視窗中。Copy and paste the following Transact-SQL script into the query window. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,然後執行此指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1 and then execute this script.

    
    -- To permit log backups, before the full database backup, modify the database to use the full recovery model.  
    USE master;  
    ALTER DATABASE AdventureWorks2016  
       SET RECOVERY FULL;  
    
    -- Back up the full AdventureWorks2016 database to the container that you created in section 1  
    BACKUP DATABASE AdventureWorks2016   
       TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2016_onprem.bak'  
    
    
  4. 開啟物件總管,並使用您的儲存體帳戶和帳戶金鑰連接到 Azure 儲存體。Open Object Explorer and connect to Azure storage using your storage account and account key.

    1. 依序展開 [容器] 及您在第 1 節中建立的容器,然後確認上述步驟 3 中的備份出現在此容器中。Expand Containers, expand the container that you created in section 1 and verify that the backup from step 3 above appears in this container.

連線到 Azure 儲存體帳戶

4 - 從 URL 將資料庫還原至虛擬機器4 - Restore database to virtual machine from URL

在本節中,您會將 AdventureWorks2016 資料庫還原至 Azure 虛擬機器中的 SQL Server 2016 執行個體。In this section, you will restore the AdventureWorks2016 database to your SQL Server 2016 instance in your Azure virtual machine.

注意

為了簡化本教學課程,我們將使用與資料庫備份時所用的相同資料和記錄檔容器。For the purposes of simplicity in this tutorial, we are using the same container for the data and log files that we used for the database backup. 在生產環境中,您可能會使用多個容器,通常也可能會使用多個資料檔案。In a production environment, you would likely use multiple containers, and frequently multiple data files as well. 使用 SQL Server 2016 時,您也可以考慮將備份等量分割到多個 Blob,以提升備份大型資料庫時的備份效能。With SQL Server 2016, you could also consider striping your backup across multiple blobs to increase backup performance when backing up a large database.

若要將 AdventureWorks2016 資料庫從 Azure Blob 儲存體還原至 Azure 虛擬機器中的 SQL Server 2016 執行個體,請遵循下列步驟:To restore the AdventureWorks2016 database from Azure blob storage to your SQL Server 2016 instance in your Azure virtual machine, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟新的查詢視窗,並連接到您 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體。Open a new query window and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine.

  3. 將下列 Transact-SQL 指令碼複製並貼入 [查詢] 視窗中。Copy and paste the following Transact-SQL script into the query window. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,然後執行此指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1 and then execute this script.

    -- Restore AdventureWorks2016 from URL to SQL Server instance using Azure blob storage for database files  
    RESTORE DATABASE AdventureWorks2016   
       FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2016_onprem.bak'   
       WITH  
          MOVE 'AdventureWorks2016_data' to 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2016_Data.mdf'  
         ,MOVE 'AdventureWorks2016_log' to 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2016_Log.ldf'  
    --, REPLACE  
    
    
  4. 開啟物件總管,並連接到您的 Azure SQL Server 2016 執行個體。Open Object Explorer and connect to your Azure SQL Server 2016 instance.

  5. 在 [物件總管] 中,展開 [資料庫] 節點,並確認 AdventureWorks2016 資料庫已還原 (必要時請重新整理節點)In Object Explorer, expand the Databases node and verify that the AdventureWorks2016 database has been restored (refresh the node as necessary)

    1. 以滑鼠右鍵按一下 AdventureWorks2016,然後選取 [屬性]。Right-click AdventureWorks2016, and select Properties.
    2. 選取 [檔案],並確認兩個資料庫檔案的路徑是指向 Azure Blob 容器中的 Blob URL (完成時請選取 [取消])。Select Files and verify that the paths for the two database files are URLs pointing to blobs in your Azure blog container(select Cancel when done).

    Azure VM 上的 AdventureWorks 資料庫

  6. 在物件總管中,連接到 Azure 儲存體。In Object Explorer, connect to Azure storage.

    1. 依序展開 [容器] 及您在第 1 節中建立的容器,然後確認上述步驟 3 中的 AdventureWorks2016_Data.mdf 和 AdventureWorks2016_Log.ldf,與第 3 節中的備份檔案一起出現在此容器中 (必要時請重新整理節點)。Expand Containers, expand the container that you created in section 1 and verify that the AdventureWorks2016_Data.mdf and AdventureWorks2016_Log.ldf from step 3 above appears in this container, along with the backup file from section 3 (refresh the node as necessary).

Azure 上容器內中的資料檔案

5 - 使用檔案快照集備份來備份資料庫5 - Backup database using file-snapshot backup

在本節中,您將使用檔案快照集備份,在 Azure 虛擬機器中備份 AdventureWorks2016 資料庫,以透過 Azure 快照集執行幾乎即時的備份。In this section, you will back up the AdventureWorks2016 database in your Azure virtual machine using file-snapshot backup to perform a nearly instantaneous backup using Azure snapshots. 如需檔案快照集備份的詳細資訊,請參閱 Azure 中資料庫檔案的檔案快照集備份For more information on file-snapshot backups, see File-Snapshot Backups for Database Files in Azure

若要使用快照集檔案備份來備份 AdventureWorks2016 資料庫,請遵循下列步驟:To back up the AdventureWorks2016 database using file-snapshot backup, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟新的查詢視窗,並連接到您 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體。Open a new query window and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine.

  3. 將下列 Transact-SQL 指令碼複製並貼入查詢視窗中,然後執行此指令碼 (不要關閉此查詢視窗 - 您將在步驟 5 中再次執行此指令碼)。Copy, paste, and execute the following Transact-SQL script into the query window (do not close this query window - you will execute this script again in step 5. 此系統預存程序可讓您檢視組成指定資料庫之每個檔案的現有檔案快照集備份。This system stored procedure enables you to view the existing file snapshot backups for each file that comprises a specified database. 您會發現此資料庫沒有任何檔案快照集備份。You will notice that there are no file snapshot backups for this database.

    -- Verify that no file snapshot backups exist  
    SELECT * FROM sys.fn_db_backup_file_snapshots ('AdventureWorks2016');  
    
  4. 將下列 Transact-SQL 指令碼複製並貼入 [查詢] 視窗中。Copy and paste the following Transact-SQL script into the query window. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,然後執行此指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1 and then execute this script. 注意此備份速度有多快。Notice how quickly this backup occurs.

    -- Backup the AdventureWorks2016 database with FILE_SNAPSHOT  
    BACKUP DATABASE AdventureWorks2016   
       TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2016_Azure.bak'   
       WITH FILE_SNAPSHOT;    
    
  5. 確認步驟 4 中的指令碼順利執行之後,請再次執行下列指令碼。After verifying that the script in step 4 executed successfully, execute the following script again. 請注意,步驟 4 中的檔案快照集備份作業會產生資料和記錄檔的檔案快照集。Notice that the file-snapshot backup operation in step 4 generated file-snapshots of both the data and log file.

    -- Verify that two file-snapshot backups exist  
    SELECT * FROM sys.fn_db_backup_file_snapshots ('AdventureWorks2016');  
    
    

    fn_db_backup_file_snapshots 顯示快照集的結果

  6. 在 [物件總管] 中,於您 Azure 虛擬機器的 SQL Server 2016 執行個體中,展開 [資料庫] 節點,並確認 AdventureWorks2016 資料庫已還原至此執行個體 (必要時請重新整理節點)。In Object Explorer, in your SQL Server 2016 instance in your Azure virtual machine, expand the Databases node and verify that the AdventureWorks2016 database has been restored to this instance (refresh the node as necessary).

  7. 在物件總管中,連接到 Azure 儲存體。In Object Explorer, connect to Azure storage.

  8. 依序展開 [容器] 及您在第 1 節中建立的容器,然後確認上述步驟 4 中的 AdventureWorks2016_Azure.bak,與第 3 節中的備份檔案及第 4 節中的資料庫檔案一起出現在此容器中 (必要時請重新整理節點)。Expand Containers, expand the container that you created in section 1 and verify that the AdventureWorks2016_Azure.bak from step 4 above appears in this container, along with the backup file from section 3 and the database files from section 4 (refresh the node as necessary).

    Azure 上的快照集備份

6 - 使用檔案快照集備份來產生活動和備份記錄6 - Generate activity and backup log using file-snapshot backup

在本節中,您將使用檔案快照集備份,在 AdventureWorks2016 資料庫中產生活動並定期建立交易記錄備份。In this section, you will generate activity in the AdventureWorks2016 database and periodically create transaction log backups using file-snapshot backups. 如需如何使用檔案快照集備份的詳細資訊,請參閱 Azure 中資料庫檔案的檔案快照集備份For more information on using file snapshot backups, see File-Snapshot Backups for Database Files in Azure.

若要使用檔案快照集備份,在 AdventureWorks2016 資料庫中產生活動並定期建立交易記錄備份,請遵循下列步驟:To generate activity in the AdventureWorks2016 database and periodically create transaction log backups using file-snapshot backups, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟兩個新的查詢視窗,並將這兩個視窗都連接到 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體。Open two new query windows and connect each to the SQL Server 2016 instance of the database engine in your Azure virtual machine.

  3. 將下列 Transact-SQL 指令碼複製並貼入其中一個查詢視窗中,然後執行此指令碼。Copy, paste, and execute the following Transact-SQL script into one of the query windows. 請注意,在我們新增步驟 4 的新資料列之前,Production.Location 資料表有 14 個資料列。Notice that the Production.Location table has 14 rows before we add new rows in step 4.

    -- Verify row count at start  
    SELECT COUNT (*) from AdventureWorks2016.Production.Location;    
    
  4. 將下列兩個 Transact-SQL 指令碼複製並分別貼入這兩個查詢視窗中。Copy and paste the following two Transact-SQL scripts into the two separate query windows. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,然後分別在這兩個查詢視窗中同時執行這些指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1 and then execute these scripts simultaneously in separate query windows. 這些指令碼需要大約七分鐘時間才能完成。These scripts will take about seven minutes to complete.

    -- Insert 30,000 new rows into the Production.Location table in the AdventureWorks2014 database in batches of 75  
    DECLARE @count INT=1, @inner INT;  
    WHILE @count < 400  
       BEGIN  
          BEGIN TRAN;  
             SET @inner =1;  
                WHILE @inner <= 75  
                   BEGIN;  
                      INSERT INTO AdventureWorks2016.Production.Location    
                         (Name, CostRate, Availability, ModifiedDate)   
                            VALUES (NEWID(), .5, 5.2, GETDATE());  
                      SET @inner = @inner + 1;  
                   END;  
          COMMIT;  
       WAITFOR DELAY '00:00:01';   
       SET @count = @count + 1;  
       END;  
    SELECT COUNT (*) from AdventureWorks2014.Production.Location;    
    
    --take 7 transaction log backups with FILE_SNAPSHOT, one per minute, and include the row count and the execution time in the backup file name   
    DECLARE @count INT=1, @device NVARCHAR(120), @numrows INT;  
    WHILE @count <= 7  
       BEGIN  
             SET @numrows = (SELECT COUNT (*) FROM AdventureWorks2016.Production.Location);  
             SET @device = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/tutorial-' + CONVERT (varchar(10),@numrows) + '-' + FORMAT(GETDATE(), 'yyyyMMddHHmmss') + '.bak';  
             BACKUP LOG AdventureWorks2016 TO URL = @device WITH FILE_SNAPSHOT;  
             SELECT * from sys.fn_db_backup_file_snapshots ('AdventureWorks2016');  
          WAITFOR DELAY '00:1:00';   
             SET @count = @count + 1;  
       END;  
    
  5. 檢閱第一個指令碼的輸出,並注意現在最後的資料列計數是 29,939。Review the output of the first script and notice that final row count is now 29,939.

    隨即顯示 29,939 個資料列計數

  6. 檢閱第二個指令碼的輸出,並注意,每次 BACKUP LOG 陳述式執行時,系統都會建立兩個新的檔案快照集:一個記錄檔檔案快照集和一個資料檔檔案快照集,因此每一個資料庫檔案總共會有兩個檔案快照集。Review the output of the second script and notice that each time the BACKUP LOG statement is executed that two new file snapshots are created, one file snapshot of the log file and one file snapshot of the data file - for a total of two file snapshots for each database file. 第二個指令碼完成之後,請注意,現在共有 16 個檔案快照集,每一個資料庫檔案總共有 8 個:一個來自 BACKUP DATABASE 陳述式而另一個是每次執行 BACKUP LOG 陳述式時產生。After the second script completes, notice that there are now a total of 16 file snapshots, 8 for each database file - one from the BACKUP DATABASE statement and one for each execution of the BACKUP LOG statement.

備份快照集結果

  1. 在物件總管中,連接到 Azure 儲存體。In Object Explorer, connect to Azure storage.

  2. 展開 [容器],並展開您在第 1 節所建立的容器,並確認其中顯示七個新備份記錄檔與先前章節的資料檔案 (您可視需要重新整理)。Expand Containers, expand the container that you created in section 1 and verify that seven new backup files appear, along with the data files from the previous sections (refresh the node as needed).

    Azure 容器中的多個快照集

7 - 將資料庫還原至某個時間點7 - Restore a database to a point in time

在本節中,您會將 AdventureWorks2016 資料庫還原至兩個交易記錄備份之間的某個時間點。In this section, you will restore the AdventureWorks2016 database to a point in time between two of the transaction log backups.

使用傳統備份時,為了完成時間點還原,您必須使用完整資料庫備份 (可能是差異備份),以及您想要還原的時間點之前及剛好超過此時間點的所有交易記錄檔。With traditional backups, to accomplish point in time restore, you would need to use the full database backup, perhaps a differential backup, and all of the transaction log files up to and just past the point in time to which you wish to restore. 使用檔案快照集備份時,您只需要兩個相鄰的記錄備份檔案,這兩個檔案會提供您想要還原的時間點之前時間範圍的目標張貼內容。With file-snapshot backups, you only need the two adjacent log backup files that provide the goal posts framing the time to which you wish to restore. 您只需要兩個記錄檔案快照集備份組,因為每個記錄備份都會建立每個資料庫檔案的檔案快照集 (每個資料檔案和記錄檔)。You only need two log file snapshot backup sets because each log backup creates a file snapshot of each database file (each data file and the log file).

若要將資料庫從檔案快照集備份組還原至指定的時間點,請遵循下列步驟:To restore a database to a specified point in time from file snapshot backup sets, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟新的查詢視窗,並連接到您 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體。Open a new query window and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine.

  3. 將下列 Transact-SQL 指令碼複製並貼入查詢視窗中,然後執行此指令碼。Copy, paste, and execute the following Transact-SQL script into the query window. 確認 Production.Location 資料表有 29,939 個資料列,再將它還原至步驟 5 中有較少資料列的時間點。Verify that the Production.Location table has 29,939 rows before we restore it to a point in time when there are fewer rows in step 5.

    -- Verify row count at start  
    SELECT COUNT (*) from AdventureWorks2016.Production.Location   
    

    隨即顯示 29,939 個資料列計數

  4. 將下列 Transact-SQL 指令碼複製並貼入 [查詢] 視窗中。Copy and paste the following Transact-SQL script into the query window. 選取兩個相鄰的記錄備份檔案,並將檔案名稱轉換成此指令碼所需的日期和時間。Select two adjacent log backup files and convert the file name into the date and time you will need for this script. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,提供第一個和第二個備份檔案名稱,提供 "June 26, 2018 01:48 PM" 格式的 STOPAT 時間,然後執行此指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1, provide the first and second backup file names, provide the STOPAT time in the format of "June 26, 2018 01:48 PM" and then execute this script. 此指令碼需要幾分鐘才能完成This script will take a few minutes to complete

    -- restore and recover to a point in time between the times of two transaction log backups, and then verify the row count  
    ALTER DATABASE AdventureWorks2016 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
    RESTORE DATABASE AdventureWorks2016   
       FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/<firstbackupfile>.bak'   
       WITH NORECOVERY,REPLACE;  
    RESTORE LOG AdventureWorks2016   
       FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/<secondbackupfile>.bak'    
       WITH RECOVERY, STOPAT = 'June 26, 2018 01:48 PM';  
    ALTER DATABASE AdventureWorks2016 set multi_user;  
    -- get new count  
    SELECT COUNT (*) FROM AdventureWorks2016.Production.Location ;
    
  5. 檢閱輸出。Review the output. 請注意,還原後的資料列計數為 18,389,也就是介於記錄備份 5 和 6 之間的資料列計數 (您的資料列計數會有所不同)。Notice that after the restore the row count is 18,389, which is a row count number between log backup 5 and 6 (your row count will vary).

    18-thousand-rows.JPG

8 - 從記錄備份還原為新的資料庫8 - Restore as new database from log backup

在本節中,您會從檔案快照集交易記錄備份將 AdventureWorks2016 資料庫還原為新的資料庫。In this section, you will restore the AdventureWorks2016 database as a new database from a file-snapshot transaction log backup.

在此情況下,您會基於商務分析和報告在不同的虛擬機器上執行還原至 SQL Server 執行個體。In this scenario, you are performing a restore to a SQL Server instance on a different virtual machine for the purposes of business analysis and reporting. 還原至不同虛擬機器上的不同執行個體,會基於此目的,將工作負載卸載至專用虛擬機器並進行大小調整,方法是從交易式系統中移除其資源需求。Restoring to a different instance on a different virtual machine offloads the workload to a virtual machine dedicated and sized for this purpose, removing its resource requirements from the transactional system.

使用檔案快照集備份從交易記錄備份進行還原十分地快速,而且比使用傳統串流備份還要快。Restore from a transaction log backup with file-snapshot backup is very quick, substantially quicker than with traditional streaming backups. 運用傳統串流備份,您必須使用完整資料庫備份 (可能是差異備份),以及部分或所有交易記錄備份 (或新的完整資料庫備份)。With traditional streaming backups, you would need to use the full database backup, perhaps a differential backup, and some or all of the transaction log backups (or a new full database backup). 不過,使用檔案快照集記錄備份,您只需要最新的記錄備份 (或任何其他記錄備份,或任兩個相鄰記錄備份以將時間點還原至兩個記錄備份時間之間的時間點)。However, with file-snapshot log backups, you only need the most recent log backup (or any other log backup or any two adjacent log backups for point in time restore to a point between two log backup times). 若要更為清楚,您只需要一個記錄檔案快照集備份組,因為每個檔案快照集記錄備份都會建立每個資料庫檔案的檔案快照集 (每個資料檔案和記錄檔)。To be clear, you only need one log file-snapshot backup set because each file-snapshot log backup creates a file snapshot of each database file (each data file and the log file).

若要使用檔案快照集備份從交易記錄備份將資料庫還原到新的資料庫,請執行下列步驟︰To restore a database to a new database from a transaction log backup using file snapshot backup, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.

  2. 開啟新的查詢視窗,並連接到 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體。Open a new query window and connect to the SQL Server 2016 instance of the database engine in an Azure virtual machine.

    注意

    如果這是與您之前用於先前章節不同的 Azure 虛擬機器,請確定您遵循 2 - 使用共用存取簽章建立 SQL Server 認證中的步驟進行。If this is a different Azure virtual machine than you have been using for the previous sections, make sure you have followed the steps in 2 - Create a SQL Server credential using a shared access signature. 若您想要還原到不同容器,請遵循 1 - 建立預存存取原則和共用存取儲存體中的步驟。If you wish to restore to a different container, follow the steps in 1 - Create stored access policy and shared access storage for the new container.

  3. 將下列 Transact-SQL 指令碼複製並貼入 [查詢] 視窗中。Copy and paste the following Transact-SQL script into the query window. 選取您想要使用的記錄備份檔案。Select the log backup file you wish to use. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,並提供記錄備份檔案名稱,然後執行此指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1, provide the log backup file name and then execute this script.

    -- restore as a new database from a transaction log backup file  
    RESTORE DATABASE AdventureWorks2016_EOM   
        FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/<logbackupfile.bak'    
        WITH MOVE 'AdventureWorks2016_data' to 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2014_EOM_Data.mdf'  
       , MOVE 'AdventureWorks2016_log' to 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/AdventureWorks2014_EOM_Log.ldf'  
       , RECOVERY  
    --, REPLACE   
    
  4. 請檢閱輸出,確認還原成功。Review the output to verify the restore was successful.

  5. 在物件總管中,連接到 Azure 儲存體。In Object Explorer, connect to Azure storage.

  6. 展開 [容器],並展開您在第 1 節所建立的容器 (必要時,請重新整理),然後確認新資料和記錄檔出現在容器以及先前章節的 Blob 中。Expand Containers, expand the container that you created in section 1 (refresh if necessary) and verify that the new data and log files appear in the container, along with the blobs from the previous sections.

    Azure 容器,顯示新的資料庫資料和記錄檔

9 - 管理備份組和檔案快照集備份9 - Manage backup sets and file-snapshot backups

在本節中,您將會使用 sp_delete_backup (Transact-SQL) 系統預存程序來刪除備份組。In this section, you will delete a backup set using the sp_delete_backup (Transact-SQL) system stored procedure. 這個系統預存程序會刪除備份檔案以及與這個備份組相關聯的每個資料庫檔案上的檔案快照集。This system stored procedure deletes the backup file and the file snapshot on each database file associated with this backup set.

注意

如果您只是刪除 Azure Blob 容器中的備份檔案來嘗試刪除備份組,則只會刪除備份檔案本身,相關聯的檔案快照集將會予以保留。If you attempt to delete a backup set by simply deleting the backup file from the Azure Blob container, you will only delete the backup file itself - the associated file snapshots will remain. 如果您發現自己處於這種情況,請使用 sys.fn_db_backup_file_snapshots (Transact-SQL) 系統函數來識別孤立檔案快照集 URL,以及使用 sp_delete_backup_file_snapshot (Transact-SQL) 系統預存程序來刪除每個孤立檔案快照集。If you find yourself in this scenario, use the sys.fn_db_backup_file_snapshots (Transact-SQL) system function to identify the URL of the orphaned file snapshots and use the sp_delete_backup_file_snapshot (Transact-SQL) system stored procedure to delete each orphaned file snapshot. 如需詳細資訊,請參閱 Azure 中資料庫檔案的檔案快照集備份For more information, see File-Snapshot Backups for Database Files in Azure.

若要刪除檔案快照集備份組,請遵循下列步驟:To delete a file-snapshot backup set, follow these steps:

  1. 連接到 SQL Server Management Studio。Connect to SQL Server Management Studio.
  2. 開啟新的查詢視窗,並連接到您的 Azure 虛擬機器中資料庫引擎的 SQL Server 2016 執行個體 (或具有這個容器之讀取和寫入權限的任何 SQL Server 2016 執行個體)。Open a new query window and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine (or to any SQL Server 2016 instance with permissions to read and write on this container).
  3. 將下列 Transact-SQL 指令碼複製並貼入 [查詢] 視窗中。Copy and paste the following Transact-SQL script into the query window. 選取您想要刪除的記錄備份與其相關聯的檔案快照集。Select the log backup you wish to delete along with its associated file snapshots. 適當地修改儲存體帳戶名稱以及您在第 1 節中所指定容器的 URL,並提供記錄備份檔案名稱,然後執行此指令碼。Modify the URL appropriately for your storage account name and the container that you specified in section 1, provide the log backup file name and then execute this script.
sys.sp_delete_backup 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/tutorial-21764-20181003205236.bak';  
  1. 在物件總管中,連接到 Azure 儲存體。In Object Explorer, connect to Azure storage.

  2. 依序展開 [容器] 以及您在第 1 節中建立的容器,然後確認步驟 3 中所使用的備份檔案不再出現於這個容器中 (必要時,請重新整理節點)。Expand Containers, expand the container that you created in section 1 and verify that the backup file you used in step 3 no longer appears in this container (refresh the node as necessary).

    Azure 容器,顯示刪除記錄備份 Blob

  3. 將下列 Transact-SQL 指令碼複製並貼入查詢視窗中,然後執行此指令碼,確認已刪除兩個檔案快照集。Copy, paste, and execute the following Transact-SQL script into the query window to verify that two file snapshots have been deleted.

    -- verify that two file snapshots have been removed  
    SELECT * from sys.fn_db_backup_file_snapshots ('AdventureWorks2016');   
    

    顯示 2 個檔案快照集已刪除的 [結果] 窗格

10 - 移除資源10 - Remove resources

當您完成本教學課程後,若要節省資源,請務必刪除在本教學課程中建立的資源群組。Once you're done with this tutorial, and to conserve resources, be sure to delete the resource group created in this tutorial.

若要刪除資源群組,請執行下列 PowerShell 程式碼:To delete the resource group, run the following powershell code:

# Define global variables for the script  
$prefixName = '<prefix name>'  # should be the same as the beginning of the tutorial

# Set a variable for the name of the resource group you will create or use  
$resourceGroupName=$prefixName + 'rg'   

# Adds an authenticated Azure account for use in the session   
Connect-AzAccount    

# Set the tenant, subscription and environment for use in the rest of   
Set-AzContext -SubscriptionId $subscriptionID    
  
# Remove the resource group
Remove-AzResourceGroup -Name $resourceGroupName   

另請參閱See Also

Microsoft Azure 中的 SQL Server 資料檔案SQL Server Data Files in Microsoft Azure
Azure 中資料庫檔案的檔案快照集備份File-Snapshot Backups for Database Files in Azure
SQL Server 備份至 URL 共用存取簽章,第 1 部分:了解 SAS 模型SQL Server Backup to URL Shared Access Signatures, Part 1: Understanding the SAS Model
Create Container (建立容器)Create Container
Set Container ACL (設定容器 ACL)Set Container ACL
取得容器 ACL 認證 (資料庫引擎)Get Container ACL Credentials (Database Engine)
CREATE CREDENTIAL (Transact-SQL)CREATE CREDENTIAL (Transact-SQL)
sys.credentials (Transact-SQL)sys.credentials (Transact-SQL)
sp_delete_backup (Transact-SQL)sp_delete_backup (Transact-SQL)
sys.fn_db_backup_file_snapshots (Transact-SQL)sys.fn_db_backup_file_snapshots (Transact-SQL)
sp_delete_backup_file_snapshot (Transact-SQL) Azure 中的資料庫檔案檔案快照集備份sp_delete_backup_file_snapshot (Transact-SQL) File-Snapshot Backups for Database Files in Azure