SQL Server T-SQL 實作資料庫備份與還原自動化：含預設路徑取得、強制離線還原與排程作業建立

核心功能模組

1. 取得預設資料/備份路徑：讀取註冊表或系統資料表，避免硬編碼路徑。
2. 動態備份指令：BACKUP DATABASE 支援差異、記錄、完整備份，檔案名含時間戳。
3. 強制還原邏輯：ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE 踢除現有連線，確保還原成功。
4. SQL Agent 作業自動建立：sp_add_job 系列預存程序建立排程備份作業。

1. 取得預設路徑函數

CREATE FUNCTION dbo.fn_GetDefaultPath(@dbname SYSNAME = NULL)
RETURNS NVARCHAR(260)
AS
BEGIN
    DECLARE @path NVARCHAR(260);
    -- 1. 嘗試從指定資料庫取得檔案路徑
    IF @dbname IS NOT NULL AND EXISTS (SELECT 1 FROM sysdatabases WHERE name = @dbname)
    BEGIN
        SELECT @path = REVERSE(SUBSTRING(REVERSE(filename), CHARINDEX('\', REVERSE(filename)) + 1, 260))
        FROM sysaltfiles WHERE dbid = DB_ID(@dbname) AND fileid = 1;
    END
    -- 2. 回落至 master 資料庫預設資料路徑
    IF @path IS NULL
    BEGIN
        SELECT @path = REVERSE(SUBSTRING(REVERSE(filename), CHARINDEX('\', REVERSE(filename)) + 1, 260))
        FROM sysaltfiles WHERE dbid = 1 AND fileid = 1;
    END
    -- 3. 最後回落註冊表
    IF @path IS NULL
        EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
            N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @path OUTPUT;
    RETURN @path;
END
GO

2. 動態完整備份預存程序

CREATE PROCEDURE dbo.usp_BackupDatabase
    @dbname SYSNAME,
    @backupType CHAR(1) = 'D',  -- D=完整, I=差異, L=記錄
    @backupPath NVARCHAR(260) = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @filename NVARCHAR(260), @sql NVARCHAR(MAX);

    IF @backupPath IS NULL
        SET @backupPath = dbo.fn_GetDefaultPath(@dbname) + '\Backup\';

    SET @filename = @backupPath + @dbname + '_' 
        + CONVERT(VARCHAR(8), GETDATE(), 112) 
        + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') 
        + CASE @backupType WHEN 'D' THEN '.bak' WHEN 'I' THEN '.diff' ELSE '.trn' END;

    SET @sql = 'BACKUP DATABASE ' + QUOTENAME(@dbname) 
        + ' TO DISK = ' + QUOTENAME(@filename, '''')
        + CASE @backupType WHEN 'I' THEN ' WITH DIFFERENTIAL' WHEN 'L' THEN ' WITH NORECOVERY' ELSE '' END
        + ', INIT, STATS = 10';

    EXEC sp_executesql @sql;
    SELECT @backupPath = @filename;
END
GO

3. 強制還原預存程序 (含踢除連線)

CREATE PROCEDURE dbo.usp_RestoreDatabase
    @dbname SYSNAME,
    @backupFile NVARCHAR(260),
    @forceKill BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);

    -- 1. 強制設為單用戶模式，立即回滾現有交易
    IF @forceKill = 1
    BEGIN
        SET @sql = 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
        EXEC sp_executesql @sql;
    END

    -- 2. 還原資料庫 (自動搬移檔案至預設路徑)
    DECLARE @dataPath NVARCHAR(260) = dbo.fn_GetDefaultPath();
    DECLARE @logPath NVARCHAR(260) = @dataPath; -- 簡化：同目錄

    SET @sql = 'RESTORE DATABASE ' + QUOTENAME(@dbname) 
        + ' FROM DISK = ' + QUOTENAME(@backupFile, '''')
        + ' WITH REPLACE, RECOVERY, STATS = 10'
        + ', MOVE ''' + @dbname + ''' TO ''' + @dataPath + @dbname + '.mdf'''
        + ', MOVE ''' + @dbname + '_log'' TO ''' + @logPath + @dbname + '_log.ldf''';

    EXEC sp_executesql @sql;

    -- 3. 還原多用戶模式
    SET @sql = 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET MULTI_USER';
    EXEC sp_executesql @sql;
END
GO

4. 建立每日排程備份作業

DECLARE @jobId BINARY(16);
EXEC msdb.dbo.sp_add_job 
    @job_name = N'Daily Full Backup - All User DBs',
    @enabled = 1,
    @description = N'每日 02:00 執行所有使用者資料庫完整備份',
    @job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'Backup Databases',
    @subsystem = N'TSQL',
    @command = N'
        DECLARE @name SYSNAME;
        DECLARE cur CURSOR FOR SELECT name FROM sysdatabases WHERE dbid > 4; -- 排除系統庫
        OPEN cur;
        FETCH NEXT FROM cur INTO @name;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC dbo.usp_BackupDatabase @name;
            FETCH NEXT FROM cur INTO @name;
        END;
        CLOSE cur; DEALLOCATE cur;',
    @database_name = N'msdb',
    @flags = 0;

EXEC msdb.dbo.sp_add_jobschedule
    @job_id = @jobId,
    @name = N'Daily 02:00',
    @freq_type = 4,          -- Daily
    @active_start_time = 20000; -- 02:00:00

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
GO

使用範例

-- 手動完整備份
DECLARE @path NVARCHAR(260);
EXEC dbo.usp_BackupDatabase 'MyDB', 'D', @path OUTPUT;
PRINT '備份完成：' + @path;

-- 手動還原 (強制踢除連線)
EXEC dbo.usp_RestoreDatabase 'MyDB', 'D:\Backup\MyDB_20240115_020000.bak', 1;

注意事項

• 執行備份/還原的帳號需具備 sysadmin 或 db_backupoperator / dbcreator 角色。
• 還原時 MOVE 子句的邏輯檔名需與備份集一致，可用 RESTORE FILELISTONLY 查詢。
• 生產環境建議：完整備份 + 定期記錄備份 + 異地複製，並定期演練還原驗證。

結語

以 T-SQL 封裝備份還原邏輯，結合 SQL Agent 排程，可構建零成本、高可靠的自動化備份體系。核心在於：動態路徑解析、強制離線還原、作業參數化，三者缺一不可。