SQL Server 2008 資料庫日常維護常用 T-SQL 腳本彙整與實測修正

1. 定時完整備份 (含日期檔名)

DECLARE @sql NVARCHAR(MAX), @path NVARCHAR(260) = N'D:\Backup\';
SET @sql = N'BACKUP DATABASE [Smallerp] TO DISK = N''' + @path + 
           REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 112), '-', '') + N'.bak''' +
           N' WITH INIT, COMPRESSION, STATS = 10;';
EXEC sp_executesql @sql;

2. 刪除 N 天前備份檔 (需啟用 xp_cmdshell)

-- 啟用 xp_cmdshell (一次即可)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 刪除 5 天前 .bak
DECLARE @cmd NVARCHAR(MAX) = N'del /q D:\Backup\' + 
       REPLACE(CONVERT(NVARCHAR(10), DATEADD(DAY, -5, GETDATE()), 112), '-', '') + N'*.bak';
EXEC xp_cmdshell @cmd, NO_OUTPUT;

3. 記錄檔壓縮 (SQL 2008 正確寫法)

-- 簡單模式下壓縮
ALTER DATABASE [Smallerp] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (N'Smallerp_log', 100); -- 目標大小 MB
ALTER DATABASE [Smallerp] SET RECOVERY FULL;
-- 完整模式需先備份記錄才能壓縮
BACKUP LOG [Smallerp] TO DISK = N'D:\Backup\Smallerp_log.trn' WITH INIT;
DBCC SHRINKFILE (N'Smallerp_log', 100);

註： SQL 2008 已移除 BACKUP LOG ... WITH NO_LOG 與 DUMP TRANSACTION。

4. 檢視活躍連線與封鎖

-- 活躍連線
EXEC sp_who 'active';
-- 或使用 DMV (建議)
SELECT session_id, login_name, host_name, program_name, status, last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- 封鎖資訊
SELECT blocking_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

5. 查詢計畫與效能分析

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 執行查詢
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- 文字版執行計畫
SET SHOWPLAN_TEXT ON;
-- 查詢
SET SHOWPLAN_TEXT OFF;

6. 差異備份腳本

DECLARE @file NVARCHAR(260) = N'D:\Backup\Smallerp_' + 
       REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120), '-', ''), ':', ''), ' ', '') + N'.diff';
BACKUP DATABASE [Smallerp] TO DISK = @file
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

7. 定時清理多類型備份檔 (完整/差異/記錄)

DECLARE @base NVARCHAR(260) = N'D:\Backup\';
DECLARE @cmd NVARCHAR(MAX);

-- 刪除 15 天前完整備份
SET @cmd = N'del /q ' + @base + N'Full_' + 
           LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), DATEADD(DAY, -15, GETDATE()), 120), '-', ''), ':', ''), ' ', ''), 8) + N'*.bak';
EXEC xp_cmdshell @cmd, NO_OUTPUT;

-- 刪除 8 天前差異備份
SET @cmd = N'del /q ' + @base + N'Diff_' + 
           LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), DATEADD(DAY, -8, GETDATE()), 120), '-', ''), ':', ''), ' ', ''), 8) + N'*.diff';
EXEC xp_cmdshell @cmd, NO_OUTPUT;

-- 刪除 8 天前記錄備份
SET @cmd = N'del /q ' + @base + N'Log_' + 
           LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), DATEADD(DAY, -8, GETDATE()), 120), '-', ''), ':', ''), ' ', ''), 8) + N'*.trn';
EXEC xp_cmdshell @cmd, NO_OUTPUT;

8. 復原模式說明與備份策略對照

建議自動化方式

• 使用 維護計畫 或 SQL Agent Job 排程上述腳本。
• 備份完成後驗證 RESTORE VERIFYONLY。
• 將備份檔異地複製 (網路分享、雲端儲存)。
• 定期演練還原流程，確保備份可用。