如何讓SQL Server數(shù)據(jù)庫自動備份并壓縮

1、如何讓SQLServer數(shù)據(jù)庫自動備份并壓縮如果SQLServer代理沒有啟動，我們先把其啟動，然后新建立一個作業(yè)，名稱命名為“MyDb完全備份”，在分類下面選擇數(shù)據(jù)庫維護”，然后新建立作業(yè)第一個步驟，步驟名為對數(shù)據(jù)進行完全備份”，然后在命令框中輸入如下的SQL代碼：DECLAREstrSqlVARCHAR(IOOO),strSqlCmdVARCHAR(IOOO),timeDateDiffINTSETtimeDateDiff=DATEDIFF(week,O,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeD

2、ateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-備份目錄及備份的文件頭+CONVERT(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全備份日期+_0100-完全備份時間+完全備份SETstrSqlCmd=strSql+.BAK-備份文件的擴展名BACKUPDATABASEMyDbTODISK=strSqlCmdWITHINIT,NOUNLOAD,NAME=NMyDb備份,NOSKIP,STATS=10,NOFORMAT操作如圖一:然后開始執(zhí)行對數(shù)據(jù)庫的壓縮，在步驟中再新建一個作

3、業(yè)，步驟名為壓縮數(shù)據(jù)庫”，然后在命令框中輸入如下的SQL代碼：DECLAREstrSqlVARCHAR(IOOO),strSqlCmdVARCHAR(IOOO),timeDateDiffINT,strWeekDayVARCHAR(20)SETtimeDateDiff=DATEDIFF(week,O,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeDateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-備份目錄及備份的文件頭+CONVERT

4、(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全備份日期+_0100-完全備份時間+完全備份SETstrWeekDay=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THEN星期天WHEN2THEN星期一WHEN3THEN星期二WHEN4THEN星期三WHEN5THEN星期四WHEN6THEN星期五WHEN7THEN星期六ENDSETstrSqlCmd=ECHO壓縮開始日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataB

5、aseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=RAR.EXEA-R+strSql+.RAR+strSql+.BAKD:DataBaseBackDataCompressDataBaseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtPRINTLEN(strSqlCmd)PRINT(strSqlCmd)EXECmaster.dbo.X

6、P_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=ECHO壓縮日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataBaseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUT操作如圖二:完成后我們可以看到操作步驟的對話框，如圖三:圖三數(shù)據(jù)完全備份的步驟我們對照上圖，注意兩點，第一個是步驟1“成功時”

7、這一列的顯示，當(dāng)成功的時候轉(zhuǎn)到下一步，失敗時”當(dāng)失敗的時候失敗后退出，步驟2“成功時”當(dāng)成功的時候成功后退出，失敗時當(dāng)失敗時失敗后退出。確保兩個步驟對數(shù)據(jù)操作的正常。再執(zhí)行調(diào)度”一欄，主要實現(xiàn)在什么時候執(zhí)行這些作業(yè)，我們定在每周日一點的時候開始執(zhí)行，如圖四:這樣就可以建立好對數(shù)據(jù)庫的整個完全備份了。有時我們數(shù)據(jù)在遭到破壞的時候，而在恢復(fù)到上次的整個備份時，就會產(chǎn)生很多丟失的數(shù)據(jù)了，這時我們就必須還得建立另外一種備份的機制一差異備份。步驟還和上面一樣，我們建立一個作業(yè)，命名為“MyDb差異備份”，在步驟里面同樣是建立兩個步驟，分別是差異備份和差異壓縮，步驟一在命令框中輸入內(nèi)容如下：DECLARE

8、strSqlVARCHAR(IOOO),strSqlCmdVARCHAR(IOOO),timeDateDiffINTSETtimeDateDiff=DATEDIFF(week,O,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeDateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-備份目錄及備份的文件頭+CONVERT(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全備份日期+_0100-完全備份

9、時間+差異備份+_+CONVERT(CHAR(8),GETDATE(),112)-差異備份日期+_0300-差異備份時間SETstrSqlCmd=strSql+.BAK-備份文件的擴展名BACKUPDATABASEwebEIMS2008TODISK=cSqlCmdWITHINIT,NOUNLOADDIFFERENTIAL,NAME=NMyDb差異備份,NOSKIP,STATS=10,NOFORMAT我們可以看到，差異備份除了文件名命名格式不一樣外，就在備份執(zhí)行SQL語句時增加了了下DIFFERENTIAL參數(shù)，然后再執(zhí)行。步驟二在命令框中執(zhí)行如下：DECLAREstrSqlVARCHAR(IO

10、OO),strSqlCmdVARCHAR(IOOO),timeDateDiffINT,strWeekDayVARCHAR(20)SETtimeDateDiff=DATEDIFF(week,0,GETDATE()SETtimeDateDiff=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THENtimeDateDiff-1ELSEtimeDateDiffENDSETstrSql=D:DataBaseBackDataMyDb_-備份目錄及備份的文件頭+CONVERT(CHAR(8),DATEADD(week,timeDateDiff,0),112)-完全備份日期+_010

11、0-完全備份時間+差異備份+_+CONVERT(CHAR(8),GETDATE(),112)-差異備份日期+_0300-差異備份時間SETstrWeekDay=CASEDATEPART(WEEKDAY,GETDATE()WHEN1THEN星期天WHEN2THEN星期一WHEN3THEN星期二WHEN4THEN星期三WHEN5THEN星期四WHEN6THEN星期五WHEN7THEN星期六ENDSETstrSqlCmd=ECHO壓縮開始日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataBa

12、seMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=RAR.EXEA-R+strSql+.RAR+strSql+.BAKD:DataBaseBackDataCompressDataBaseMyDb_+CONVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtPRINTLEN(strSqlCmd)PRINT(strSqlCmd)EXECmaster.dbo.XP

13、_CMDSHELLstrSqlCmd,NO_OUTPUTSETstrSqlCmd=ECHO壓縮結(jié)束日期：+CONVERT(VARCHAR(20),GETDATE(),120)+strWeekDay+D:DataBaseBackDataCompressDataBaseMyDb_+C0NVERT(CHAR(6),DATEADD(week,timeDateDiff,0),112)+.txtEXECmaster.dbo.XP_CMDSHELLstrSqlCmd,NO_OUTPUT這時我們已經(jīng)建立好了步驟，只是現(xiàn)在建立作業(yè)調(diào)度的時候有些變化，我們看圖五：圖五建立差異備份作業(yè)調(diào)度對比完全備份建立的作業(yè)調(diào)度

14、，在這里我們可以看到，我們選擇的時間是除了周日以外的每天夜里3點的時候，自動執(zhí)行此次調(diào)度。當(dāng)然時間是自己靈活分配的，如數(shù)據(jù)發(fā)生的變化比較大，我們可以選擇每天，然后頻率選擇發(fā)生周期性短一點，這樣我們數(shù)據(jù)在遭到破壞的時候，我們就可以及時的恢復(fù)了。如果在SQLServer2000中，我們可以建立如上的作業(yè)就可以對數(shù)據(jù)進行備份了，而對于SQLServer2005，還有一點細微的變化，因為它默認是不支持xp_cmdshell執(zhí)行命令的，SQLServer已封鎖元件xp_cmdshell的程序sys.xp_cmdshell之存取，因為此元件已經(jīng)由此伺服器的安全性組態(tài)關(guān)閉。系統(tǒng)管理員可以使用sp_confi

15、gure來啟用xp_cmdshell的使用。所以我們得恢復(fù)其執(zhí)行命令：用下面一句話就可以了解決了。EXECsp_configureshowadvancedoptions,1;RECONFIGURE;EXECsp_configurexp_cmdshell,RECONFIGURE;利用WINRAR實現(xiàn)SQLSERVER自動壓縮的方法下面為您介紹的方法可以實現(xiàn)SQLSERVER自動壓縮，有了SQLSERVER自動壓縮,就不必每天再浪費時間進行手動的壓縮了。當(dāng)SQL數(shù)據(jù)庫大于2G時XP_MAKECAB擴展存儲過程壓縮失敗，結(jié)合WINDOWS操作系統(tǒng)的任務(wù)計劃，建立命令行下的批處理文件(.BAT),實現(xiàn)

16、每天SQLSERVER自動壓縮,如下：1、SQLSERVER自動備份2、COPYWINRAR目錄到以下批處理文件所在的目錄3、用文本編輯器建立批處理文件(.BAT),如下內(nèi)容echooffecho.echo.coloraclsechooffecho.echo歡迎來到數(shù)據(jù)庫備份壓縮批處理echo.echo.echo.echo.正在自動壓縮備份數(shù)據(jù).echoecho.echo.echo.echo.echo.echooffifnotexistzsimcmis_db_%date:0,4%date:5,2%date:8,2%1830.bakgotoexistfile-檢查是否存在未被壓縮數(shù)據(jù)庫備份文件g

17、otobackup:backupecho已經(jīng)找到備份好的備份文件zsimcmis_db_%date:0,4%date:5,2%date:8,2%1830.bakecho.echo!提示:正在壓縮備份文件.!！echo.echo!提示:正在將備份文件zsimcmis_db_%date:0,4%date:5,2%date:8,2%1830.bak壓縮到G:Cabfileszsimcmis_db_%date:0,4%date:5,2%date:8,2%2000.RAR文件中-只是顯示屏幕而已，中間的文件名沒實際用處WinRARwinrara-as-ibckG:Cabfileszsimcmis_db_.rar-m3-agyyyym