SQL Server Express 自動化管理 (二) 定期刪除資料

本文將介紹如何以 DOS Batch、SQL Stored Procedure、Windows 7 工作排程器來開發一個簡易的自動清除舊資料功能。共包括 3 部分：

• Clean.bat：DOS Batch 檔案，透過 sqlmd 呼叫 Stored Procedure --> usp_Clean 以進行刪除作業。
• usp_Clean：SQL Server Stored Procedure，存在於欲刪除舊資料的資料庫中，負責刪除作業，由 Clean.bat 呼叫。
• 工作排程器：Windows 所內附功能，此處將以 Windows 7 進行說明。

建置步驟

1) 在該台已安裝 SQL Server 2008 R2 Express 的電腦上，確定目錄 C:\Home\Expense 已經存在。


2) 用文章(SQL Server 2008 R2 Express 自動化管理 (一) 定期備份) 建立的 Expense 資料庫、DETAIL_RECORD Table。


3) 建立一個供排程作業使用的 Clean.bat DOS Batch 檔案，並將其存放到 C:\Home\Expense 目錄。該檔案內容如下：

@echo off

sqlcmd -S localhost\SQLEXPRESS -E -d Expense -Q "exec usp_Clean"

4) 撰寫 usp_Clean Stored Procedure，並於 SQL Server 的 Expense 資料庫中編譯 (Compile)，內容如下：

use Expense;

if object_id ('usp_Clean') is not null
   drop proc usp_Clean
go

Create Proc usp_Clean
as

declare @Current_Month_First_Date   datetime,
        @Now                        datetime
  
set nocount on

select @Now = GETDATE()

select @Current_Month_First_Date = 
       CAST(CAST(DATEPART(YEAR,  @Now) as varchar(4)) + '/' + 
            CAST(DATEPART(MONTH, @Now) as varchar(2)) + '/01 00:00:00 ' 
            as datetime)

-- Delete Old Data (More Than 5 Year)
delete from DETAIL_RECORD
 where Record_Date < dateadd(YEAR, -5, @Current_Month_First_Date)

set nocount off
return

5) 將工作排程器設定為每月 1 號下午 01:00:00 呼叫 Clean.bat 進行過期資料刪除作業：(螢幕左下方) 開始 --> 所有程式 --> 附屬應用程式 --> 系統工具 --> 工作排程器
(1). 於 "工作排程器" 視窗中，highlight 畫面左方的 "工作排程器程式庫"後，按一下滑鼠右鍵，選取 "建立工作"。
(2). 此時將出現 "建立工作" 視窗 (目前位於 "一般" 頁籤)。
(3). 在 "名稱" 欄位輸入 DB Clean，勾選 "只有使用者登入時才執行"、"以最高權限執行" 欄位後，點選上方 "觸發程序" 頁籤繼續設定。
(4). 於 "觸發程序" 頁籤點選 "新增"，出現 "新增觸發程序" 視窗 --

• 選擇 "每月"。
• "開始" 欄位，選取 2012/4/1 下午 01:00:00 。
• "月份" 欄位，選取 "選取每個月"。
• 按下 "確定" 後，點選上方 "動作" 頁籤繼續設定。

(5). 於 "動作" 頁籤點選 "新增"，此時出現 "新的執行動作" 視窗 --

• "執行" 欄位，選取 "啟動程式"。
• 按下 "瀏覽"，於出現的視窗中，選擇 C:\Home\Expense\Clean.bat，按 "開啟舊檔"。
• 按下 "確定" 功能鍵後，再按一次 "確定"。

自 2012/4/1 起，每月 1 號下午 01:00:00 便會自動刪除 Expense 資料庫內超過 5 年的資料。