Share via

SQL Server 2016 新功能搶先看 - 資料庫檔案快照備份及備份至 URL

  • Article

SQL Server 2016 搶先看第八波，這次要帶大家認識「資料庫檔案快照備份及備份至 URL」！

回顧系列專文：　

  1. SQL Server 2016 新功能搶先看 - Stretch Database / Managed Backup (內含 Demo 畫面)
  2. SQL Server 2016 新功能搶先看 - Live Query Statistics / JSON
  3. SQL Server 2016 新功能搶先看 - 動態資料遮罩 / 多重 tempdb 資料檔
  4. SQL Server 2016 新功能搶先看 - Stretch Database 詳解
  5. SQL Server 2016 新功能搶先看 - Always Encrypted
  6. SQL Server 2016 新功能搶先看 - Temporal Tables
  7. SQL Server 2016 新功能搶先看 - 受管理的備份

本文將帶大家了解 SQL Server 2016 資料庫檔案快照備份及備份至 URL 以下功能：

 1. 資料庫檔案快照備份

2. 備份至 URL

3. 結論

資料庫檔案快照備份

前一篇我們討論了 SQL Server 2016 增強受管理的備份（Managed Backup），讓 DBA 得以降低管理資料庫備份的工作負擔，讓資料庫引擎依照工作負載的狀況，自動把資料庫備份到Microsoft Azure儲存體服務的 Blob ，並且增加許多更細緻的設定，例如備份頻率及診斷備份狀況等功能。

這一篇繼續來看 SQL Server 2016 還有哪些和備份混合雲相關的功能，其中「資料庫檔案快照備份（Snapshot Backups for Database Files in Azure）」，用來把存放在儲存體服務中的資料庫/交易記錄檔（MDF、NDF和 LDF ）以建立快照的方式進行備份，不論是備份或還原資料庫檔案，幾乎都是瞬間就能完成。

所謂的快照備份是由資料庫檔案加上備份媒體及快照檔案所組成，其中備份媒體可以像過去那樣選擇存放在磁碟、磁帶或 URL ，若您為建置異地備份的解決方案，那麼備份到 URL 會是較為建議的做法，而快照的部分會存在相同與資料庫檔案所在的容器之中。

資料庫檔案快照備份的快照檔案，不會顯示在儲存體中，因此無法手動將之刪除，另外當您刪除備份媒體或刪除資料庫，已經建立的快照並不會被刪除，您必須自行以系統預存程序來手動刪除。有關管理快照檔案及資料庫快照備份，本文後面會有詳細的介紹。

準備工作

使用快照備份的先決條件是該資料庫的資料庫檔案（含交易記錄檔）必須放在Azure儲存體，否則執行快照備份會發生如下圖的錯誤。

本文假設您已經啟用 Azure 訂用帳戶，並且熟悉如何以 Azure 管理入口網站或 Azure PowerShell 建立儲存體帳戶和容器。本文以下列 PowerShell 指令碼示範在 Azure 位於東亞的資料中心建立名稱為 sqldbfiles 的儲存體帳戶，接著在該儲存體帳戶中建立3個容器用來存放資料檔、交易記錄檔以及備份媒體，名稱分別為 datafiles、logfiles 和 bak ，最後產生建立認證所需的共用存取簽章 Token 之 T-SQL 指令碼。

#資料中心位置

$location = 'East Asia'

#儲存體帳戶名稱

$stracct = 'sqldbfiles'

#容器名稱

$containers = 'datafiles','logfiles','bak'

#訂用帳戶名稱

$subscriptionName='MSDN - VS Premium'

 

#建立儲存體帳戶

New-AzureStorageAccount -StorageAccountName $stracct -Location $location

 

#建立儲存體帳戶的內容(Context)

$context = New-AzureStorageContext -StorageAccountName $stracct -StorageAccountKey (Get-AzureStorageKey -StorageAccountName $stracct).Primary

 

foreach ($c in $containers)

