SQL Server

首先開一個新專案，選擇SQL Server資料庫專案 [](http://2.bp.blogspot.com/-TqYagomYUG0/U6APChmvlvI/AAAAAAAABes/SYUg_DQmxyo/s1600/01.%25E9%2596%258B%25E6%2596%25B0%25E5%25B0%2588%25E6%25A1%2588.png) 匯入的方式有三種，這邊用現有的資料庫當例子 [](http://4.bp.blogspot.com/-vzW-ukm2OxE/U6APVp7OrNI/AAAAAAAABfQ/k4iepZU_0wA/s1600/02.%25E5%258C%25AF%25E5%2585%25A5.png) 輸入要匯入的資料庫連線資料 [](http://3.bp.blogspot.com/-hOsS_RXJajE/U6APVdfAZmI/AAAAAAAABfY/oO2HQNcDcUM/s1600/03.%25E8%25A8%25AD%25E5%25AE%259A%25E8%25B3%2587%25E6%2596%2599%25E5%25BA%25AB%25E9%2580%25A3%25E7%25B7%259A.png) 接下來需要一點時間來把資料庫物件轉成對應的檔案 [](http://2.bp.blogspot.com/--FHHY35xyxo/U6APVKJMJtI/AAAAAAAABe4/co7hQ7vvirM/s1600/04.%25E5%258C%25AF%25E5%2585%25A5%25E5%25AE%258C%25E6%2588%2590.png) 匯入成功後會得到一堆物件定義的檔案 [](http://1.bp.blogspot.com/-LRIOtxZZ1io/U6AQunB6iUI/AAAAAAAABgE/fwmTcRpphhY/s1600/05.1%E5%AE%8C%E6%88%90%E7%9A%84%E6%A8%A3%E5%AD%90.png) 接下來用新增一個資料表當例子 [](http://2.bp.blogspot.com/-uthvGT3J7kE/U6APV0bF4II/AAAAAAAABfE/UbHIjLCztf0/s1600/05.%25E5%258A%25A0%25E5%2585%25A5%25E6%2596%25B0%25E9%25A0%2585%25E7%259B%25AE.png) 輸入要新增的資料表的名稱 [](http://3.bp.blogspot.com/-Pg61sJvN6Ko/U6APWOvui2I/AAAAAAAABfM/HdF5U5Fkf6c/s1600/06.%25E5%258A%25A0%25E5%2585%25A5%25E8%25B3%2587%25E6%2596%2599%25E8%25A1%25A8.png) 簡單建立幾個欄位，完成後存檔 [](http://4.bp.blogspot.com/-ZbQe18CtgaA/U6APXuQ8xVI/AAAAAAAABfo/BBqGe5A_qpE/s1600/07.%25E7%25B7%25A8%25E8%25BC%25AF%25E6%25A0%25BC%25E5%25BC%258F.png) 編譯後可以得到一個dacpac檔案，就可以拿到目標伺服器上面去部署 這邊用直接發行的方式來部署 [](http://2.bp.blogspot.com/-rw7LiyRoGBw/U6APXP14k2I/AAAAAAAABfs/0iGLFw2aaD0/s1600/08.%25E7%2599%25BC%25E8%25A1%258C.png) 輸入要部署的目標伺服器的連線字串後按下發行 [](http://3.bp.blogspot.com/-1T2G9FZjoDg/U6APXYXEBsI/AAAAAAAABf4/rHo2_CktK-Q/s1600/09.%25E7%2599%25BC%25E8%25A1%258C%25E8%25A8%25AD%25E5%25AE%259A.png) 順利發行成功 [](http://4.bp.blogspot.com/-N2DmfwTtiT4/U6APX4WCNXI/AAAAAAAABf0/0rkjS3envR0/s1600/10.%25E7%2599%25BC%25E8%25A1%258C%25E6%2588%2590%25E5%258A%259F.png) 目標伺服器上就可以看到新增的資料表囉 [!

為了方便練習，所以透過SQL LocalDB建立兩個執行個體[](http://1.bp.blogspot.com/-PYm-vP7F9SQ/U5_tXS5y79I/AAAAAAAABbY/QPu4WEiLHPY/s1600/01.%25E5%25BB%25BA%25E7%2592%25B0%25E5%25A2%2583.png) 再來建立一個練習用的資料庫，這裡用北風資料庫為例子[](http://4.bp.blogspot.com/-QazOoP4b4vU/U5_tXe_J0JI/AAAAAAAABbk/pq6cEvQRC8w/s1600/02.%25E5%258C%2597%25E9%25A2%25A8%25E8%25B3%2587%25E6%2596%2599%25E5%25BA%25AB.png) 首先把資料庫註冊成資料層應用程式[](http://1.bp.blogspot.com/-e0MTcSY8jTM/U5_tXfsjfyI/AAAAAAAABbw/Pufq3ukfQOQ/s1600/03.%25E8%25A8%25BB%25E5%2586%258A.png) 設定屬性 [](http://1.bp.blogspot.com/-Pl3r-mf7M_s/U5_tXxN09nI/AAAAAAAABbo/FdNbbjp1-P8/s1600/04.%25E8%25A8%25BB%25E5%2586%258A%25E5%25B1%25AC%25E6%2580%25A7.png) 驗證與摘要 [](http://4.bp.blogspot.com/-c4VmOP54fdc/U5_tYMrk4pI/AAAAAAAABeY/uCabHv_R9SA/s1600/05.%25E8%25A8%25BB%25E5%2586%258A%25E6%2591%2598%25E8%25A6%2581.png) 註冊完成 [](http://4.bp.blogspot.com/-VFcJVLC0KFU/U5_tYT_tj-I/AAAAAAAABb4/a8aRtT07uE4/s1600/06.%25E8%25A8%25BB%25E5%2586%258A%25E5%25AE%258C%25E6%2588%2590.png) 再來擷取資料層應用程式，不用註冊也可以直接擷取[](http://1.bp.blogspot.com/-RSIBOQZNGCM/U5_tY546YjI/AAAAAAAABec/W0PK5ycOF_k/s1600/07.%25E6%2593%25B7%25E5%258F%2596.png) 選擇檔案輸出的路徑和檔名 [](http://3.bp.blogspot.com/-LLMbv0HkHOE/U5_tZNY6SaI/AAAAAAAABcM/spVOVImkRGk/s1600/08.%25E6%2593%25B7%25E5%258F%2596%25E5%25B1%25AC%25E6%2580%25A7.png) 驗證與摘要 [](http://4.bp.blogspot.com/-61wr623zbFM/U5_tZRMNqLI/AAAAAAAABcU/WeBsHrL7iwY/s1600/09.%25E6%2593%25B7%25E5%258F%2596%25E6%2591%2598%25E8%25A6%2581.png) 封裝完成 [](http://4.bp.blogspot.com/-cLyszCK0cvA/U5_tZ8t6XyI/AAAAAAAABck/OAtx5RDGruQ/s1600/10.%25E6%2593%25B7%25E5%258F%2596%25E5%25AE%258C%25E6%2588%2590.png) 再來把擷取出來的檔案部署到第二個環境[](http://4.bp.blogspot.com/-HW0bgK0INAE/U5_taIk8hfI/AAAAAAAABeI/MdQ6LrTAvcc/s1600/11.%25E9%2583%25A8%25E7%25BD%25B2.png) 選擇剛輸出的檔案 [](http://4.bp.blogspot.com/-hD5cEqITLcM/U5_taFULOqI/AAAAAAAABcg/mxhSAzjH0wA/s1600/12.%25E9%2583%25A8%25E6%259A%2591%25E9%2581%25B8%25E6%25AA%2594.png) 設定要部署的資料庫名稱 [](http://3.bp.blogspot.com/--HEJU57mtNU/U5_taRjuiiI/AAAAAAAABc0/u6ZaFtAdBiA/s1600/13.%25E9%2583%25A8%25E7%25BD%25B2%25E5%2590%258D%25E7%25A8%25B1.png) 部署摘要 [](http://3.bp.blogspot.com/-kAAdV8Uf_WE/U5_tazOwtdI/AAAAAAAABco/st3V2Dhj1zk/s1600/14.%25E9%2583%25A8%25E7%25BD%25B2%25E6%2591%2598%25E8%25A6%2581.png) 部署完成 [](http://4.bp.blogspot.com/-h_FTGXGLzVA/U5_tbOrYbXI/AAAAAAAABc8/0bFQzE8Av30/s1600/15.%25E9%2583%25A8%25E7%25BD%25B2%25E5%25AE%258C%25E6%2588%2590.png) 第二個環境也有北風資料庫了 [](http://2.bp.blogspot.com/-KhTDtavtW30/U5_tbfNVwCI/AAAAAAAABeQ/RIxgYNMd3z8/s1600/16.%25E9%2583%25A8%25E7%25BD%25B2%25E5%25BE%258C%25E7%259A%2584%25E6%25A8%25A3%25E5%25AD%2590.png) 再來異動第一個環境的資料庫的schema，這裡用新增一個資料表當例子 [](http://4.bp.blogspot.com/-Z9Ah5th7ysI/U5_tcFWAAFI/AAAAAAAABdI/9CNike4If7w/s1600/17.%25E6%2596%25B0%25E5%25A2%259E%25E8%25B3%2587%25E6%2596%2599%25E8%25A1%25A8.png) 再擷取一次資料層應用程式 [](http://2.bp.blogspot.com/-aefoQHvcJwA/U5_tcsH9hwI/AAAAAAAABdc/kYcdxZRIwEg/s1600/18-1.%25E5%2586%258D%25E6%2593%25B7%25E5%258F%2596%25E4%25B8%2580%25E6%25AC%25A1.png) 在第二個環境上用升級的方式匯入 [](http://3.bp.blogspot.com/-fmVxwKzU1Zk/U5_tcqyqHTI/AAAAAAAABdU/xYCAJbOrZVw/s1600/18-2.%25E5%258D%2587%25E7%25B4%259A.png) 選擇剛匯出的檔案 [](http://1.bp.blogspot.com/-0dr008Hag30/U5_tdDzNlDI/AAAAAAAABeU/QFWzbi0JWZY/s1600/19.%25E5%258D%2587%25E7%25B4%259A%25E9%2581%25B8%25E6%25AA%2594.png) 這邊的尚未變更指的是從上次建立資料層應用程式後都沒有變更Schema 而不是和要升級的檔案比對的結果 [](http://3.bp.blogspot.com/-u0kKhoucR7Q/U5_tdQeE3MI/AAAAAAAABeA/IRpxqd8w-Z4/s1600/20.%25E5%258D%2587%25E7%25B4%259A%25E5%2581%25B5%25E6%25B8%25AC%25E7%25B5%2590%25E6%259E%259C.png) 升級選項 [](http://1.bp.blogspot.com/-WZ8HUXKankM/U5_tdzT3F1I/AAAAAAAABdo/Vldgx3uqvPs/s1600/21.%25E5%258D%2587%25E7%25B4%259A%25E9%2581%25B8%25E9%25A0%2585.png) 升級計畫[](http://4.bp.blogspot.com/-BDKkSYVWaHE/U5_td354TOI/AAAAAAAABd8/54GfkmMNWwU/s1600/22.%25E5%258D%2587%25E7%25B4%259A%25E8%25A8%2588%25E5%258A%2583.png) 升級摘要 [](http://4.bp.blogspot.com/-Mw0clAb-f5Q/U5_teYqP2zI/AAAAAAAABd0/Gwo6zt5Tr2c/s1600/23.%25E5%258D%2587%25E7%25B4%259A%25E6%2591%2598%25E8%25A6%2581.png) 升級完成 [](http://3.bp.blogspot.com/-IE5yhPETFqQ/U5_telmV4VI/AAAAAAAABd4/Lse01AvDSkQ/s1600/24.%25E5%258D%2587%25E7%25B4%259A%25E5%25AE%258C%25E6%2588%2590.png) 升級後可以看到新增的資料表 [](http://4.bp.blogspot.com/-va77B4s-mHI/U5_te15PgUI/AAAAAAAABeM/Wp1USWOjwS4/s1600/25.%25E5%258D%2587%25E7%25B4%259A%25E5%25BE%258C%25E7%259A%2584%25E6%25A8%25A3%25E5%25AD%2590.png)

首先淮備好資料庫，例如一個名稱為TeamCity的DB ](http://2.bp.blogspot.com/-vkJGa93mgS8/UJud6ZDutdI/AAAAAAAAAXA/efJdpGCptNE/s1600/01.NewDB.png) 建一個登入帳號，這裡帳號用bar，密碼用foo當範例 ](http://1.bp.blogspot.com/-WRh2WZcEgNM/UJud7G_MGSI/AAAAAAAAAXI/hipvEg4ZmNY/s1600/02.NewUser.png) 給予db_owner的權限 ](http://3.bp.blogspot.com/-1mkS-zvnKaw/UJud7voFGwI/AAAAAAAAAXQ/IbcM34MqW_g/s1600/03.Owner.png) 再來下載Database Driver，官網的文件中有介紹兩種Driver 但我試了半天都不行用，後來爬文找到更改連線字串的寫法就可以了 所以在這裡以Microsoft JDBC Driver來介紹 先去Microsoft Download Center 下載Driver ](http://1.bp.blogspot.com/-YNHb9XrRv9k/UJud8PN0l0I/AAAAAAAAAXY/-w8b5MtUqSk/s1600/04.Download.png) 下載後解壓縮，把sqljdbc4.jar複製到這個神秘的地方 ](http://3.bp.blogspot.com/-whQq072tXfw/UJud8vAsOII/AAAAAAAAAXg/QhDweQyvu08/s1600/05.LibDir.png) 再來編輯database.mssql.properties.dist這一個範本檔 ](http://1.bp.blogspot.com/-8eIjxlciydI/UJud9RDp2bI/AAAAAAAAAXo/LzrW7FDwNZA/s1600/06.DatabaseTemplate.png) 官網的文件是把帳密打在後面，但試了半天就是不行 爬文的結果把帳密改寫在連線字串中就成功了，JAVA真是一種神秘的語言 ](http://2.bp.blogspot.com/-k2IyAF5GUFk/UJud91XM0HI/AAAAAAAAAXw/l4Jxv5E89a8/s1600/07.Config.png) TeamCity升級到7.1.2後，JDBC的設定不能用了，只好再試了一下另一種連線方式jtds-1.2.2 首先到此下載，jTDS - SQL Server and Sybase JDBC driver 我下載最新的版本，會有java.lang.UnsupportedClassVersionError的錯誤，換成1.2.2就好了 一樣把jtds-1.2.2.jar複製到lib/jdbc的資料夾 連線字串就可以照著範本的格式打了 connectionUrl=jdbc:jtds:sqlserver://<hostname>:1433/<dbname> connectionProperties.user=<username> connectionProperties.password=<password> 參考資料 Migrating to an External Database 到此淮備工作就完成了，要開始轉移資料之前，要先把服務停下來 ](http://1.bp.blogspot.com/-ZO21PE08jw8/UJud-TBJl6I/AAAAAAAAAX4/3I9MMVD7KMw/s1600/08.StopService.png) 然後打開一個cmd，輸入以下指令 set path=%path%;c:\TeamCity\jre\bin 這行是和設定JAVA_HOME這個神秘的全域變數同樣的效果 用來把Java Run Time的路徑包含進系統的path 因為我沒安裝JAVA環境，所以要指向到TeamCity自帶的jre路徑 但如果在執行過程中會發生無法開啟jvm.cfg這個錯誤訊息的話 把System32下面的Java.exe、Javaw.exe、Javaws.exe都砍掉就行了 maintainDB.cmd migrate -T c:\ProgramData\JetBrains\TeamCity\config\database.mssql.properties.dist maintainDB.cmd是TeamCity自帶的資料庫維護工具，可以用來備份、還原、和搬移資料 這邊用的是migrate，把剛設定資料庫的設定檔用-T參考傳進去就行了 ](http://3.bp.blogspot.com/-6skME4p2u5U/UJud-95UhjI/AAAAAAAAAYA/L0Lezi6GN14/s1600/09.Cmd.png) 順利的話，就會開始搬移資料進SQL Server，並且也會把剛剛的資料庫連線範本 複制成正式用的檔案database.properties ](http://3.bp.blogspot.com/-Y76ZKXqUFmY/UJumBkLtVVI/AAAAAAAAAYw/56KfkQqXhIQ/s1600/10.Success.png)