最近在SQL Server遇到要複製一個鏡像資料庫的需求(同Server不同名稱)
由於在網路稍微搜尋一下沒有各種方法的比較,加上我很久沒有寫技術文件了
就趁此機會整理一下我知道的方法跟缺點吧
(本篇的圖片擷取至SQL Server Management Studio 17.4,可能跟舊版有一點差異)

 

※測試環境

2018-07-22 23_35_57-Window.png

名為Sample資料庫,裡面有一張User資料表,其中ID是PK,並且設為流水號

 

※目標

複製出一個Sample2資料庫到同一個SQL Server上,且結構描述與資料皆相同
另外實體檔案名稱(mdf)也需要變更成Sample2.mdf

 

(1) 還原資料庫

2018-07-22 23_49_12-還原資料庫 - Sample2.png

作法:對Sample資料庫執行備份,再將bak檔拿來還原
結果:失敗,原因是他只能用在同樣DB才能還原,手動開一個Sample2再倒回去是不行的
如果目標資料庫沒有同名DB的話倒是可以還原回去後,用指令修改實體檔案名稱,但還是很麻煩
另外若SQL Server版本不同,可能無法順利還原回去

2018-07-23 22_48_21-Microsoft SQL Server Management Studio.png

 

(2) 複製資料庫功能

2018-07-22 23_44_53-Window.png

作法:對資料庫按右鍵-[工作]-[匯出資料],複製資料目的地選擇當前資料庫作為目標

2018-07-22 23_34_08-SQL Server 匯入和匯出精靈.png

 

結果:失敗,索引不會複製過去,導致所有PK跟FK漏失,沒注意到的話會造成嚴重後果
所以這個方法並不可行

2018-07-22 23_35_41-Window.png

 

(3) 產生指令碼

2018-07-22 23_50_43-產生和發佈指令碼.png

作法:對資料庫按右鍵-[工作]-[產生指令碼]
會將DB的結構描述與資料弄成sql語法,可以方便直接帶著走
只要直接建立一個Sample2資料庫,再執行前面的sql語法,即可複製一份

2018-07-22 23_51_21-進階編寫指令碼選項.png

結果:成功
優點:可以選擇匯出結構描述或只匯出資料(2008 R2後的功能)
可以指定要匯出的資料表跟使用者
可以指定相容性版本,即使目標是低版本的SQL Server也可成功匯出
缺點:當資料量大時,這個方法會造成sql檔過大
會造成SSMS不允許執行,要用下sqlcmd指令方式突破限制,對一般使用者不方便
適合用在小型資料庫上

 

2018-07-22 23_51_42-Window.png

 

(4) 匯出資料層應用程式

2018-07-22 23_52_38-匯出資料層應用程式 %5CSample%5C.png

作法:對資料庫按右鍵-[工作]-[匯出資料層應用程式],產生bacpac檔
對資料庫按右鍵-[匯入資料層應用程式],指定該檔並輸入新的DB名稱

2018-07-22 23_54_02-Window.png

結果:成功
優點:資料庫內容會被壓縮成單一檔案,可以突破sql檔過大的限制
缺點:對古老版本的資料庫,匯出資料層應用程式可能會執行失敗
不同SQL Server版本,我沒有測試是否能成功匯入

2018-07-22 23_55_27-匯入資料層應用程式.png

 

※結論

我目前已知的作法中(3)跟(4)是比較方便的
其中(3)會有資料庫大小的限制,因此若小專案資料少時建議用(3),資料多時建議用(4)
當然也可能是我知識不足,如果還有其他方法,歡迎不吝指教

文章標籤
創作者介紹
創作者 蕭雲 的頭像
蕭雲

正因為活著

蕭雲 發表在 痞客邦 留言(0) 人氣()