{

    #建立容器

    New-AzureStorageContainer -Name $c -Context $context

 

    #建立SAS Token

    Select-AzureSubscription -SubscriptionName $subscriptionName

    $accountKeys = Get-AzureStorageKey -StorageAccountName $stracct

    $storageContext = New-AzureStorageContext -StorageAccountName $stracct -StorageAccountKey $accountKeys.Primary

    $container = Get-AzureStorageContainer -Context $storageContext -Name $c

    $cbc = $container.CloudBlobContainer

    $permissions = $cbc.GetPermissions()

    $policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy'

    $policy.SharedAccessStartTime = $(Get-Date).ToUniversalTime().AddMinutes(-5)

    $policy.SharedAccessExpiryTime = $(Get-Date).ToUniversalTime().AddYears(5)

    $policy.Permissions = "Read,Write,List,Delete"

    $permissions.SharedAccessPolicies.Add($c+'_Policy', $policy)

    $cbc.SetPermissions($permissions);

    $policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy'

    $sas = $cbc.GetSharedAccessSignature($policy, $c+'_Policy')

    Write-Host 'Shared Access Signature= '$($sas.Substring(1))''

    Write-Host 'Credential T-SQL'

    $TSQL = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.Uri,$sas.Substring(1)

    Write-Host $TSQL

}

執行結果如下圖所示，請將紅色方框處的 T-SQL 複製下來。

開啟 SQL Server Management Studio 於查詢視窗貼上並執行 T-SQL 指令碼，就可以建立存取儲存體帳戶的認證。

準備好存放資料庫檔案的儲存體帳戶和容器之後並且建立認證後，接著建立資料庫並將資料庫檔案路徑指向容器的 URL 位置。

上述步驟也可以利用 T-SQL 來完成，其指令碼如下。

CREATE DATABASE SnapshotBakDB

 ON  PRIMARY

(

 NAME = N'SnapshotBakDB'

, FILENAME = N'https://sqldbfiles.blob.core.windows.net/datafiles/SnapshotBakDB.mdf'

)

 LOG ON

(

 NAME = N'SnapshotBakDB_log'

, FILENAME = N'https://sqldbfiles.blob.core.windows.net/logfiles/SnapshotBakDB_log.ldf')

使用資料庫檔案快照備份

使用資料庫檔案快照備份，依然可以選擇將資料庫備份（或交易記錄備份）的檔案放在磁碟或 URL，建議將備份媒體放在 URL 以確保備份媒體的可用性。使用快照備份的方式很簡單，只需在 BACKUP 敘述後搭配 WITH FILE_SNAP 選項。

下列 T-SQL 指令碼示範使用資料庫檔案快照備份進行資料庫完整備份。

--備份至磁碟

BACKUP DATABASE SnapshotBakDB

TO DISK = 'SSBDB.bak'

WITH FILE_SNAPSHOT

 

--備份至URL

BACKUP DATABASE SnapshotBakDB

TO URL = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB.bak'

WITH FILE_SNAPSHOT

下列T-SQL指令碼示範使用資料庫檔案快照進行交易記錄備份。

--  備份至磁碟

DECLARE @Log_FileName AS VARCHAR (300) = 'SSBDB_Log_' +REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.trn'

 

BACKUP LOG SnapshotBakDB

TO DISK = @Log_FileName

 

GO

 

--備份至URL

DECLARE @Log_FileName AS VARCHAR (300) = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB_Log_' +REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.trn'

 

BACKUP LOG SnapshotBakDB

TO URL = @Log_FileName

還原資料庫檔案快照備份

不論是從磁碟或 URL 還原資料庫和交易記錄時，都跟未使用資料庫檔案快照備份相同，不需額外做任何設定或調整。

下列的 T-SQL 指令碼示範從URL還原完整備份及交易記錄備份。

USE master

GO

 

RESTORE DATABASE SnapshotBakDB

FROM URL = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB.bak'

WITH NORECOVERY,REPLACE

 

RESTORE LOG SnapshotBakDB

FROM URL = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB_Log_2015_07_31_16_46_11.trn'

