[Azure][SQL]使用 Azure SQL Database 還原資料庫時候的小技巧

2023-04-19

Azure SQL Database 支援可以將資料庫還原到 30 天內指定的時間點，再還原過程和處理資料庫，可以運用一些小技巧來方便資料回復

最近有朋友在測試 Azure SQL Database 的時候，不小心將部分資料給異動到，但不知道要怎麼來回復當時的資料，因此在協助他回復的過程中，順手整理一下，這樣下次就可以請他自己來做處理了。

為了要方便展示，所以我先建立一個叫做 Source 的 Azure SQL Database，基本上就是 AdventureWorks 的範例資料庫，在這個資料庫中我也模擬資料異動，因此先用 SQL 故意異動一些資料。

因此我們先記錄下來資料的異動時間，等一下還原的時候要從這個異動時間點來回推。接著我們回到 Azure Portal 的網站上，在原本的資料庫上面，選擇「還原」的功能

前面我們知道是下午 2:02 的時候更動資料庫，因此還原資料庫的時候我們要選那個時間點之前，要注意在 Azure 上面我們要給他的是 UTC 的時間，因此這裡我們設定我們要還原到 06:00 的時間 ( 也就是下午 2:00 )，而 Azure Portal 會隨我們設定的時間，自動設定要還原的資料庫名稱。

基本上大部分人在處理的時候也都是這樣進行，但是這裡我們就很時常遇到一個問題，就是在還原的過程中雖然預設還原的資料庫和原本的資料庫是相同的等級，但是可能您原本的資料庫有許多的異動紀錄，造成資料庫再還原的過程中要等待非常的久，甚至會還原到失敗的狀況。

因此在這裡我們會先建議在還原的過程中，可以先適當的放大 DTU 的等級，像我之前有個資料庫還原的時候沒有注意到，結果等了快一個小時最後還失敗，那就非常的嘔了。因此這裡我通常會放大到 S3，等還原結束後再來調低到較低的等級來做使用。

在還原好之後，我們會有一個 Source 的資料庫 , 和一個名稱為 Source_2023-04-18T06-00Z 的歷史資料庫。雖然我們知道後面這個資料庫是修改資料前的資料庫，但我們要怎麼知道到底在實際的環境中，有哪個資料表的資料有被異動呢 ? 這裡我用一段簡單的 SQL 來做處理

透過下面這段 SQL 語法，我們最主要是透過 INFORMATION_SCHEMA.TABLES 來取得有哪些資料表，因為我們是使用 Azure SQL Database，所以這裡我們就不能使用 sp_MSforeachtable 的方式來做處理，另外我們搭配 CHECKSUM_AGG 和 BINARY_CHECKSUM 這兩個函數，來針對每個資料表做個計算，這要要是兩邊的資料庫計算出來的值相同，那就是資料表內的資料是一致的了。

DECLARE @databaseName VARCHAR(100) = 'Source';  -- 資料庫名稱
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @List TABLE ( TableName sysname , CheckSumValue int )



SELECT @sql += 'SELECT ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''' AS TableName, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS ChecksumValue FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = @databaseName;

INSERT INTO @List
	EXEC sp_executesql @sql;

SELECT * FROM @List ORDER BY TableName

DECLARE @databaseName VARCHAR(100) = 'Source_2023-04-18T06-00Z';  -- 資料庫名稱
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @List TABLE ( TableName sysname , CheckSumValue int )



SELECT @sql += 'SELECT ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''' AS TableName, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS ChecksumValue FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = @databaseName;

INSERT INTO @List
	EXEC sp_executesql @sql;

SELECT * FROM @List ORDER BY TableName

因此我把資料結果丟到 Excel 來做排列，從下面的圖中可以看出來，兩邊每個資料表都可以算出一個 CHECKSUM 的值，只有 SalesLT.Customer 的值，兩邊計算出來是有所不同的，因此我們需要將 Source_2023-04-18T06-00Z 裡面的資料，回復到 Source 的資料庫中，到時候要讓兩邊計算出來的值要是相同的

接下來我們要來做資料的回復，當然這裡有很多的方式，有些人會習慣把資料全部 TRUNCATE，然後用 BCP 將資料匯出後再做匯入，這也是一個不錯的方式，但我們今天想要展示的是另外的方式，是希望找到資料表內有差異的來做更新，因此這裡我們會來採用使用外部資料表 ( External Table ) 的方式來做進行

這裡我們在 Source 資料庫要做三個處理，分別是

建立 MASTER KEY ( 如果資料庫之前有建立過則可以不用再建立 )
建立資料庫認證 – 用來連接 DATA SOURCE 的時候所需要的帳號和密碼
建立資料來源 – 用來指定連線的主機和資料庫

-- 建立 MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz@WSX3edc';
GO


