[SQL Server][Upgrade]SQL Server 2005移轉資料庫檔案到SQL Server 2017

2017-12-24

16520
0
SQLUpgrade
2017-12-24

這次不用升級SQL Instance版本(就地升級)，但要移轉幾個User Database到新機器上新版本的SQL Instance。在學校曾上過SQL 2000的課，幾年前重新接觸SQL Server，已經是SQL 2008R2 ，幾年下來慢慢也從SQL 2008移轉到2012/2014/2016，好在SQL資料庫的相容性，每次都很順利，不過這次是自己沒使用過的SQL 2005，試試SQL2005到SQL2017，一個12生肖的距離。

SQL 2017的相容性層級可以選擇140(2017), 130(2016), 120(2014), 110(2012), 100(2008)，微軟官方Docs(下圖)也提到SQL Instance的升級可以是以下版本，但最早也只到SQL 2008，來試試SQL2005的資料庫檔案(user database)的移轉。

下載SQL 2005資料庫

從微軟資料庫的練習範本找SQL 2005的資料庫檔案，雖然2016 年 4 月微軟終止了SQL2005的延伸支援，但幸好連結還在

1.下載好安裝檔案後，點擊安裝檔兩下

2.我同意，下一步

3.修改產生資料庫檔案的路徑，然後下一步

4.取得AdventureWorks 資料庫mdf、ldf檔案

5.下載好了SQL2005的資料庫檔案後，把他們複製到SQL2017的機器上。

SQL2017附加舊版資料庫檔案

1.先確認機器上的SQL Instance版本

SELECT @@VERSION

版本SQL 2017

2.附加(ATTACH)資料庫

USE [master]
GO
CREATE DATABASE AdventureWorks2005 ON 
( FILENAME = N'F:\Data\SQL2005\AdventureWorks_Data.mdf' ),
( FILENAME = N'F:\Data\SQL2005\AdventureWorks_Log.ldf' )
FOR ATTACH
GO

順利成功!

訊息中，可以發現SQL正在轉換內部版本從611到869

Converting database 'AdventureWorks2005' from version 611 to the current version 869.

附加資料庫成功!資料庫已經可以使用了。

SQL 資料庫內部版本(Internal version)

進一步觀察資料庫內的版本，查詢資料庫的內部版本號:

USE [AdventureWorks2005]
DBCC TRACEON (3604)
DBCC DBINFO
DBCC TRACEOFF (3604)
GO

可以從db info得知，2006年建立資料庫時，內部版本號還在611(當時在SQL2005)；目前則Upgrade到869(SQL2017)，相容性層級則在100(SQL 2008)。

不過內部版本一旦升級到SQL2017，就像變了心的女朋友，回不來了~

相較有彈性的是相容性層級:

查看資料庫屬性的頁面，在選項下有一個相容性層級:

這次的移轉，我們的資料庫原本是2005(90)也升級到SQL 2017最低可支援的SQL Server 2008(100)了。

SQL 2017還能向下相容其他4的版本

相容性層級(compatibility level)及內部版本(Internal version)

相容性層級控制SQL語法元素的層級、函數版本、執行計畫的基數估計演算法及保留字等，如下表，每一個版本的SQL Instance都可以支援多種相同性層級的資料庫。
內部版本則是SQL資料庫的內部處理版本，舊資料庫升級到新的SQL Instance後會自動更新與Instance相符的內部版本，也沒辦法修改。最常碰到的問題是不能降級，舉例來說，用SQL2017開新資料庫，就不能把新資料庫檔案在SQL2017以前的版本做資料庫還原或資料庫附加。另一個問題就是太舊的也不能直接跨世代升級，像SQL 2000，一種威爾史密斯之我是傳奇，就沒辦法升到SQL2012以後的版本。

Marketing Name	Compatibility Level	Supported Compatibility Level	InternalVersion	UpgradeDb file	UpgradeDb file
SQL 2017	140	140, 130, 120, 110, 100	869		通
SQL 2016	130	130, 120, 110, 100	852
SQL 2014	120	120, 110, 100	782
SQL 2012	110	110, 100, 90	706
SQL 2008R2	100	100, 90, 80	661	通
SQL 2008	100	100, 90, 80	655
SQL 2005	90	90, 80	611
SQL 2000	80	80	539

如果單看相容層級及Instance升級說明，原以為SQL2017最早只能吃到SQL 2008的資料庫，實驗結果是可以吃到SQL 2005資料庫檔案。不過Docs也有說明可以升:

Docs的說明:

更早的版本(SQL 2000:539)

看來得先從SQL 2005或2008這兩個過渡版本洗一遍再升級，晚一點有空再繼續測。

小結:

除了資料庫卸離附加可以升級，其實用備份還原也行，就看我們移動資料庫檔案 vs 備份檔移動 + 備份還原的時間比較，兩者升級都會有相同的upgrade step作業。
記得連線字串、SQL login要照顧一下。
微軟網站: 透過卸離與附加升級SQL 2005到SQL2014影片
相容性層級晚一點再升到SQL2016/2017。
注意各版本已被取代、已停用及重大變更。
瀕臨絕種 vs 化石生物
甲客戶今年安裝SQL 2016，延伸支援到2026年7月。9年
乙客戶今年安裝SQL 2014，延伸支援到2024年7月。7年