三種用來備份還原 SQL Server schema 與資料的做法

部份公司團隊在專案開發流程上有較多的環境需要建置，或是有時需要針對不同環境的資料及 schema 進行除錯與比對時就是件辛苦的事了，這樣的工作我們一定需要透過工具來處理，避免人為的疏忽也讓人力去做更有價值的工作

但工具的安裝在許多環境是不被允許的，或是有些環境的權限比較嚴謹，我們的工具無法直接存取目接資料庫或是沒有目標資料庫的權限，這時我們就得考慮將資料庫備份回其他環境進行還原才能完成比對工作

所以來介紹工作中常使用的幾種 SQL Server 備份還原方式及優缺點

一、使用 .bak 檔案

  1. 備份

    • 目標資料庫 –> 右鍵 –> Tasks –> Back Up..

      1backup

    • 備份選項

      • 主要是指定備份檔案名稱

        2options

    • 備份成功

      3success

  2. 還原

    • Databases 資料夾 –> 右鍵 –> Restore Database…

      4restore

    • Device –> …

      5device

    • Select backup devices

      6selectdevice

    • 選取後就會將相關資訊填入 Destination 及 Restore plan 中

      7restoreplan

    • 其他還原選項:資料庫存在時的策略、資料庫檔案儲存位置

      8restoreoption

  3. 優缺點

    • 優點：

      1. 備份還原速度快，三種方式最快的

    • 缺點：

      1. 新版 SQL Server 備份出來的檔案無法還原至較舊的 SQL Server 中
      2. Azure SQL 無法使用

二、使用 script

  1. 備份

    • 目標資料庫 –> 右鍵 –> Tasks –> Generate Scripts..

      9genscript

    • Introduction

      10intro

    • Choose Onjects

      • 可以自由選擇需要匯出的資料庫物件

        11selectobject

    • Set Scripting Options

      12scriptoption

      • 紅框可以自訂進階匯出選項(以下分享幾個我自己常用的設定項目)

        • Script DROP and CREATE (選擇產生 drop 或 create 語法)

          13dropcreate

        • Script Server Version (針對不同版本 SQL Server 產生對應的 script)

          14scriptversion

        • Types of data to script (選擇產生 schema 或 data 的 script )

          15typescript

        • Script for the database engine type(選擇一般資料庫或是 Azure SQL)

          16enginetype

      • 綠框可以選擇匯出的目標

        • 儲存成檔案
        • 儲存至剪貼簿
        • 儲存至新的查詢視窗

    • Summary

      17summary

    • Save of Publish Scripts

      18inprogress

      19success

  2. 還原

    直接執行產生的 script 即可

  3. 優缺點

    • 優點：

      1. 可以選擇產出舊版 SQL Server 的 script
      2. 可以自行調整 script

    • 缺點：

      1. 特殊字元或是 binary 無法匯入
      2. 如果想要建立相同的資料庫需要調整的內容較多

三、使用 .bacpac

較詳細的內容可以參考 你認識 SQL Server 的資料層應用程式(Data-tier Applications - DAC)嗎?

  1. 備份

    • 目標資料庫 –> 右鍵 –> Tasks –> Export Data-tier Application…

      35export

    • Introduction

      36intro

    • Export Settings

      • Settings

        選擇儲存位置及檔名

        37settings

      • Advanced

        選擇匯出內容

        38advanced

    • Summary

      39summary

    • Results

      40inprogress

      41success

  2. 還原

    • 資料庫資料夾 –> 右鍵 –> Import Data-tier Application…

      42import

    • Introduction

      43INTRO

    • Import Settings

      選擇 BACPAC 檔案位置

      44filelocation

    • Database Settings

      設定 db 名稱及相關檔案儲放路徑

      45dbsetting

    • Summary

      46summary

    • Results

      47inprogress

      48success

  3. 優缺點

    • 優點：

      1. 使用 XML 來進行資料描述，比 script 可攜性高
      2. 速度比產生 script 快
      3. Azure SQL 可以直接使用

    • 缺點：

      1. 舊版 DAC 工具無法讀取新版 DAC 產生的檔案
      2. 有些比較進階的特性不支援

參考資訊

  1. 你認識 SQL Server 的資料層應用程式(Data-tier Applications - DAC)嗎?