WITH RECOVERY

下列 T-SQL 指令碼示範還原資料庫至 2015-08-04 14:16:36.813 這個時間點。

USE master

GO

 

RESTORE DATABASE SnapshotBakDB

FROM URL = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB.bak'

WITH NORECOVERY,REPLACE

 

RESTORE LOG SnapshotBakDB

FROM URL = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB_Log_2015_08_04_14_17_21.trn'

WITH RECOVERY,STOPAT = '2015-08-04 14:16:36.813'

 

GO

檢視資料庫快照備份

在【說明】一節曾經提到，資料庫快照備份會被存放在相關聯的資料庫檔案所在的容器之中，若您從 Azure 管理入口網站並無法看到這些快照檔案，這樣做的好處可以避免快照檔案被不小心刪除，若要查看這些檔案可以使用 sys.fn_db_backup_file_snapshots 系統函數。

下列 T-SQL 指令碼示範使用 sys.fn_db_backup_file_snapshots 系統函數查詢 SnapshotBakDB 資料庫的資料庫快照備份檔案，兩段 T-SQL 的查詢結果相同。

USE SnapshotBakDB

GO

 

select * from sys.fn_db_backup_file_snapshots (null) ;

GO

 

USE master

GO

 

select * from sys.fn_db_backup_file_snapshots ('SnapshotBakDB') ;

GO

由下圖可見快照備份的時間點及相關的快照檔案的URL。

刪除資料庫快照備份組及個別資料庫快照

由於 Azure 儲存體容器之中看不到快照備份關聯的檔案，一旦您將資料庫備份媒體從容器中刪除，相關聯的快照檔案並不會跟著被刪除，因此若要刪除這些快照檔案只能使用 sys.sp_delete_backup_file_snapshot 系統預存程序來逐一刪除個別的資料庫快照檔案。

下列 T-SQL 指令碼示範使用 sys.sp_delete_backup_file_snapshot 刪除特定的快照檔案。

EXEC sys.sp_delete_backup_file_snapshot

@db_name = N'SnapshotBakDB',

@snapshot_url = N'https://sqldbfiles.blob.core.windows.net/datafiles/SnapshotBakDB.mdf?snapshot=2015-07-31T07:17:17.2993550Z'

當您使用 WITH FILE_SNAPSHOT 選項進行資料庫檔案快照備份後，若要刪除備份媒體應該使用 sys.sp_delete_backup 系統預存程序，該預存程序會在刪除備份媒體時同時刪除關聯的快照檔案。

下列 T-SQL 指令碼示範使用 sys.sp_delete_backup 系統預存程序刪除完整備份媒體及其關聯的快照檔案。

EXEC sys.sp_delete_backup

@backup_url  = 'https://sqldbfiles.blob.core.windows.net/bak/SSBDB.bak',

@database_name = N'SnapshotBakDB'

 

GO

備份至 URL

自從 SQL Server 2012 SP1 CU2 以來 SQL Server 提供將資料備份位置直接指向 Azure 儲存體，如此一來可以很輕易地實現異地備份，並直接享有儲存體所帶來的低成本、低管理負擔以及高彈性及可用性等優點。

這樣的設計還是有些可以做得更好的地方，像是安全性的部分，在 SQL Server 2012-2014 必須使用存取金鑰來建立存取儲存體帳戶的認證，擁有金鑰就可以完全控制儲存體帳戶，無法有更多細緻的權限設定（如讀取、寫入、列舉或刪除）。再者過去備份至 URL，備份媒體是以分頁（Page）Blob 格式來儲存，其成本相較於區塊（Block）Blob 來的高，並且有備份大小上限為 1TB 等限制。

有鑑於此，SQL Server 2016強化備份至URL的功能，與之前版本的差異如下表所示：

項目        版本

SQL Server 2012-2014

SQL Server 2016

存取方式

存取金鑰

共用存取簽章（Shared Access Signature ，SAS）

儲存方式

分頁（Page）Blob

區塊（Block）Blob

