SQL SERVER 設定自動備份和刪除舊的資料庫檔案

標籤：

開啟SQL SERVER MANAGEMENT STUDIO，啟動SQL SERVER代理服務（注意在“控制台-管理工具-服務”中設定SQL SERVER AGENT的啟動類型為自動）。啟動後點擊“作業-新增作業”，彈出一個作業屬性的視窗，在“常規”欄目裡可以先給作業命名，假設為“備份資料”。 一、備份資料的第一個步驟--“備份當日資料” 
在“步驟”欄目裡建立一個步驟名為“備份當日資料”，類型為“T-SQL”，資料庫選擇你要操作的資料庫（假設為“TESTDB”），命令視窗裡填入備份的SQL語句。假裝置份資料放在“D:\BACKUP”裡，備份檔案命名格式為“SQL-2009-3-26.BAK”，那麼語句如下：
DECLARE @filename VARCHAR(255)
DECLARE @date DATETIME
SELECT @date=GETDATE()
SELECT @filename = ‘D:\BACKUP\SQL-‘+CAST(DATEPART(yyyy,@date) as varchar)+‘-‘+CAST(DATEPART(mm,@date) as varchar)+‘-‘+CAST(DATEPART(dd,@date) as varchar)+‘.bak‘
BACKUP DATABASE [TESTDB] TO  DISK = @filename WITH INIT
GO
在步驟屬性的進階的“成功時要執行的操作”選擇“轉到下一步”即可，這樣“備份當日資料”的步驟已經建立好。
二、備份資料的第二個步驟--“刪除舊有備份”
我們可以設定只保留5天內的備份資料，那麼必須刪除5天前的資料備份檔案。在“備份資料”作業屬性視窗的步驟欄目裡，建立第二個步驟命名為“刪除舊有備份”。同樣類型為“T-SQL”，命令視窗裡填入一下SQL語句：
DECLARE @OLDDATE DATETIME
SELECT @OLDDATE=GETDATE()-5
EXECUTE master.dbo.xp_delete_file 0,N‘D:\BACKUP‘,N‘bak‘,@olddate,1
此命令會刪除“D:\BACKUP”裡5天前的.BAK或.TRN格式的檔案，不用指定檔案名稱是什麼。因為SQL SERVER的備份檔案裡包含了時間屬性在裡面。在步驟屬性的進階的“成功時要執行的操作”選擇“退出報告成功的作業”即可，這樣第二個步驟已經建立好。
三、備份資料作業屬性的計劃欄目裡，設定作業的執行時間
建立一個作業計劃，命名為“每日自動備份和刪除”，再選擇執行的周期，例如每天淩晨1點開始執行。
最後儲存整個“備份資料”的作業，每日就會自動備份資料庫和刪除舊有資料了。

SQL SERVER 設定自動備份和刪除舊的資料庫檔案