第一章、關於SQL Server資料庫的備份與還原(sp_addumpdevice、backup、Restore）

標籤：

在sql server資料庫中，備份與還原都只能在伺服器上進行，備份的資料檔案在伺服器上，還原的資料檔案也只能在伺服器上，當在非伺服器的機器上啟動sql server用戶端的時候，也可以通過該用戶端來備份與還原資料庫，但是這種操作實質是在伺服器上進行的，備份的資料檔案在伺服器上，還原的資料檔案也只能在伺服器上，這個原則不會變，只是使用了用戶端的一個工具來操作這個過程而已。

1.1、備份資料庫

備份資料庫有兩種方式：

第一種是在企業管理器中，利用工具對資料庫進行備份，這種備份的檔案只會有一個，即以.bak結尾的檔案。這種方式下對一個資料庫進行備份的時候，可以任意的命名備份檔案和尾碼，還可以對已備份完成的備份檔案進行名字和尾碼名的修改，都不會影響檔案的恢複，但是備份檔案內部已經記錄了原來的資料庫的.mdf和.ldf檔案的名字和儲存的路徑。備份的時候，如果目標檔案夾中已經存在了與備份檔案名同名的檔案，則有兩個選項，附加和覆蓋，一般選擇覆蓋.建議：以原資料庫名字為備份檔案的檔案名稱字，並且以 .bak作為尾碼名.

第二種方式是直接找資料庫的data檔案夾，直接將.mdf和.ldf檔案拷貝儲存。

 

1.2、還原資料庫

還原資料庫有兩種方式：

第一種是還原.bak檔案，當確定了備份的資料庫之後，就可以利用企業管理器來還原了。還原的新資料庫名可以是任意的，在option選項裡面可以根據當前機器中安裝Sql Server 2005 的路徑來修改路徑，甚至可以修改.mdf和.ldf檔案的名字，名字只是標識符，任意修改都可以，編譯器會把.mdf和.ldf檔案與新資料庫名聯絡起來的。這樣還原後，就產生了.mdf和.ldf檔案，原來的備份資料檔案就可以刪除了。還原的時候，如果目標檔案夾中已經存在了與還原檔案名稱同名的檔案，可以選擇覆蓋原來的資料檔案。建議：還原後還是以原資料庫名為還原資料庫名，option選項裡面不要改名字，只有在有必要的時候修改路徑即可.

第二種是還原.mdf和.ldf檔案，還原的時候可以用Attach來把.mdf和.ldf檔案附加上，然後命名新的資料庫名，但是這兩個檔案是不能被刪除的，否則就失去了資料來源了。建議：把.mdf和.ldf檔案放到Data檔案夾中.還原的資料庫名字與原來的資料庫名字相同.

 

1.3、與Database Backup和還原相關的兩個重要的檔案夾：

……\Microsoft SQL Server\MSSQL.1\MSSQL\Backup  

-------存放備份的資料檔案

……\Microsoft SQL Server\MSSQL.1\MSSQL\Data

                                            --------存放.mdf和.ldf檔案

 

1.4、通過sql  server的命令來備份還原資料庫

我們通常備份資料庫時，需要通過sql server用戶端登入資料庫伺服器去備份和恢複，這樣很不方便，其實SQL SERVER內建的命令就可以備份與還原資料庫。這些命令可以在sql server用戶端的sql指令碼視窗直接執行，也可以很通過ADO.Net調用這些命令實現遠程備份和恢複資料庫。

（1）、備份還原資料庫的命令

備份：

BACKUP DATABASE‘被備份的資料庫名‘TO DISK = ‘備份檔案路徑‘;

--注意：被備份的資料庫名必須是存在的，否則會出錯

還原：

--將資料庫置於離線狀態

ALTER DATABASE‘被恢複的資料庫名‘SET OFFLINE WITH ROlLBACK IMMEDIATE;

--恢複資料庫

RESTORE DATABASE ‘被恢複的資料庫名‘FROM DISK = ‘還原檔案路徑（源檔案）‘;

--將資料庫置於線上狀態

ALTER DATABASE‘被恢複的資料庫名‘SET ONLINE WITH ROlLBACK IMMEDIATE;

--注意：被恢複的資料庫名必須是存在的，並且該資料庫將會被覆蓋掉，還原檔案路徑（源檔案）必須是存在的，另外，在執行前後的兩個ALTER語句的時候，所選擇的資料庫必須是master，

有時候為了保險起見，ALTER語句可以不要。

（2）、命令應用

A、可以直接在sql server用戶端的sql表單中執行命令。

B、可以在資料庫上寫預存程序，然後配置JOB，定期調用這個預存程序。

C、通過應用程式的ADO.NET來執行這些命令或者命令組成的預存程序，實現應用程式控製備份還原資料庫，這些應用程式可以寫成服務，定期調用。

（3）、C#案例

//備份資料庫

string sql = "backup database qis2006 to disk = ‘" + Server.MapPath("").ToString() +"\\"

+ bakname+ System.DateTime.Now.DayOfYear.ToString() + System.DateTime.Now.Millisecond.ToString() +".bak‘";//備份檔案名

Access acc = new Access();

acc.ExecuteNoneQuery(sql);

//還原資料庫

string sql = "Alter Database qis2006 Set Offline with Rollback immediate;";

sql +=  "restore database qis2006 from disk = ‘" ;

sql += Server.MapPath("").ToString() +"\\";

sql += bakname + "‘";  //bakname 是備份檔案名

sql += "Alter Database qis2006 Set OnLine With rollback Immediate;";

try

{

//串連 master資料庫 ;

//執行 sql  語句;

Response.Write("<script language=javascript>alert(‘資料恢複成功！‘);</script>");

}

catch(Exception ex)

{

Response.Write("<script language=javascript>alert(‘資料恢複失敗！‘);</script>");

this.Label2.Text = ex.ToString();

}

（4）、預存程序案例

//備份資料庫

create   proc   backup_database    

 (  

  @device_name   varchar(80),--備份裝置名 

  @file_name   varchar(125),  --備份檔案名 

  @devicelog_name   varchar(85),--記錄備份裝置名稱 

  @filelog_name   varchar(130)  --記錄備份檔案名稱 

 )  

as 

begin

  --建立資料庫的備份邏輯裝置 

  exec   sp_addumpdevice   ‘disk‘,   @device_name  ,@file_name  

--建立裝置邏輯名 

  exec   sp_addumpdevice   ‘disk‘,   @devicelog_name,   @filelog_name    

  backup   database   testdb   to   @device_name   --備份資料庫 

  backup   log   testdb   to   @devicelog_name     --備份日誌 

end

 

//還原資料庫

create   proc   restore_database  

 (  

  @device_name   varchar(80),  

  @devicelog_name   varchar(85)  

 )  

as

begin 

  Restore   database   testdb   from     @device_name  

  Restore   log   testdb     @devicelog_name

end

 sp_addumpdevice備份裝置是指為了防止裝置系統運轉中由於某台關鍵或易損裝置的故障造成整個系統癱瘓，專門預備用於替換故障裝置的裝置。備份裝置有時也簡稱為“備機”。備份裝置可分為“熱備”和“冷備”。熱備是指與目標裝置共同運轉，當目標裝置發生故障或停機時，熱備裝置立即承擔起故障裝置的工作任務；冷備是指當目標裝置發生故障或停機後，冷備裝置才開始由停機等待狀態進入啟動運轉狀態，並承擔起故障裝置的工作任務。sp_addumpdevice 會將一個備份裝置添加到 sys.backup_devices 目錄檢視中。然後便可以在 BACKUP 和 RESTORE 語句中邏輯引用該裝置。sp_addumpdevice 不執行對物理裝置的任何訪問。只有在執行 BACKUP 或 RESTORE 語句後才會訪問指定的裝置。建立一個邏輯備份裝置可簡化 BACKUP 和 RESTORE 語句，在這種情況下指定裝置名稱將代替使用 "TAPE =" 或 "DISK =" 子句指定裝置路徑。

 

sp_addumpdevice 文法：

sp_addumpdevice [ @devtype = ] ‘device_type‘          , [ @logicalname = ] ‘logical_name‘          , [ @physicalname = ] ‘physical_name‘       [ , { [ @cntrltype = ] controller_type |           [ @devstatus = ] ‘device_status‘ }       ]

eg.

添加了一個名為 mydiskdump 的磁碟備份裝置，其實體名稱為 c:\dump\dump1.bak。

USE master; GO EXEC sp_addumpdevice ‘disk‘, ‘mydiskdump‘, ‘c:\dump\dump1.bak‘;

 

第一章、關於SQL Server資料庫的備份與還原(sp_addumpdevice、backup、Restore）