如何讓SQL Server數據庫自動備份并壓縮
當前位置：點晴教程→知識管理交流
→『 技術文檔交流 』
我們通常在維護數據庫的時候，都會建立一個備份的機制，在SQL Server中，我們就可以通過如下的方法來實現： 如果SQL Server代理沒有啟動，我們先把其啟動，然后新建立一個作業(yè)，名稱命名為“MyDb完全備份”，在分類下面選擇“數據庫維護”，然后新建立作業(yè)第一個步驟，步驟名為“對數據進行完全備份”，然后在命令框中輸入如下的SQL代碼： DECLARE @strSql VARCHAR(1000), @strSqlCmd VARCHAR(1000), @timeDateDiff INT SET @timeDateDiff = DATEDIFF(week,0,GETDATE()) SET @timeDateDiff = CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN @timeDateDiff -1 ELSE @timeDateDiff END SET @strSql='D:\DataBase\BackData\MyDb_' -- 備份目錄及備份的文件頭 +CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全備份日期 +'_0100' -- 完全備份時間 +'完全備份' SET @strSqlCmd= @strSql+'.BAK' --備份文件的擴展名 BACKUP DATABASE [MyDb]TO DISK = @strSqlCmd WITH INIT, NOUNLOAD, NAME = N'MyDb 備份',NOSKIP, STATS = 10, NOFORMAT 操作如圖一：
圖一 建立作業(yè)對數據庫進行完全備份 然后開始執(zhí)行對數據庫的壓縮，在步驟中再新建一個作業(yè)，步驟名為“壓縮數據庫”，然后在命令框中輸入如下的SQL代碼： DECLARE @strSql VARCHAR(1000), @strSqlCmd VARCHAR(1000), @timeDateDiff INT, @strWeekDay VARCHAR(20) SET @timeDateDiff=DATEDIFF(week,0,GETDATE()) SET @timeDateDiff=CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN @timeDateDiff-1 ELSE @timeDateDiff END SET @strSql='D:\DataBase\BackData\MyDb_' -- 備份目錄及備份的文件頭 +CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112) -- 完全備份日期 +'_0100' -- 完全備份時間 +'完全備份' SET @strWeekDay= CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '星期天' WHEN 2 THEN '星期一' WHEN 3 THEN '星期二' WHEN 4 THEN '星期三' WHEN 5 THEN '星期四' WHEN 6 THEN '星期五' WHEN 7 THEN '星期六' END SET @strSqlCmd='ECHO 壓縮開始日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt' exec master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT SET @strSqlCmd='RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb__'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt' PRINT LEN(@strSqlCmd) PRINT (@strSqlCmd) exec master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT SET @strSqlCmd='ECHO 壓縮日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt' exec master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT 操作如圖二：
圖二 建立作業(yè)對數據進行壓縮 完成后我們可以看到操作步驟的對話框，如圖三：
圖三 數據完全備份的步驟 我們對照上圖，注意兩點，第一個是步驟1“成功時”這一列的顯示，當成功的時候轉到下一步，“失敗時”當失敗的時候失敗后退出，步驟2“成功時”當成功的時候成功后退出，“失敗時”當失敗時失敗后退出。確保兩個步驟對數據操作的正常。 再執(zhí)行“調度”一欄，主要實現在什么時候執(zhí)行這些作業(yè)，我們定在每周日一點的時候開始執(zhí)行，如圖四：
圖四 建立調度 這樣就可以建立好對數據庫的整個完全備份了。 有時我們數據在遭到破壞的時候，而在恢復到上次的整個備份時，就會產生很多丟失的數據了，這時我們就必須還得建立另外一種備份的機制—差異備份。 步驟還和上面一樣，我們建立一個作業(yè)，命名為“MyDb差異備份”，在步驟里面同樣是建立兩個步驟，分別是差異備份和差異壓縮，步驟一在命令框中輸入內容如下： DECLARE @strSql VARCHAR(1000), @strSqlCmd VARCHAR(1000), @timeDateDiff INT SET @timeDateDiff = DATEDIFF(week,0,GETDATE()) SET @timeDateDiff = CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN @timeDateDiff -1 ELSE @timeDateDiff END SET @strSql='D:\DataBase\BackData\MyDb_' -- 備份目錄及備份的文件頭 +CONVERT(CHAR(8),DATEADD(week, @timeDateDiff,0),112) -- 完全備份日期 +'_0100' -- 完全備份時間 +'差異備份' +'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差異備份日期 +'_0300' -- 差異備份時間 SET @strSqlCmd= @strSql+'.BAK' --備份文件的擴展名 BACKUP DATABASE [webEIMS2008] TO DISK = @cSqlCmd WITH INIT, NOUNLOAD, DIFFERENTIAL, NAME = N'MyDb差異備份', NOSKIP, STATS = 10, NOFORMAT 我們可以看到，差異備份除了文件名命名格式不一樣外，就在備份執(zhí)行SQL語句時增加了了下DIFFERENTIAL參數，然后再執(zhí)行。 步驟二在命令框中執(zhí)行如下： DECLARE @strSql VARCHAR(1000), @strSqlCmd VARCHAR(1000), @timeDateDiff INT, @strWeekDay VARCHAR(20) SET @timeDateDiff=DATEDIFF(week,0,GETDATE()) SET @timeDateDiff=CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN @timeDateDiff-1 ELSE @timeDateDiff END SET @strSql='D:\DataBase\BackData\MyDb_' -- 備份目錄及備份的文件頭 +CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112) -- 完全備份日期 +'_0100' -- 完全備份時間 +'差異備份' +'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差異備份日期 +'_0300' -- 差異備份時間 SET @strWeekDay=CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '星期天' WHEN 2 THEN '星期一' WHEN 3 THEN '星期二' WHEN 4 THEN '星期三' WHEN 5 THEN '星期四' WHEN 6 THEN '星期五' WHEN 7 THEN '星期六' END SET @strSqlCmd='ECHO 壓縮開始日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt' exec master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT SET @strSqlCmd='RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt' PRINT LEN(@strSqlCmd) PRINT (@strSqlCmd) exec master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT SET @strSqlCmd='ECHO 壓縮結束日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+' >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt' exec master.dbo.XP_CMDSHELL @strSqlCmd,NO_OUTPUT 這時我們已經建立好了步驟，只是現在建立作業(yè)調度的時候有些變化，我們看圖五：
圖五 建立差異備份作業(yè)調度 對比完全備份建立的作業(yè)調度，在這里我們可以看到，我們選擇的時間是除了周日以外的每天夜里3點的時候，自動執(zhí)行此次調度。 當然時間是自己靈活分配的，如數據發(fā)生的變化比較大，我們可以選擇每天，然后頻率選擇發(fā)生周期性短一點，這樣我們數據在遭到破壞的時候，我們就可以及時的恢復了。 如果在SQL Server2000中，我們可以建立如上的作業(yè)就可以對數據進行備份了，而對于SQL Server2005，還有一點細微的變化，因為它默認是不支持xp_cmdshell執(zhí)行命令的，SQL Server 已封鎖元件 'xp_cmdshell' 的 程序 'sys.xp_cmdshell' 之存取，因為此元件已經由此伺服器的安全性組態(tài)關閉。系統(tǒng)管理員可以使用sp_configure來啟用 'xp_cmdshell' 的使用。所以我們得恢復其執(zhí)行命令，用下面命令就可以了解決了。 -- 開啟 xp_cmdshell exec sp_configure 'show advanced options', 1; RECONFIGURE; exec sp_configure 'xp_cmdshell', 1; RECONFIGURE; -- 關閉 xp_cmdshell exec sp_configure 'show advanced options', 1; RECONFIGURE; 該文章在 2023/10/10 11:12:58 編輯過
|
關鍵字查詢
相關文章
正在查詢... 
|
||||||
400 186 1886
