如何讓SQL Server數(shù)據(jù)庫自動(dòng)備份并壓縮
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
我們通常在維護(hù)數(shù)據(jù)庫的時(shí)候，都會(huì)建立一個(gè)備份的機(jī)制，在SQL Server中，我們就可以通過如下的方法來實(shí)現(xiàn)： 如果SQL Server代理沒有啟動(dòng)，我們先把其啟動(dòng)，然后新建立一個(gè)作業(yè)，名稱命名為“MyDb完全備份”，在分類下面選擇“數(shù)據(jù)庫維護(hù)”，然后新建立作業(yè)第一個(gè)步驟，步驟名為“對(duì)數(shù)據(jù)進(jìn)行完全備份”，然后在命令框中輸入如下的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' -- 完全備份時(shí)間 +'完全備份' SET @strSqlCmd= @strSql+'.BAK' --備份文件的擴(kuò)展名 BACKUP DATABASE [MyDb]TO DISK = @strSqlCmd WITH INIT, NOUNLOAD, NAME = N'MyDb 備份',NOSKIP, STATS = 10, NOFORMAT 操作如圖一：
圖一 建立作業(yè)對(duì)數(shù)據(jù)庫進(jìn)行完全備份 然后開始執(zhí)行對(duì)數(shù)據(jù)庫的壓縮，在步驟中再新建一個(gè)作業(yè)，步驟名為“壓縮數(shù)據(jù)庫”，然后在命令框中輸入如下的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' -- 完全備份時(shí)間 +'完全備份' 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è)對(duì)數(shù)據(jù)進(jìn)行壓縮 完成后我們可以看到操作步驟的對(duì)話框，如圖三：
圖三 數(shù)據(jù)完全備份的步驟 我們對(duì)照上圖，注意兩點(diǎn)，第一個(gè)是步驟1“成功時(shí)”這一列的顯示，當(dāng)成功的時(shí)候轉(zhuǎn)到下一步，“失敗時(shí)”當(dāng)失敗的時(shí)候失敗后退出，步驟2“成功時(shí)”當(dāng)成功的時(shí)候成功后退出，“失敗時(shí)”當(dāng)失敗時(shí)失敗后退出。確保兩個(gè)步驟對(duì)數(shù)據(jù)操作的正常。 再執(zhí)行“調(diào)度”一欄，主要實(shí)現(xiàn)在什么時(shí)候執(zhí)行這些作業(yè)，我們定在每周日一點(diǎn)的時(shí)候開始執(zhí)行，如圖四：
圖四 建立調(diào)度 這樣就可以建立好對(duì)數(shù)據(jù)庫的整個(gè)完全備份了。 有時(shí)我們數(shù)據(jù)在遭到破壞的時(shí)候，而在恢復(fù)到上次的整個(gè)備份時(shí)，就會(huì)產(chǎn)生很多丟失的數(shù)據(jù)了，這時(shí)我們就必須還得建立另外一種備份的機(jī)制—差異備份。 步驟還和上面一樣，我們建立一個(gè)作業(yè)，命名為“MyDb差異備份”，在步驟里面同樣是建立兩個(gè)步驟，分別是差異備份和差異壓縮，步驟一在命令框中輸入內(nèi)容如下： 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' -- 完全備份時(shí)間 +'差異備份' +'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差異備份日期 +'_0300' -- 差異備份時(shí)間 SET @strSqlCmd= @strSql+'.BAK' --備份文件的擴(kuò)展名 BACKUP DATABASE [webEIMS2008] TO DISK = @cSqlCmd WITH INIT, NOUNLOAD, DIFFERENTIAL, NAME = N'MyDb差異備份', NOSKIP, STATS = 10, NOFORMAT 我們可以看到，差異備份除了文件名命名格式不一樣外，就在備份執(zhí)行SQL語句時(shí)增加了了下DIFFERENTIAL參數(shù)，然后再執(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' -- 完全備份時(shí)間 +'差異備份' +'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差異備份日期 +'_0300' -- 差異備份時(shí)間 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 壓縮結(jié)束日期: '+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 這時(shí)我們已經(jīng)建立好了步驟，只是現(xiàn)在建立作業(yè)調(diào)度的時(shí)候有些變化，我們看圖五：
圖五 建立差異備份作業(yè)調(diào)度 對(duì)比完全備份建立的作業(yè)調(diào)度，在這里我們可以看到，我們選擇的時(shí)間是除了周日以外的每天夜里3點(diǎn)的時(shí)候，自動(dòng)執(zhí)行此次調(diào)度。 當(dāng)然時(shí)間是自己靈活分配的，如數(shù)據(jù)發(fā)生的變化比較大，我們可以選擇每天，然后頻率選擇發(fā)生周期性短一點(diǎn)，這樣我們數(shù)據(jù)在遭到破壞的時(shí)候，我們就可以及時(shí)的恢復(fù)了。 如果在SQL Server2000中，我們可以建立如上的作業(yè)就可以對(duì)數(shù)據(jù)進(jìn)行備份了，而對(duì)于SQL Server2005，還有一點(diǎn)細(xì)微的變化，因?yàn)樗J(rèn)是不支持xp_cmdshell執(zhí)行命令的，SQL Server 已封鎖元件 'xp_cmdshell' 的 程序 'sys.xp_cmdshell' 之存取，因?yàn)榇嗽呀?jīng)由此伺服器的安全性組態(tài)關(guān)閉。系統(tǒng)管理員可以使用sp_configure來啟用 'xp_cmdshell' 的使用。所以我們得恢復(fù)其執(zhí)行命令，用下面命令就可以了解決了。 -- 開啟 xp_cmdshell exec sp_configure 'show advanced options', 1; RECONFIGURE; exec sp_configure 'xp_cmdshell', 1; RECONFIGURE; -- 關(guān)閉 xp_cmdshell exec sp_configure 'show advanced options', 1; RECONFIGURE; 該文章在 2023/10/10 11:12:58 編輯過
|
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
||||||
400 186 1886
