SQL SERVERDatabase Backup與複製（4）：讓SQL SERVER自動備份方法一

我們通常在維護資料庫的時候，都會建立一個 備份的機制，在 SQL Server中，我們就可以通過如下的方法來實現：

　　在SQL Server 2005資料庫中實現自動備份的具體步驟:

　　1、開啟SQL Server Management Studio

　　2、啟動SQL Server代理

　　3、點擊作業->新增作業

　　4、"常規"中輸入作業的名稱

　　5、建立步驟，類型選T-SQL，在下面的命令中輸入下面語句（紅色部分要根據自己的實際情況更改，D:sql2005ack改為自己的備份路徑，sq_test修改為想備份的資料庫的名稱）

　　

　　如果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代碼：

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

操作二：

　　圖二 建立作業對資料進行壓縮　　完成後我們可以看到操作步驟的對話方塊，三：

　　圖三 資料完全備份的步驟

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

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

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

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

　　步驟還和上面一樣，我們建立一個作業，命名為“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

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

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

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 這時我們已經建立好了步驟，只是現在建立作業調度的時候有些變化，我們看圖五： 　　圖五 建立差異備份作業調度　　對比完全備份建立的作業調度，在這裡我們可以看到，我們選擇的時間是除了周日以外的每天夜裡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;