備份大小

最高1TB

最高12.8 TB

備份至多個Blob

不支援

支援

準備工作

備份至 URL 依然可以使用舊版搭配 WITH CREDENTIAL 選項來備份至儲存體，若您想要使用 SQL Server 2016 備份至 URL 的新功能，則必須先建立共同存取簽章並建立相對應的認證，其建立方式在此就不贅述，請參考【資料庫檔案快照備份】的【準備工作】一節。

備份至 URL

使用 SQL Server 2016 備份至 URL 有兩種方式，一個是透過 SQL Server Management Studio 的圖形化介面，另一種方式是以 T-SQL 方式執行 BACKUP 敘述。在這個版本在圖形化介面允許備份至 URL 時不選擇 SQL 認證，當您未選擇 SQL 認證時，在備份至 URL 時就會將備份媒體以區塊 Blob 方式儲存。

以 T-SQL 方式備份至區塊Blob也不需使用 WITH CREDENTIAL 選項，只需指定儲存體容器的URL及備份媒體名稱，例如下列的 T-SQL 指令碼。

BACKUP DATABASE DB2

TO  URL = N'https://sqldbfiles.blob.core.windows.net/bak/DB2_backup_2015_08_05_171307.bak'

下列的 T-SQL 指令碼是用來實驗在 SQL Server 2016 備份至 URL 時搭配 WITH CREDENTIAL 選項的差異。

USE master

GO

 

BACKUP DATABASE DB2

TO URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB2.bak'

WITH CREDENTIAL = 'mycredential'

使用WITH CREDENTIAL選項，其儲存媒體的Blob類型會是分頁Blob，而不是區塊Blob（如下圖所示）。

在備份效率方面，SQL Server 2016 備份至 URL 開始支援將備份媒體拆成多個，此時備份作業會以多執行緒方式進行，藉此降低備份資料庫所需的時間，尤其資料庫越大時越能體會出這種備份方式在速度上的提升。

下列 T-SQL 指令碼示範對 DB3 資料庫進行完整備份，並將備份媒體拆成 3 份存放至所指定的儲存體容器。

USE master

GO

 

BACKUP DATABASE DB3

TO URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB3-1.bak'

,URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB3-2.bak'

,URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB3-3.bak'

WITH FORMAT

備份成功後您可以在 Azure 管理入口網站查看備份媒體的 Blob 類型，由下圖可見確實使用這種方式來將資料庫備份至 URL，其 Blob 屬性確實為區塊 Blob。

從 URL 還原

和過去在地端一樣，當您將備份媒體切割成數個，還原資料庫時必須同時使用當初備份時所有的備份媒體，只要缺少一個就會造成還原資料庫失敗並發生如下圖的錯誤訊息。

接續上一節的範例，正確還原資料庫的做法應該是像下列 T-SQL 。

USE master

GO

 

RESTORE DATABASE DB3

FROM URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB3-1.bak'

,URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB3-2.bak'

,URL = 'https://sqldbfiles.blob.core.windows.net/bak/DB3-3.bak'

WITH RECOVERY,REPLACE

 

GO

結論

SQL Server 2016 對於實現備份混合雲的應用做了許多改良，包含之前文章提到的受管理的備份，增加了自訂備份週期及備份媒體加密等功能。另外，備份至 URL 做了部分改良，包含改以共用存取簽章取代儲存體存取金鑰、支援多個備份媒體和改用區塊 Blob 格式儲存。最後，針對資料庫檔案放在 Azure 儲存體的情境，新增了資料庫檔案快照備份功能，藉以提升資料庫備份的效率。綜合上述，您可以依照企業所需，任意搭配這些備份至 Azure 儲存體的技術，實作出符合需求的異地備份解決方案。

Comments

  • Anonymous
    September 24, 2015
    The comment has been removed
  • Anonymous
    September 29, 2015
    The comment has been removed
  • Anonymous
    October 04, 2015
    SQL Server 2016 搶先看系列第十彈 ！

    本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server