SQLServer

2025-02-03SQLServerSQL Server憑證備份還原使用者IOVLF作者：VincentCorgi約 1 分鐘閱讀

憑證

特性	憑證（Certificate）	對稱密鑰（Symmetric Key）
加密方式	非對稱加密（公鑰加密、私鑰解密）	對稱加密（同一密鑰加密與解密）
密鑰洩露風險	極低，只有私鑰需要保護	高，密鑰洩漏即暴露數據
跨伺服器傳輸安全性	高，公鑰可安全傳輸	低，密鑰傳輸過程需額外保護
性能	較慢，適合小範圍加密	較快，適合大量資料加密
管理難度	較高，需要處理公私鑰管理	較低，但密鑰需嚴格保護

建立憑證

切換到 master 資料庫
建立 MASTER KEY
創建加密憑證
匯出憑證及私鑰(options)

Code

-- 切換到 master 資料庫
USE master;
GO

-- 建立 MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- 創建加密憑證
CREATE CERTIFICATE <cert_name>
WITH SUBJECT = 'Certificate for encrypted backup';
GO

-- 匯出憑證及私鑰
BACKUP CERTIFICATE <cert_name>
TO FILE = '<backup_path>\<cert_name>.cert'
WITH PRIVATE KEY (
    FILE = '<backup_path>\<cert_name>_PrivateKey.key',
    ENCRYPTION BY PASSWORD = '<password>'
);
GO

刪除憑證

檢查現有的憑證
檢查憑證的依賴關係
刪除該憑證
刪除 master key

Code

-- 1. 檢查現有的憑證
SELECT name, principal_id, issuer_name, subject
FROM sys.certificates;

-- 2. 檢查憑證的依賴關係
SELECT name, certificate_id, issuer_name, subject
FROM sys.certificates
WHERE name = '<cert_name>';

-- 3. 刪除該憑證
USE master;
GO
DROP CERTIFICATE <cert_name>;
GO

-- 4. 刪除 master key
USE master;
GO
DROP MASTER KEY;
GO

使用者

後端 user

建立 user & permission

Code

-- 1. 登入 on powershell 
sqlcmd -S localhost -U sa -P '<password>' 

-- 2. 伺服器層級帳號 
CREATE LOGIN <login_name> WITH PASSWORD = '<password>'; 

-- 3. 在 master 資料庫中建立對應的資料庫層級帳號 
USE master; 
CREATE USER <db_user_name> FOR LOGIN <login_name>; 

-- 4. 授予建立資料庫的權限 
GRANT CREATE ANY DATABASE TO <login_name>;
-- 撤銷權限： 
-- REVOKE CREATE ANY DATABASE TO <login_name>;

-- 5. 授予修改資料庫的權限 
GRANT ALTER ANY DATABASE TO <login_name>;

測試

Code

sqlcmd -S localhost -U <login_name> -P '<password>'

sqlcmd -S localhost -U <login_name> -P '<login_password>'
-- 新增
CREATE DATABASE <database_name>;
GO

USE <database_name>;
CREATE TABLE <table_name> (id INT, name NVARCHAR(50), quantity INT);
GO

INSERT INTO <table_name> VALUES (1, 'banana', 150); INSERT INTO <table_name> VALUES (2, 'orange', 154);
GO

SELECT * FROM <table_name> WHERE quantity > 152;
GO

INSERT INTO <table_name> VALUES (3, 'ban', 150); INSERT INTO <table_name> VALUES (4, 'ora', 154);
GO

SELECT * FROM <table_name>;
GO

DROP TABLE <table_name>;
GO

DROP DATABASE <database_name>;
GO

備份 user

建立 user & permission

Code

-- 1. 登入 on powershell 
sqlcmd -S localhost -U sa -P '<password>'

-- 2. 創建伺服器層級的登入
CREATE LOGIN <backup_login> WITH PASSWORD = '<password>';
GO

-- 3. 創建資料庫層級的使用者
USE master;
CREATE USER <backup_user> FOR LOGIN <backup_login>;
GO

-- 4. 授予備份資料庫的權限
GRANT BACKUP DATABASE TO <backup_login>;
GO

-- 5. 授予對稱密鑰的使用權限（加密備份需要此權限）
GRANT CONTROL ON CERTIFICATE::<cert_name> TO <backup_login>;
GO