-- 建立 CREDENTIAL 
CREATE DATABASE SCOPED CREDENTIAL [Source_2023-04-18T06-00Z]
WITH IDENTITY='admin',
SECRET='1qaz@WSX3edc'
GO


-- 建立 DATA SOURCE
CREATE EXTERNAL DATA SOURCE [Source_2023-04-18T06-00Z]
WITH (
    TYPE = RDBMS,
    LOCATION = 'james2023.database.windows.net',
    DATABASE_NAME = 'Source_2023-04-18T06-00Z',
    CREDENTIAL = [Source_2023-04-18T06-00Z]
)

接下來我們就要在 Source 的資料庫來建立一個 External Table 來連到 Source_2023-04-18T06-00Z 的資料庫上「同一個名稱」的 SalesLT.Customer 資料表，但是這裡就會有個小問題要來解決。因為預設會使用 External Table 的 Schema 和 Table Name 來連接 Data Source 中相同的 Schema 和 Table Name，而對我們來說兩個相同資料庫就要想辦法來避開這樣的問題。

一般來說會可以用兩個方式來做

在 Source_2023-04-18T06-00Z 中另外 View 或者是 Synonym 的方式，將資料表先建立一個別名來讓 External 使用
在 Source 中建立 External Table 的時候，來指定 Schema 和 Table Name

我個人會比較推崇使用後者的方式，因為在我自己的經驗中，很多時候那個您需要讀取的資料庫中，並不允許您有權限在那個資料庫中作異動，畢竟那些設定對原本的資料庫是沒有任何的效益，我們會在自己的資料庫中去建立的時候來做指定，因此在下面的範例中，我們會採用參考 SalesLT.Customer 資料表的建立語法，修改成為建立 External Table 的語法

CREATE EXTERNAL TABLE [SalesLT].[Customer_History]
(
	[CustomerID] [int] NOT NULL,
	[NameStyle] [dbo].[NameStyle] NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [dbo].[Name] NOT NULL,
	[MiddleName] [dbo].[Name] NULL,
	[LastName] [dbo].[Name] NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[CompanyName] [nvarchar](128) NULL,
	[SalesPerson] [nvarchar](256) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[Phone] [dbo].[Phone] NULL,
	[PasswordHash] [varchar](128) NOT NULL,
	[PasswordSalt] [varchar](10) NOT NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,)
WITH
(
    SCHEMA_NAME = 'SalesLT',
	OBJECT_NAME = 'Customer',
    DATA_SOURCE = [Source_History]
);

在上面的指定中，我們可以看到有特別去加上 SCHEMA_NAME 和 OBJECT_NAME 的設定，透過這兩個的設定，您在建立 External Table 的時候，就不用受限於需要跟要介接的名稱相同。

當知道有那些資料表有不同，我們也建立好 External Table 的資料表，讓我們可以有原始的資料來做更新，接下來我們就需要知道，到底有哪些資料有變動，以往很多人會習慣或者是建議使用 CheckSum 來做處理，其實這裡也有個簡單的方式來做處理，我們會採用 except 的指令來做集合運算，這樣也可以達到類似的效果

select * from [SalesLT].[Customer]
except
select * from [SalesLT].[Customer_History]

這樣很容易我們就可以知道有那些筆資料有差異，因為原本的資料表有使用 Identity 的方式設定鍵值，因此這裡我就使用 UPDATE 的方式來做處理，因此知道哪些需要修改，我們就可以搭配 CTE 的語法來做個處理

with b as
(
	select * from [SalesLT].[Customer]
	except
	select * from [SalesLT].[Customer_History]
)
update a set Phone = c.Phone
from [SalesLT].[Customer] a 
join b ON a.CustomerID = b.CustomerID
join [SalesLT].[Customer_History] c ON a.CustomerID = c.CustomerID

所以當我們用上述語法完成後，可以使用一開始查看 CHECKSUM 的指令再來執行一次

DECLARE @databaseName VARCHAR(100) = 'Source';  -- 資料庫名稱
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @List TABLE ( TableName sysname , CheckSumValue int )



SELECT @sql += 'SELECT ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''' AS TableName, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS ChecksumValue FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = @databaseName;

INSERT INTO @List
	EXEC sp_executesql @sql;

SELECT * FROM @List ORDER BY TableName

在這裡 INFORMATION_SCHEMA 會把 External Table 給一併抓進來，因此我們就可以看到當我們使用更新之後，這兩個資料表的 CHECKSUM 的值是相同的，看起來這個簡單任務我們也順利完成了。

希望透過上述的範例，可以提供大家一些方便的技巧，讓大家在不小心修改到資料，或者是臨時需要找舊的資料回來比對的時候，可以節省一些時間

回首頁

五餅二魚工作室

James Fu 的技術學習之路

[Azure][SQL]使用 Azure SQL Database 還原資料庫時候的小技巧

系列文章

標籤雲