SQL Server自動備份并壓縮

　這裡有兩個方法，其中第一個比較繁瑣但是比較詳細，第二個較為簡單，推薦用第二個方法！

 方法一：　

SQL Server代理沒有啟動，我們先把其啟動，然後建立立一個作業，名稱命名為“MyDb完全備份”，在分類下面選擇“資料庫維護”，然後建立立作業第一個步驟，步驟名為“對資料進行完全備份”，然後在指令框中輸入如下的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

 　　操作如圖一： 

　　然後開始執行對資料庫的壓縮，在步驟中再建立一個作業，步驟名為“壓縮資料庫”，然後在指令框中輸入如下的SQL代碼：

 ,@strWeekDay VARCHAR(20) 

SET @timeDateDiff= DATEDIFF(week,0,GETDATE())

SET @timeDateDiff= CASE DATEPART(WEEKDAY,GETDATE())

 WHEN 1 THEN @timeDateDiff-1

 +CONVERT(CHAR(8),DATEADD(week,@timeDateDiff,0),112)  -- 完全備份日期

 +'完全備份'

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)

SET @strSqlCmd= 'ECHO 壓縮日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+'  >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'

　　操作如圖二：

　　完成後我們可以看到操作步驟的對話框，如圖三，資料完全備份的步驟：

　　我們對照上圖，注意兩點，第一個是步驟1“成功時”這一列的顯示，當成功的時候轉到下一步，“失敗時”當失敗的時候失敗後退出，步驟2“成功時”當成功的時候成功後退出，“失敗時”當失敗時失敗後退出。確定兩個步驟對資料操作的正常。

　　再執行“排程”一欄，主要實作在什麼時候執行這些作業，我們定在每周日一點的時候開始執行，如圖四：

這樣就可以建立好對資料庫的整個完全備份了。

　　有時我們資料在遭到破壞的時候，而在恢複到上次的整個備份時，就會産生很多丢失的資料了，這時我們就必須還得建立另外一種備份的機制—差異備份。

　　步驟還和上面一樣，我們建立一個作業，命名為“MyDb差異備份”，在步驟裡面同樣是建立兩個步驟，分别是差異備份和差異壓縮，步驟一在指令框中輸入内容如下：

 +'差異備份'

 +'_'+CONVERT(CHAR(8),GETDATE(),112) -- 差異備份日期

 +'_0300'    -- 差異備份時間 

BACKUP DATABASE [webEIMS2008] 

 TO  DISK = @cSqlCmd WITH INIT 

 ,DIFFERENTIAL 

 ,NAME = N'MyDb差異備份'

　　我們可以看到，差異備份除了檔案名命名格式不一樣外，就在備份執行SQL語句時增加了了下DIFFERENTIAL參數，然後再執行。

　　步驟二在指令框中執行如下：

View Code

SET @strSql='D:\DataBase\BackData\MyDb_'   -- 備份目錄及備份的檔案頭

 +'_0300'    -- 差異備份時間

SET @strSqlCmd= 'RAR.EXE A -R '+@strSql+'.RAR '+@strSql+'.BAK >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'

SET @strSqlCmd= 'ECHO 壓縮結束日期: '+CONVERT(VARCHAR(20),GETDATE(),120)+' '+@strWeekDay+'  >> D:\DataBase\BackData\CompressDataBase\MyDb_'+CONVERT(CHAR(6),DATEADD(week,@timeDateDiff,0),112)+'.txt'

　　這時我們已經建立好了步驟，隻是現在建立作業排程的時候有些變化，我們看圖五：

對比完全備份建立的作業排程，在這裡我們可以看到，我們選擇的時間是除了周日以外的每天夜裡3點的時候，自動執行此次排程。

　　當然時間是自己靈活配置設定的，如資料發生的變化比較大，我們可以選擇每天，然後頻率選擇發生周期性短一點，這樣我們資料在遭到破壞的時候，我們就可以及時的恢複了。

　　如果在SQL Server2000中，我們可以建立如上的作業就可以對資料進行備份了，而對于SQL Server2005，還有一點細微的變化，因為它預設是不支援xp_cmdshell執行指令的，SQL Server 已封鎖元件 'xp_cmdshell' 的 程式 'sys.xp_cmdshell' 之存取，因為此元件已經由此伺服器的安全性組态關閉。系統管理者可以使用sp_configure來啟用 'xp_cmdshell' 的使用。是以我們得恢複其執行指令：