-- 6. 
USE <database_name>;
CREATE USER <backup_user> FOR LOGIN <backup_login>;
GO

-- 7. 
ALTER ROLE db_backupoperator ADD MEMBER <backup_user>;
GO

測試

Code

sqlcmd -S localhost -U <backup_login> -P '<password>'

sqlcmd -S localhost -U <backup_login> -P '<backup_password>'

-- full
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>\Full_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.bak';

BACKUP DATABASE <database_name>
TO DISK = @BackupFile
WITH COMPRESSION, 
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- diff
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>\Diff_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.bak';

BACKUP DATABASE <database_name>
TO DISK = @BackupFile
WITH DIFFERENTIAL, 
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- log
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>\Log_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.trn';

BACKUP LOG <database_name>
TO DISK = @BackupFile
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

備份

使用憑證做加密。

!!!! 目前要備份資料庫的話，只要新增資料庫就要幫 backup user 新增 login 跟新增備份的權限，他權限非常低。

目前時間策略：每週日 AM 1:00 一次完整備份，每天 AM 2:00 一次差異備份，每 30 分鐘的交易紀錄備份：開頭是 -5 分開始，不要跟整點撞到 ex: 55 分跟 25 分。

備份

Code

BACKUP DATABASE <database_name>
TO DISK = '<backup_path>\<database_name>Backup.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

還原

都是用 SA

1. 在全新的 SQL 復原加密備份資料(options)

Code

-- 在新伺服器上匯入憑證及私鑰
USE master;
GO

CREATE CERTIFICATE <cert_name>
FROM FILE = '<restore_path>\<cert_name>.cert' -- 匯入憑證
WITH PRIVATE KEY (
    FILE = '<restore_path>\<cert_name>_PrivateKey.key', -- 匯入私鑰
    DECRYPTION BY PASSWORD = '<private_key_password>' -- 使用密碼解密私鑰
);
GO

2. 還原資料

Code

-- latest full
RESTORE DATABASE <database_name>
FROM DISK = '<backup_path>\<database_name>_Full_<timestamp>.bak'
WITH NORECOVERY;
GO

-- latest diff
RESTORE DATABASE <database_name>
FROM DISK = '<backup_path>\<database_name>_Diff_<timestamp>.bak'
WITH NORECOVERY;
GO

-- all log 
RESTORE LOG <database_name>
FROM DISK = '<backup_path>\<database_name>_Log_<timestamp>.trn'
WITH NORECOVERY;
GO

3. 還原到新檔名（MOVE）

Code

USE master;
GO

RESTORE DATABASE <new_database_name>
FROM DISK = '<backup_path>\<source_database>.bak'
WITH
  MOVE '<source_logical_data_name>'
    TO '<sql_data_path>\<new_database_name>.mdf',
  MOVE '<source_logical_log_name>'
    TO '<sql_data_path>\<new_database_name>_log.ldf';
GO

備註

如果要用不是 sa 做備份要新增 restore 權限

Code

-- 授予還原資料庫的權限
GRANT RESTORE DATABASE TO <restore_user>;

-- 授予對稱密鑰的控制權限
GRANT CONTROL ON CERTIFICATE::<cert_name> TO <restore_user>;

自動備份

啟用 SQL Server Agent
建立自動備份的 SQL 作業 (Job)
1. 開啟 SQL Server Management Studio (SSMS)。
2. 展開 SQL Server Agent → Jobs → 右鍵 → New Job。
3. 配置以下選項：
  1. General (一般)：
    1. Job Name：如 job_name
    2. Owner：選擇高權限帳戶（例如 sa 或資料庫所有者）。
  2. Steps (步驟)：
    1. 點擊 New，然後編寫備份腳本。

常用指令

Code

-- 查詢現在的DB
SELECT DB_NAME();

-- 列出所有 DB
SELECT Name from sys.databases;

-- 刪掉 DB
DROP DATABASE <database_name>;

-- 刪除數據庫用戶
DROP USER <user_name>;

-- 刪除伺服器登錄
DROP LOGIN <login_name>;

-- 查詢 憑證
SELECT name, certificate_id
FROM sys.certificates;

-- 查詢 對稱加密密鑰（Symmetric Keys)
SELECT name, key_algorithm, key_length
FROM sys.symmetric_keys;

