SQL Server資料庫的備份與還原（.NET項目）

     空閑之餘來到了部落格園，這次是我第一次寫隨筆 ，希望大家多多支援，我目前最關注的是資料庫，也想在畢業來臨之際找到我理想的工作，希望在今後的人生中在資料庫方面走得更遠！下面將我學習的資料庫知識分享給大家，希望在這個平台能不斷地學習新的技術，相信我們能共同進步！O(∩_∩)O~

--備份的裝置有2種(臨時裝置和永久裝置) 注意：預設下的備份類型是完整備份  
--第一種：
backup database Company to disk='d:\backup\1.bak'--臨時裝置
/*如果這裡不指定明確路徑的話（如：backup database company to disk='backup\1.bak'），
那麼備份的資料庫將會自動備份到系統指定的目錄下：
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup*/

--第二種：
/*第一步首先建立永久備份裝置 （系統內建的預存程序）在master 資料庫中就會找到1： 
*/
--執行語句如：
exec sp_addumpdevice 'disk','disk_company','D:\2.bak'--永久裝置

--執行結果就會出現2：
--多了一個備份裝置：disk_company
--第二步：
backup database company to disk_company with noinit --預設表示追加(不覆蓋)

 --好了  備份完成 ！ 
--現在我來還原資料庫(我用的是第一種方法備份的，所以我要第一種方法來還原) ，
--原來的資料3：
--經過我手動刪除幾個表後的資料庫4:

--執行語句：
restore database Company from disk='d:\backup\1.bak' --注意備份到哪裡去就要從還原哪裡來
--執行後會出現什麼呢？請看錯誤訊息：
/*訊息 3159，層級 16，狀態 1，第 1 行
尚未備份資料庫 "company" 的日誌尾部。如果該日誌包含您不希望丟失的工作，請使用 BACKUP LOG WITH NORECOVERY 備份該日誌。請使用 RESTORE 語句的 WITH REPLACE 或 WITH STOPAT 子句來只覆蓋該日誌的內容。
訊息 3013，層級 16，狀態 1，第 1 行
RESTORE DATABASE 正在異常終止。*/
--為什麼會出現這種錯誤呢  我們可以從錯誤的訊息中找到解決方案！
--我們去看看這個資料庫的復原模式5：

--因為的復原模式是 ：完整； 所以它的功能是將所有事務都寫入日誌，把所有資料庫檔案的都還原
--方案一：我現在只是還原的資料庫檔案 並沒有備份記錄檔 所以我再去備份記錄檔
backup log Company to disk='d:\backup\2.bak' --備份記錄檔
restore database Company from disk='d:\backup\1.bak'  --再去還原資料庫
restore log Company from disk='d:\backup\2.bak'--這步可有可無

--執行的結果為：6：

--方案二 由於錯誤訊息中的提示：請使用 RESTORE 語句的 WITH REPLACE 或 WITH STOPAT 子句來只覆蓋該日誌的內容。
---訊息 3013，層級 16，狀態 1，第 1 行  所以 我想到去覆蓋掉記錄檔  雖然復原模式是完整的 但是我要覆蓋它 也是可以的
--只是對資料庫的操作沒有日誌沒有完全還原而已 也是可以的
--執行語句如下：
restore database Company from disk='d:\backup\1.bak' WITH REPLACE

--執行成功
/*已為資料庫 'Company'，檔案 'Company_Data' (位於檔案 1 上)處理了 224 頁。
已為資料庫 'Company'，檔案 'Company_Log' (位於檔案 1 上)處理了 5 頁。
RESTORE DATABASE 成功處理了 229 頁，花費 0.225 秒(8.319 MB/秒)。*/

--方案三：我想了一下  我只是備份了資料庫，但是沒有備份記錄檔  根據備份還原的原理
/*
   復原模式                說明
   簡單           不用備份的交易記錄，即可還原
                  用於小型資料庫和不經常更改的資料庫
   完整           所有事務都被記錄到日誌中
                  保留所有日誌，直到交易記錄備份
                  用於生產資料庫
 大容量日誌       完整復原模式的補充
                  不將大容量日誌操作寫入日誌
*/

--所以我修改了這個資料庫的屬性中的復原模式  改為 “簡單”
--7:

--我直接執行還原的代碼
restore database Company from disk='d:\backup\1.bak'
/*執行結果：
已為資料庫 'Company'，檔案 'Company_Data' (位於檔案 1 上)處理了 224 頁。
已為資料庫 'Company'，檔案 'Company_Log' (位於檔案 1 上)處理了 5 頁。
RESTORE DATABASE 成功處理了 229 頁，花費 0.224 秒(8.356 MB/秒)。*/

--三種還原的解決方案成功

--但是這用到項目中資料庫正在使用的話是不成功的 ，它具有排它性 ！
--所以我寫了一個預存程序來解決，這也是很多程式員花了很久才解決的問題
--代碼用法如下 ：有附帶的例子下載
--建立預存程序　killspid   

create    proc    killspid    (@dbname    varchar(20))     
as     
begin     
declare    @sql    nvarchar(500)     
declare    @spid    int     
set    @sql='declare    getspid    cursor    for         
select    spid    from    sysprocesses    where    dbid=db_id('''+@dbname+''')'     
exec    (@sql)     
open    getspid     
fetch    next    from    getspid    into    @spid     
while    @@fetch_status    <    >-1     
begin     
exec('kill    '+@spid)     
fetch    next    from    getspid    into    @spid     
end     
close    getspid     
deallocate    getspid     
end    

GO

--說明：
--1.此預存程序應寫在Master中;
--2.以上代碼就是解決因為資料庫正在使用,所以未能獲得對資料庫的排它訪問權的問題,不然系統有時會報錯；

我附帶一個簡單的備份還原的例子 (ASP.NET +SQL SERVER 2005 的運行環境)

若經過多次反覆嘗試(包括關閉所有資料庫連接、重啟SQL Server服務、重啟電腦)均以失敗告終，解決此問題的關鍵SQL語句：

ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE

有時間我會寫一篇關於怎樣還原到指定時間點的例子 我會採用完整備份 差異備份 記錄備份  我也會把原資料庫檔案也損壞掉 再去還原的！ ！！！！！！！！！！