　　用下面一句話就可以了解決了。

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

利用自動壓縮WINRAR實作SQL SERVER

　　面為您介紹的方法可以實作SQL SERVER自動壓縮，有了SQL SERVER自動壓縮，就不必每天再浪費時間進行手動的壓縮了。

　　當SQL資料庫大于2G時XP_MAKECAB擴充存儲過程壓縮失敗，結合WINDOWS 作業系統的任務計劃，建立指令行下的批處理檔案(.BAT)，實作每天　SQL SERVER自動壓縮，如下：

　　1、SQL SERVER自動備份

　　2、COPY WINRAR目錄到以下批處理檔案所在的目錄

　　3、用文本編輯器建立批處理檔案（.BAT)，如下内容：

@echo off

echo.

color a

cls

echo                 歡迎來到資料庫備份壓縮批處理_dudumao

echo.                    ....正在自動壓縮備份資料....

echo ------------------------------------------------------------------------------

if not exist zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak goto existfile   --檢查是否存在未被壓縮資料庫備份檔案

goto backup

:backup

echo 已經找到備份好的備份檔案zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak

echo .

echo !!!提示:正在壓縮備份檔案....!!!

echo !!!提示:正在将備份檔案zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak壓縮到G:\Cabfiles\zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%2000.RAR檔案中.......   --隻是顯示螢幕而已,中間的檔案名沒實際用處

WinRAR\winrar a -as -ibck G:\Cabfiles\zsimcmis_db_.rar -m3 -agyyyymmddhhmmss zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak      --什麼不知道意思，WINRAR指令行的參數參考一下。

goto end

:existfile

echo !!!備份失敗!!!

echo 沒有找到已經備份好的備份檔案zsimcmis_db_%date:~0,4%%date:~5,2%%date:~8,2%1830.bak

pause

:end

echo 操作完畢

　　其中：XP   system　c:\> md   d:\%Date:~0,4%%Date:~5,2%%Date:~8,2%

　　　　　2k   system　c:\> md   d:\%Date:~4,4%%Date:~9,2%%Date:~12,2%

　

　　4、在WIN的任務計劃中，建立運作計劃，指令就是運作上以批處理檔案就是OK！不必每天手工壓縮了。。。

方法二：

　　建立兩個步驟，第一步備份出BAK資料庫檔案，第二步打包壓縮資料庫備份檔案後并删除原備份檔案

　　執行備份的指令：

declare @filename varchar(200)

set @filename='D:\'+convert(char(10),getdate(),120)+'.bak'  --設定備份檔案的路徑和檔案名

print @filename

backup database[DatabaseName] to disk=@filename with NOINIT,NOUNLOAD,NAME='backup',NOSKIP,STATS=10,NOFORMAT  --執行備份

　　壓縮并删除源檔案的指令：

declare @file varchar(200)

set @file='c:\winrar.exe a -ep -df D:\DatebaseName_'+convert(char(10),getdate(),120)+'.rar D:\'+convert(char(10),getdate(),120)+'.bak'  --将壓縮備份檔案并删除源檔案的winrar指令行指令

exec master..xp_cmdshell @file  --執行指令

　　winrar指令行的壓縮指令是：[winrar路徑] a [參數] [壓縮後的路徑] [需要壓縮的檔案路徑]，這是本文需要用到的指令格式。

　　a就是winrar壓縮檔案的指令參數

　　-ep參數的作用是不壓縮檔案的完整路徑，也就是指壓縮指定的檔案。

　　-df參數的作用就是壓縮後删除源檔案

　　後面指令的意思就是把D盤根目錄下以當天日期命名的備份檔案壓縮到D盤根目錄，并命名為當天日期的rar檔案

　　一般預設下winrar的程式都是安裝在C:\Program Files\WinRAR下的，但是由于其中的Program Files目錄名中有空格，CMD指令行中是不支援有空格的名字的，是以把程式複制到一個簡單的路徑友善調用