-- 關閉對稱密鑰
CLOSE SYMMETRIC KEY <key_name>;

-- 刪除對稱密要
DROP SYMMETRIC KEY <key_name>;

-- 驗證 檢測潛在問題
-- • 確認備份檔案與目前的 SQL Server 版本是否兼容。
-- • 確保備份檔案中包含完整的資料庫結構和資料。
-- • 如果備份檔案使用了加密或壓縮，驗證相關密鑰或憑證是否可用。ex: 沒有憑證，無法驗證
------- Cannot find the certificate '<cert_name>', because it does not exist or you do not have permission.
RESTORE VERIFYONLY
FROM DISK = '<backup_path>\<database_name>_Full_<timestamp>.bak';
GO

備份實際流程

Code

-- dev
CREATE DATABASE <database_name>;
GO

USE <database_name>;
GO
CREATE TABLE <table_name> (
    ID INT PRIMARY KEY,
    ProductName NVARCHAR(50),
    Quantity INT
);
GO

INSERT INTO <table_name> VALUES (1, 'Apple', 100), (2, 'Orange', 50);
GO

-- backup - full
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>_Full_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.bak';

BACKUP DATABASE <database_name>
TO DISK = @BackupFile
WITH COMPRESSION, 
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- dev
USE <database_name>;
GO
INSERT INTO <table_name> VALUES (3, 'Banana', 30);
GO

-- backup - diff 1
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>_Diff_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.bak';

BACKUP DATABASE <database_name>
TO DISK = @BackupFile
WITH DIFFERENTIAL, 
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- dev
USE <database_name>;
GO
INSERT INTO <table_name> VALUES (4, 'Pineapple', 20);
GO

-- backup - diff 2
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>_Diff_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.bak';

BACKUP DATABASE <database_name>
TO DISK = @BackupFile
WITH DIFFERENTIAL, 
     ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- dev
USE <database_name>;
GO
INSERT INTO <table_name> VALUES (5, 'Strawberry', 10);
GO

-- backup - log 1
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>_Log_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.trn';

BACKUP LOG <database_name>
TO DISK = @BackupFile
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- dev
USE <database_name>;
GO
INSERT INTO <table_name> VALUES (6, 'Blueberry', 25);
GO

-- backup - log 2
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>_Log_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.trn';

BACKUP LOG <database_name>
TO DISK = @BackupFile
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- dev
USE <database_name>;
GO
INSERT INTO <table_name> VALUES (7, 'graph', 22);
GO

-- backup - log 3
DECLARE @BackupFile NVARCHAR(255) = 
    '<backup_path>\<database_name>_Log_' + FORMAT(GETDATE(), 'yyyyMMddHHmm') + '.trn';

BACKUP LOG <database_name>
TO DISK = @BackupFile
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = <cert_name>);
GO

-- dev
use master;
DROP DATABASE <database_name>;
GO

-------------------------- restore ----------------------------
-- latest full
RESTORE DATABASE <database_name>
FROM DISK = '<backup_path>\<database_name>_Full_<timestamp>.bak'
WITH NORECOVERY;
GO

-- latest diff
RESTORE DATABASE <database_name>
FROM DISK = '<backup_path>\<database_name>_Diff_<timestamp>.bak'
WITH NORECOVERY;
GO

-- all log 
RESTORE LOG <database_name>
FROM DISK = '<backup_path>\<database_name>_Log_<timestamp>.trn'
WITH NORECOVERY;
go
RESTORE LOG <database_name>
FROM DISK = '<backup_path>\<database_name>_Log_<timestamp2>.trn'
WITH NORECOVERY;
GO
RESTORE LOG <database_name>
FROM DISK = '<backup_path>\<database_name>_Log_<timestamp3>.trn'
WITH RECOVERY;
GO

-- option, 萬一最後一個沒有用 RECOVERY，可以直接強制關掉。
RESTORE DATABASE <database_name> 
WITH RECOVERY;
GO


--------------- Check ------------------
USE <database_name>;
SELECT * FROM <table_name>;
GO

IO 統計與 VLF

Code

-- 查看 IO 統計
SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_latency,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_latency
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id;

Code

-- 檢查 Log 檔案詳細資訊
SELECT
    name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('<database_name>') AND type = 1;

-- 檢查 VLF 數量
DBCC LOGINFO('<database_name>');