T-SQL 實戰：自動化資料庫備份與還原完整解決方案

在生產環境中，透過 T-SQL 腳本實現資料庫備份與還原自動化，不僅能靈活控制備份策略，更能整合進 CI/CD 流程與災難恢復演練。本文提供一套完整的 T-SQL 解決方案，涵蓋預設路徑獲取、備份/還原指令、強制斷開連線、SQL Agent 作業排程四大核心模組。

核心函數：動態獲取資料庫預設路徑

避免硬編碼路徑，通用函數 dbo.f_getdbpath 可根據輸入參數返回資料檔、記錄檔或備份檔的預設目錄：

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[f_getdbpath]') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[f_getdbpath];
GO

CREATE FUNCTION f_getdbpath(@dbname SYSNAME)
RETURNS NVARCHAR(260)
AS
BEGIN
    DECLARE @re NVARCHAR(260);
    -- 取得 master 資料庫檔案路徑作為基準
    IF @dbname IS NULL OR DB_ID(@dbname) IS NULL
        SELECT @re = RTRIM(REVERSE(filename)) FROM master..sysdatabases WHERE name = 'master';
    ELSE
        SELECT @re = RTRIM(REVERSE(filename)) FROM master..sysdatabases WHERE name = @dbname;

    -- 反轉字串並截取目錄部分
    IF @dbname IS NULL
        SET @re = REVERSE(SUBSTRING(@re, CHARINDEX('\', @re) + 5, 260)) + 'BACKUP';  -- 備份預設目錄
    ELSE
        SET @re = REVERSE(SUBSTRING(@re, CHARINDEX('\', @re), 260));  -- 資料/記錄檔目錄

    RETURN(@re);
END
GO

呼叫範例

SELECT 
    [tempdb路徑] = dbo.f_getdbpath('tempdb'),
    [預設資料目錄] = dbo.f_getdbpath(''),
    [預設備份目錄] = dbo.f_getdbpath(NULL);

備份與還原核心腳本模板

1. 完整備份

DECLARE @dbname SYSNAME = 'YourDB';
DECLARE @backupPath NVARCHAR(500) = dbo.f_getdbpath(NULL) + '\' + @dbname + '_Full_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak';

BACKUP DATABASE @dbname
TO DISK = @backupPath
WITH INIT, COMPRESSION, STATS = 10;  -- 覆寫、壓縮、進度顯示

2. 還原（含強制斷開連線）

DECLARE @dbname SYSNAME = 'YourDB';
DECLARE @backupFile NVARCHAR(500) = 'D:\Backup\YourDB_Full_20240115.bak';

-- 1. 將資料庫設為單用戶模式，強制斷開現有連線
ALTER DATABASE @dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 2. 還原資料庫
RESTORE DATABASE @dbname
FROM DISK = @backupFile
WITH REPLACE, RECOVERY, STATS = 10;

-- 3. 還原多檔案群組時需指定 MOVE 選項（視實際檔案結構調整）
-- WITH MOVE 'LogicalData' TO 'D:\Data\YourDB.mdf',
--      MOVE 'LogicalLog' TO 'L:\Log\YourDB.ldf';

SQL Agent 作業自動化部署

將上述腳本封裝為預存程序，再透過 sp_add_job、sp_add_jobstep、sp_add_jobschedule 建立定時作業，實現：

• 每日完整備份 + 每小時記錄備份
• 備份檔案自動清理（保留 N 天）
• 還原演練作業（定期驗證備份可用性）

關鍵知識點總結

1. 系統目錄查詢： 利用 master..sysdatabases 反推預設路徑，適應叢集、命名實例等複雜部署。
2. BACKUP/RESTORE 語法精通： 掌握 WITH INIT, COMPRESSION, STATS, REPLACE, RECOVERY, NORECOVERY, STANDBY 等選項組合。
3. 連線控制： ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE 是還原前斷開連線的標準做法。
4. 作業系統整合： 透過 xp_cmdshell 或 PowerShell 步驟實現備份檔異地複製、雲端上傳等擴充功能。

掌握這套 T-SQL 體系，您將擁有完全可控、可審計、可擴展的資料庫備份還原自動化能力。