# SQL Server 快速產生還原資料庫 T-SQL 指令碼的技巧

去年曾經分享過一篇【[觀念釐清：SQL Server 完整備份、差異備份、交易記錄備份](/post/2010/04/21/SQL-Server-Full-Differential-Transaction-Backup.aspx)】解釋 SQL Server 備份觀念，看懂的人應該能清楚理解資料庫的備份過程與依序還原資料庫的方法，我以往都會認為這種依序還原資料庫的方法實在太沒效率，真的遇到資料庫災難的時候需要花不少時間準備還原資料庫的 T-SQL 指令碼然後才能批次執行將資料庫還原，不過最近意外發現原來在 Management Studio 有個非常棒的小技巧可以幫我快速產生還原資料庫的 T-SQL 指令碼，讓你不管是本機資料庫還原或異機資料庫還原都能非常快速的完成！

假設我們每週做一次完整備份，每天做一次差異備份，每 15 分鐘做一次交易記錄備份，這樣的備份設定透過維護計畫很容易就能辦到，但是卻沒有人教你怎樣能夠快速的還原這些曾經備份過的資料庫。

我先假設現在是某一個星期五的中午發生了資料庫檔案異常的狀況（也許是該資料庫檔案毀損），那麼你至少需要找到最近一次的完整備份、最近一次的差異備份，以及從差異備份之後的所有交易記錄備份 (假設有 28 個交易記錄檔)，如此一來我們必須要執行 [RESTORE](http://msdn.microsoft.com/zh-tw/library/ms186858.aspx) 還原指令 30 次才能完整還原資料庫到毀損前的那個時間點，當然一個一個準備這些 T-SQL 非常沒效率，但還是不得不這樣做。

我前陣子意外發現原來有個非常方便的方式能夠透過 Management Studio 快速產生資料庫還原指令碼，只要你的原本資料庫的 msdb 沒有毀損，都能透過以下方法快速還原資料庫，以下是操作的步驟說明。

先開啟 Management Studio 並開啟還原資料庫工作視窗：

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/bbed97f0ed5a081b4898-image_962.png)

開啟還原資料庫視窗後在 \[還原的來源\] 段落這邊選取「來源資料庫」(如下圖示)， 在這裡可以挑選的資料庫就是曾經在這一台電腦備份過的資料庫，如果你沒有在這一台 SQL Server 備份過的話，這裡是選不到任何資料的。選取完資料庫之後，你會發現下方的「選取要還原的備份組」會出現一大堆曾經備份過的紀錄，而這些就是該資料庫所有 **備份組** (backupset) 的備份歷史紀錄，並依照備份的時間列出來，而厲害的是 Management Studio 會聰明的幫你預設勾選所有能夠還原的項目，他會幫你自動選取最近一次的完整備份、最近一次的差異備份以及從差異備份之後的所有交易記錄備份，請參考以下兩張圖片的示意：

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/de8e72d2d3a47b24d2c7-image_963.png)

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/503d4e1dd2815d249208-image_964.png)

備註：你如果不打算將資料庫還原到最新版，也可以少勾選一些選項。

從上圖你可以發現，這些備份的資料其實都是透過維護計畫設定好的，每一個名稱其實就代表著一個備份檔案，如果要手動撰寫這些指令碼真的挺花時間的，因為每個檔案你都要寫一條 RESTORE 還原指令！

最後，我們先不要按下視窗右下角的「確定」鍵，而改按下左上角的「指令碼」按鈕：  
( 此技巧之前也有透過【[查詢透過 Management Studio 的 GUI 所執行的 T-SQL 指令](/post/2008/06/05/Find-out-T-SQL-commands-generated-by-SQL-Server-Management-Studio.aspx)】文章分享過 )

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/f695b0663e69f85ca1c1-image_965.png)

產生指令碼之後，你就會看到非常完整的還原資料庫 T-SQL 指令碼，這些指令碼剛好也可以讓你學習到資料庫還原時的細部過程，不僅僅只有教學意義，重點是你要還原資料庫時也更快、更方便了！

 ![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/8e1e3db6c5e0fba7f159-image_966.png)

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/0e41f38820ef1ff25081-image_967.png)

最後要執行還原動作只要按下 \[執行\] 按鈕即可，唯一要特別注意的是透過這種方法產生的指令碼，其資料庫備份檔的路徑都是「備份資料庫時當下的備份目的路徑」，如果你的備份檔所在目錄與檔名沒有改變的話是可以直接執行，但如果備份檔有被移動到其他目錄的話，記得都要修改過才能執行。

我個人認為透過這個技巧，非常適合套用在某些情境上：

-   將毀損的資料庫還原到同一台 SQL Server 的原資料庫

-   還原資料庫時可以設定要將資料庫還原到任意一個時間點

-   將資料庫還原到同一台 SQL Server 的不同資料庫（建立測試資料庫之用）

-   一樣也可以在還原資料庫時設定將資料庫還原到任意一個時間點

-   將資料庫還原到另外一台 SQL Server 上

-   只要將所有 SQL 備份檔批次移至另一台主機，再用本文章介紹的技巧產生 RESTORE 指令碼，再用文字編輯器批次修改掉備份檔的所在路徑即可！
-   請注意 [SQL 資料庫還原到到另一台後無法登入要怎麼解決](/post/2007/11/18/SQL-Backup-and-Restore-cause-ASPNET-Can-not-Login.aspx) 的問題

當然有種狀況十分棘手，那就是整台 SQL Server 主機掛掉的狀況，這時連 msdb 都不見了，自然連 **備份組** (backupset) 的紀錄也都消失殆盡，這種情況你只能一筆一筆的來建立 RESTORE 指令碼了。

工具方便歸方便，身為一位專業的 SQL DBA 還是要多加練習 T-SQL 的撰寫能力，以應變不時之需！

**相關連結**

-   [觀念釐清：SQL Server 完整備份、差異備份、交易記錄備份](/post/2010/04/21/SQL-Server-Full-Differential-Transaction-Backup.aspx)
-   [查詢透過 Management Studio 的 GUI 所執行的 T-SQL 指令](/post/2008/06/05/Find-out-T-SQL-commands-generated-by-SQL-Server-Management-Studio.aspx)
-   [SQL 資料庫還原到到另一台後無法登入要怎麼解決](/post/2007/11/18/SQL-Backup-and-Restore-cause-ASPNET-Can-not-Login.aspx)
-   [在 SQL Server 中備份和還原資料庫](http://msdn.microsoft.com/zh-tw/library/ms187048.aspx)
-   [RESTORE (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms186858.aspx)
-   [使用備份媒體](http://msdn.microsoft.com/zh-tw/library/ms191248.aspx)
-   [backupset (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms186299.aspx)