星期三, 5月 20, 2015

如何將較高版本SQL Server複製到低版本SQL Server (降級為舊版)並保留權限及資料庫圖表

一般若是要將SQL Server裡的Database轉往其他Server時,最簡單的方式就是備份(Backup)後再還原(Restore),或者是䣃離(detach)後附加(attach)。 但是很不幸地,若是由較低版本(e.g. 2008)到較高版本(e.g. 2012)要怎麼辦呢?
此時就要先考慮需求,若權限不是問題,資料量也不大時,當然就使用SSMS(SQL Server management Studio)裡的「複製資料庫」或是「匯出資料」,都可以輕易達成。但若資料量太大時,複製作業會失敗,似乎是DTS會啟動交易(Transaction),我並沒有確認真正原因。

但若是有複雜的權限管理時怎麼辦呢?以前我會用資料庫發行精靈 (Database Publishing Wizard),但是這工具似乎沒有後續更新,可參考保哥這篇。也就是匯出為script的方式,以下概述之。

新版的SQL Server在資料庫的「工作」裡有「產生指令碼」,「選項」裡有「結構描述和資料」、「僅限結構描述」、「僅限資料」三種。若資料量太大時,我會建議先產生結構描述的script,再產生資料的script。





通常不同版本的SQL Server安裝目錄不會一樣,所以產生出來的Script要稍加修改,我是直接用Create Database的部份刪除,手動新增Database後,再匯入建立結構的script。在匯入前先手動建立需要的登入帳號,比較不會有問題,若不知道怎麼做可以參考如何在 SQL Server 2005 和 SQL Server 2008 的執行個體之間傳送登入和密碼

但是又遇到一個難題,資料部份的script太大,SSMS會跳出記憶體不足的錯誤訊息。幸好SSMS產生出來的Script會自行分段再加上一列GO指令,因此我另外寫一個小程式,批次執行這些script,但最後不會有GO,要自己加上去或是不要忘記執行最後一段script。

這樣就把權限和登入都匯入了嗎?對,全都產生完成,但是不會有「資料庫圖表」(Database Diagrams)。

SQL Server 2005以上的圖表存在一個系統資料表,叫dbo.sysdiagrams,用產生指令碼的方式選不到這個資料表,可以利用「工作」->「匯出資料」,再將此資料表匯入新SQL Server。



最後要再下個指令
UPDATE dbo.sysdiagrams SET principal_id = 1;
完成後點資料庫圖表時,會問是否要建立;不要害怕,點下建立後就會看到之前辛苦建立的資料庫圖表了!

沒有留言: