SQLServer自動備份、壓縮、刪除舊文件

1、sqlserversqlserver 自動備份、壓縮、刪除舊文件自動備份、壓縮、刪除舊文件作為數據庫維護人員，可能大家經常 要做的事情就是備份數據庫，并且為了安全起見最好是異地備份，但是如果要經常備份，然后把他們傳到本地，是一件非常費時費力的事情，尤其像我這種比較懶的程序員，甚至有時候會忘記備份，所以比較好的解決辦法就是讓 sql server 自動備份，同時自動下載。同時由于 sql server 自動備份的 bak 文件通常都比較大，如果直接傳到本地，要花比較久的時間，因此為了提高效率最好在備份后先壓縮，并且為了安全起見，下載下來的文件還要加密。那么如何讓 sql 自動備份、加密壓縮、刪除

2、舊的 bak 文件并自動下載的所 有 流 程 都 自 動 完 成 呢 ， 下 面 我 就 將 我 使 用 到 的 工 具 、 方 法 和 代 碼 共 享 出 來 ， 供 大 家 參 考 。第一步：在查詢分析器中執(zhí)行以下命令use masterexec sp_configure show advanced options, 1;reconfigure;exec sp_configure xp_cmdshell, 1;reconfigure;sql server2000 可以跳過這一步， 而對于 sql server2005 因為它默認是不支持 xp_cmdshell 執(zhí)行命令的， sql ser

3、ver已封鎖元件 xp_cmdshell 的 程序 sys.xp_cmdshell 的存取，此元件已經由此伺服器的安全性組態(tài)關閉，系統(tǒng)管理員可以使用 sp_configure 來啟用 xp_cmdshell 的使用，所以我們得恢復其執(zhí)行命令。第二步：在 sql 企業(yè)管理器sql 代理（如果服務未啟用，先啟用該服務）作業(yè)新建作業(yè)在常規(guī)選項中輸入作業(yè)名稱如圖1：圖 1錄入作業(yè)步驟：在步驟選項中新建作業(yè)步驟 1，名稱命名為xx 備份、壓縮 ，然后在命令框中輸入如下的 sql代碼：declareprefix nvarchar(100),datefile nvarchar(100),bakfile nv

4、archar(100),rarfile nvarchar(100),delcmd nvarchar(100),rarcmd nvarchar(150)set prefix=f:erp -備份文件夾set datefile=db_aaa+cast(year(getdate() as varchar(4)+cast(month(getdate() asvarchar(2)+cast(day(getdate() as varchar(2)+cast(datepart(hour,getdate() as nvarchar(2)+cast(datepart(minute,getdate() asnvar

5、char(2)-備份文件名set bakfile=prefix+datefile+.bakset rarfile=prefix+datefile+.rarbackup database db_aaa to disk = bakfile with init , nounload , name = ndb_aaa數據備份, noskip , stats = 10, noformatset delcmd = del+prefix+ rar*.rar -先清空rar備份文件夾set rarcmd=c:progra1winrarwinrar.exe a -p123 +rarfile+ +bakfile

6、-將yourpw改為您要設置的密碼，進行加密壓縮exec master.xp_cmdshell delcmdexec master.xp_cmdshell rarcmdgo作業(yè)步驟 1 建好后，新建作業(yè)步驟 2，名稱命名為刪除當天 bak 文件 ，然后在命令框中輸入如下的 sql 代碼：declare d nvarchar(64);set d = convert(varchar,dateadd(day,-0,getdate(),120);execute master.dbo.xp_delete_file 0,nf:erp,nbak,d;新建計劃任務：在計劃選項中新建一個計劃任務如下圖：作業(yè)計劃

7、建好后在 sql 企業(yè)管理器-sql 代理-作業(yè)在作業(yè)上點右鍵查看該作業(yè)是否已經啟用。sqlsql 定時自動備份，并將備份文件加密壓縮并自動下載的實現定時自動備份，并將備份文件加密壓縮并自動下載的實現sql 定時自動備份，并將備份文件加密壓縮并自動下載的實現作者：刀尖客 qq:51978456 msn:=*/作為程序員或者數據庫維護人員，可能大家經常 要做的事情就是備份數據庫，并且為了安全起見最好是異地備份，但是如果要經常備份，然后把他們傳到本地，是一件非常費時費力的事情，尤其像我這種比較懶的程序員，甚至有時候會忘記備份_，所以比較好的解決辦法就是讓 sql server 自動備份，同時自動下

8、載。同時由于 sql server 自動備份的 bak 文件通常都比較大，如果直接傳到本地，要花比較久的時間，因此為了提高效率最好在備份后先壓縮，并且為了安全起見，下載下來的文件還要加密。那么有沒有可能讓 sql 自動備份，加密壓縮，自動下載的所有流程都自動完成呢，答案是肯定的。下面我就將我使用到的工具、方法和代碼共享出來，供大家參考。第一個步驟就是讓 sql server 自動備份數據庫，這方面的文章比較多，最常用的是使用sql server 的代理， 定時執(zhí)行一個“作業(yè)”。 基本步驟就是先編寫一個執(zhí)行備份的存儲過程，然后在 sql server 的企業(yè)管理器中，選擇“管理sql serve

9、r 代理新建作業(yè)”，然后在新建作業(yè)步驟中類型選擇 transact-sql， 再在命令中輸入執(zhí)行備份的存儲過程 （或者開始不建立存儲過程，在這里直接輸入備份的 sql 代碼也可）就可以了。第二個步驟就是加密并壓縮備份的數據庫文件。我自己試了一下，一個 200m 的 sql 備份文件，用 winrar 壓縮之后的大小是 18m 左右，所以為了快速傳到本地，壓縮是非常有必要的。為了在備份后，馬上進行壓縮，最方便的就是直接使用 sql 命令執(zhí)行壓縮，由于 winrar 可以通過命令行調用，所以我們可以使用 sql server 中的 xp_cmdshell命令來執(zhí)行壓縮指令，同時通過其中的一些參數指

10、定壓縮密碼，這樣加密壓縮就實現了。（為了實現此功能，服務器上要安裝 winrar）最后還需要定時自動下載。要實現這個功能可以使用 windows 的任務計劃，再結合系統(tǒng)自帶的 ftp 命令即可實現。下面給出實現此功能的完整步驟和代碼：服務器端：1、 在要備份的數據庫中建立存儲過程：create procedure do_backup asdeclareprefix nvarchar(100),datefile nvarchar(100),bakfile nvarchar(100),rarfile nvarchar(100),delcmd nvarchar(100),rarcmd nvarcha

11、r(150)set prefix=e:/bak/databasename/ -備份文件夾set datefile=fzjs+cast(year(getdate() as varchar(4)+cast(month(getdate() as varchar(2)+cast(day(getdate() as varchar(2)set bakfile=prefix+datefile+.bakset rarfile=prefix+rar/+datefile+.rarbackup database databasename2 to disk = bakfile with init , nounload

12、 , name = ndatabasename 數據備份,noskip , stats = 10, noformatset delcmd = del +prefix+ rar/*.rar -先清空 rar 備份文件夾set rarcmd =c:/progra1/winrar/winrar.exe a -pyourpw +rarfile+ +bakfile -將 yourpw 改為您要設置的密碼，進行加密壓縮exec master.xp_cmdshell delcmdexec master.xp_cmdshell rarcmdgo2、 數據庫管理器管理作業(yè)新建作業(yè)設置作業(yè)的執(zhí)行程序和運行時間，根據自己的備份需要進行設置，然后啟用作業(yè)。3、 在服務器的 ftp中將數據壓縮備份的目錄設置好。4、 在本地建立一個 ftp 命令的文本文件：c:/ftp.txt文件中輸入以下內容（替換成您自己的內容并去掉）：open 您的服務器 ip 地址數據庫備份壓縮目錄 ftp登錄用戶名數據庫備份壓縮目錄 ftp登錄密碼verbose offlcd 本地保存路徑prompt of