如何在運行 SQL Server 的電腦之間移動資料庫

本頁

概要

如果您使用的是 SQL Server 2005

備份與還原

Sp_detach_db 和 Sp_attach_db 預存程序

關於定序的說明

匯入和匯出資料（在 SQL Server 資料庫之間複製對象和資料）

第 2 步：如何傳輸登入和密碼

第 3 步：如何解決孤立使用者

第 4 步：如何移動作業、警報和運算子

第 5 步：如何移動 DTS 包

方法 1

方法 2

更多資訊

參考

概要

本文分步介紹了如何在運行 SQL Server 的電腦之間移動 Microsoft SQL Server 使用者資料庫和大多數常見的 SQL Server 元件。
本文中介紹的步驟假定您不移動 master、model、tempdb 或 msdb 這些系統資料庫。這些步驟為您傳輸登入以及master 和 msdb 資料庫中包含的大多數常見組件提供了多個選項。
有關執行本文中介紹的步驟時未傳輸的特定項目的資訊，請參閱本文中的“更多資訊”一節。
注意：支援將資料從 SQL Server 2000 遷移到 Microsoft SQL Server 2000（64 位元）。您可以將一個 32 位元據庫附加到一個 64 位元資料庫上，方法是：使用 sp_attach_db 系統預存程序或 sp_attach_single_file_db 系統預存程序，或者使用 32 位企業管理器中的備份與還原功能。您可以在 SQL Server 的 32 位和 64 位元兩種版本之間來回移動資料庫。您還可以使用同樣的方法從 SQL Server 7.0 遷移資料。但是，不支援將資料從 SQL Server 2000（64 位元）降級到 SQL Server 7.0。 下面分別介紹這幾種方法。

如果您使用的是 SQL Server 2005

您可以使用相同的方法從 SQL Server 7.0 或 SQL Server 2000 遷移資料。但是，Microsoft SQL Server 2005 中的管理工具與 SQL Server 7.0 或 SQL Server 2000 中的管理工具有所不同。您應該使用 SQL Server Management Studio（而不是 SQL Server 企業管理器）以及 SQL Server 匯入和匯出嚮導 (DTSWizard.exe)（而不是資料轉換服務匯入和匯出資料嚮導）。

備份與還原

在原始伺服器上備份使用者資料庫，然後將使用者資料庫還原到目標伺服器上。

•在備份過程中時可能有人使用資料庫。如果使用者在備份完成後對資料庫執行 INSERT、UPDATE 或 DELETE 語句，則備份中不會包含這些更改。如果您必須傳輸所有更改，那麼，假如您既執行交易記錄備份又執行完整Database Backup，您可以以儘可能短的停止時間來傳輸這些更改。

1.在目標伺服器上還原完整Database Backup，並指定 WITH NORECOVERY 選項。
注意：為防止對資料庫做進一步的修改，請指導使用者在原始伺服器上退出資料庫活動。

2.執行交易記錄備份，然後使用 WITH RECOVERY 選項將交易記錄備份還原到目標伺服器上。停止時間僅限於交易記錄備份和恢複的時間。有關更多資訊，請參閱 SQL Server 聯機叢書的“Transact-SQL 參考”主題中的“還原”子主題。

•目標伺服器上的資料庫將與原始伺服器上的資料庫大小相同。要減小資料庫的大小，您必須在執行備份前壓縮來源資料庫的大小，或者在完成還原後壓縮目標資料庫的大小。有關更多資訊，請參閱 SQL Server 聯機叢書的“建立和維護資料庫”標題下的“壓縮資料庫”子主題。

•如果您將資料庫還原到的檔案位置不同於來源資料庫的檔案位置，則必須指定 WITH MOVE 選項。例如，在原始伺服器上，資料庫位於 D:\Mssql\Data 檔案夾中。目標伺服器沒有 D 磁碟機，因而您需要將資料庫還原到 C:\Mssql\Data 檔案夾。 有關如何將資料庫還原到其他位置的更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

221465 (http://support.microsoft.com/kb/221465/) INF：結合使用 WITH MOVE 選項和 RESTORE 語句

304692 (http://support.microsoft.com/kb/304692/) INF：使用 BACKUP 和 RESTORE 將 SQL Server 7.0 資料庫移到新的位置

•如果您想覆蓋目標伺服器上的一個現有資料庫，則必須指定 WITH REPLACE 選項。有關更多資訊，請參閱 SQL Server 聯機叢書的“Transact-SQL 參考”主題中的“還原”子主題。

•原始伺服器和目標伺服器上的字元集、排序次序和 Unicode 整序可能必須相同，具體取決於您要還原到 SQL Server 的哪種版本。有關更多資訊，請參閱本文中的“關於定序的說明”一節。

Sp_detach_db 和 Sp_attach_db 預存程序

要使用 sp_detach_db 和 sp_attach_db 這兩個預存程序，請按下列步驟操作：

1.使用 sp_detach_db 預存程序分離原始伺服器上的資料庫。您必須將與資料庫關聯的 .mdf、.ndf 和 .ldf 這三個檔案複製到目標伺服器上。參見下表中對檔案類型的描述：

副檔名說明

.mdf主要資料檔案

.ndf輔助資料檔案

.ldf交易記錄檔

2.使用 sp_attach_db 預存程序將資料庫附加到目標伺服器上，並指向您在上一步驟中複製到目標伺服器的檔案。 有關如何使用這些方法的更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

224071 (http://support.microsoft.com/kb/224071/) 如何通過使用 SQL Server 中的分離和附加功能將 SQL Server 資料庫移到新位置

•分離資料庫後將無法訪問該資料庫，並且複製檔案時也無法使用該資料庫。在進行分離的那一時刻資料庫中包含的所有資料都被移動。

•在您使用附加或分離方法時，兩個伺服器上的字元集、排序次序和 Unicode 整序都必須相同。有關更多資訊，請參閱本文中的“關於定序的說明”一節。

關於定序的說明

如果您使用備份與還原或附加和分離方法在兩個 SQL Server 7.0 伺服器之間移動資料庫，則兩個伺服器上的字元集、排序次序和 Unicode 整序都必須相同。如果您將資料庫從 SQL Server 7.0 移到 SQL Server 2000，或者在不同的 SQL Server 2000 伺服器之間移動資料庫，則資料庫將保留來源資料庫的整序。這意味著，如果運行 SQL Server 2000 的目標伺服器的整序與來源資料庫的整序不同，則目標資料庫的整序也將與目標伺服器的 master、model、tempdb 和 msdb 資料庫的整序不同。有關更多資訊，請參見 SQL Server 2000 聯機叢書中的“混合整序環境”主題。

匯入和匯出資料（在 SQL Server 資料庫之間複製對象和資料）

您可以使用資料轉換服務匯入和匯出資料嚮導來複製整個資料庫或有選擇地將來源資料庫中的對象和資料複製到目標資料庫。

•在傳輸過程中，可能有人在使用來源資料庫。如果在傳輸過程中有人在使用來源資料庫，您可能會看到傳輸過程中出現一些阻滯現象。

•在您使用匯入和匯出資料嚮導時，原始伺服器與目標伺服器的字元集、排序次序和整序不必相同。

•因為來源資料庫中未使用的空間不會移動，所以目標資料庫不必與來源資料庫一樣大。同樣，如果您只移動某些對象，則目標資料庫也不必與來源資料庫一樣大。

•SQL Server 7.0 資料轉換服務可能無法正確地傳輸大於 64 KB 的文本和映像資料。但 SQL Server 2000 版本的資料轉換服務不存在此問題。 有關更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

257425 (http://support.microsoft.com/kb/257425/) FIX：DTS 對象傳輸不能傳輸大於 64 KB 的 BLOB 資料

第 2 步：如何傳輸登入和密碼

如果您不將原始伺服器中的登入傳輸到目標伺服器，當前的 SQL Server 使用者就無法登入到目標伺服器。您可以按照下面的 Microsoft 知識庫文章中的說明來傳輸登入和密碼：

246133 (http://support.microsoft.com/kb/246133/) 如何在 SQL Server 執行個體之間傳輸登入和密碼

目標伺服器上的登入的預設資料庫可能與原始伺服器上的登入的預設資料庫不同。您可以使用 sp_defaultdb 預存程序來更改登入的預設資料庫。有關更多資訊，請參見 SQL Server 聯機叢書中的“Transact-SQL 參考”主題的“sp_defaultdb”子主題。

第 3 步：如何解決孤立使用者

在您向目標伺服器傳輸登入和密碼後，使用者可能還無法訪問資料庫。登入與使用者是靠安全識別符 (SID) 關聯在一起的；在您移動資料庫後，如果 SID 不一致，SQL Server 可能會拒絕使用者訪問資料庫。此問題稱為孤立使用者。如果您使用 SQL Server 2000 DTS 傳輸登入功能來傳輸登入和密碼，就可能會產生孤立使用者。此外，被允許訪問與原始伺服器處於不同域中的目標伺服器的整合登入帳戶，也會導致出現孤立使用者。

1.尋找孤立使用者。在目標伺服器上開啟查詢分析器，然後在您移動的使用者資料庫中運行以下代碼：

exec sp_change_users_login 'Report'

此過程將列出任何未連結到一個登入帳戶的孤立使用者。如果沒有列出使用者，請跳過第 2 步和第 3 步，直接進行第 4 步。

2.解決孤立使用者問題。如果一個使用者是孤立使用者，資料庫使用者可以成功登入到伺服器，但卻無權訪問資料庫。如果您嘗試向資料庫授予登入訪問權，則會因該使用者已經存在而出現下列錯誤訊息：

Microsoft SQL-DMO (ODBC SQLState:42000) 錯誤 15023:當前資料庫中已存在使用者或角色 '%s'。

有關如何解決孤立使用者的更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

240872 (http://support.microsoft.com/kb/240872/) 如何解決在運行 SQL Server 的伺服器之間移動資料庫時的許可權問題

此文章介紹如何將登入映射到資料庫使用者，以及如何解決標準的 SQL Server 登入和整合登入產生的孤立使用者。

274188 (http://support.microsoft.com/kb/274188/) PRB：聯機叢書中的“孤立使用者疑難解答”主題不完整

上面的文章介紹了如何使用 sp_change_users_login 預存程序來逐個糾正孤立使用者。sp_change_users_login 預存程序僅能解決標準的 SQL Server 登入帳戶的孤立使用者問題。

3.如果資料庫擁有者 (dbo) 被當作孤立使用者列出，請在使用者資料庫中運行下面的代碼：

exec sp_changedbowner 'sa'

此預存程序會將資料庫擁有者更改為 dbo 並解決這個問題。要將資料庫擁有者更改為另一使用者，請使用您想使用的使用者再次運行 sp_changedbowner。有關更多資訊，請參見 SQL Server 聯機叢書的“Transact-SQL 參考”主題中的“sp_changedbowner”子主題。

4.如果您的目標伺服器啟動並執行是 SQL Server 2000 Service Pack 1，則在您執行附加操作或還原作業（或兩種操作都執行）後，企業管理器的使用者檔案夾中的列表中可能沒有資料庫擁有者使用者。 有關更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

305711 (http://support.microsoft.com/kb/305711/) BUG：在企業管理器中沒有顯示 DBO 使用者

5.如果目標伺服器上不存在映射到原始伺服器上的 dbo 的登入，您在嘗試通過企業管理器更改系統管理員 (sa) 密碼時，可能會收到以下錯誤訊息：

錯誤 21776:[SQL-DMO] 名稱 'dbo' 在 Users 集合中沒有找到。如果該名稱是合法名稱，則使用 [] 來分隔名稱的不同部分，然後重試。

有關更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

218172 (http://support.microsoft.com/kb/218172/) PRB：在企業管理器中不能更改 SA 密碼

警告：如果您再次還原或附加資料庫，則資料庫使用者可能會再次被孤立，這樣您就必須重複第 3 步操作。

第 4 步：如何移動作業、警報和運算子

第 4 步是可選操作。您可以為原始伺服器上的所有作業、警報和運算子產生指令碼，然後在目標伺服器上運行指令碼。

•要移動作業、警報和運算子，請按照下列步驟操作：

1.開啟 SQL Server 企業管理器，然後展開管理檔案夾。

2.展開 SQL Server Agent，然後按右鍵警報、作業或運算子。

3.單擊所有任務，然後單擊產生 SQL 指令碼。對於 SQL Server 7.0，請單擊為所有作業產生指令碼、警報或運算子。

您可以用按右鍵選擇為所有警報、所有作業或所有運算子產生指令碼。

•您可以將作業、警報和運算子從 SQL Server 7.0 移到 SQL Server 2000，也可以在運行 SQL Server 7.0 和運行 SQL Server 2000 電腦之間移動。

•如果在原始伺服器上為運算子設定了 SQLMail 通知，則目標伺服器上也必須設定 SQLMail，才能具有相同的功能。 有關更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

263556 (http://support.microsoft.com/kb/263556/) INF：如何配置 SQL Mail

第 5 步：如何移動 DTS 包

第 5 步是可選操作。如果 DTS 包在原始伺服器上儲存在 SQL Server 中或存放庫中，您可以在需要時移動這些包。要在伺服器之間移動 DTS 包，請使用下列方法之一。

方法 1

1.在原始伺服器上將 DTS 包儲存到一個檔案中，然後在目標伺服器上開啟 DTS 包檔案。

2.將目標伺服器上的包儲存到 SQL Server 或存放庫中。
注意：您必須用單獨的檔案逐個地移動這些包。

方法 2

1.在 DTS 設計器中開啟每個 DTS 包。

2.在包菜單上，單擊另存新檔。

3.指定目標 SQL Server。

注意：在新伺服器上，包可能無法正常運行。您可能必須對包變更，更改包中任何對舊的原始伺服器上的串連、檔案、資料來源、設定檔和其他資訊的引用，以便引用新的目標伺服器。您必鬚根據每個包的設計逐個包進行這些更改。

更多資訊

您可能還需要移動其他一些項目，例如複製、記錄傳送、全文檢索目錄、指定的備份裝置、維護計劃和連結的伺服器等。如果您需要的話，您可以檢查這些配置的原始伺服器，然後在目標伺服器上採用相應的步驟手動設定它們。
有關如何移動全文組件的更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

240867 (http://support.microsoft.com/kb/240867/) 如何移動、複製和備份全文檢索目錄檔案夾和檔案

本文中介紹的步驟不移動資料庫圖表以及備份與還原記錄。如果您必須移動這些資訊，請移動 msdb 系統資料庫。有關如何移動 msdb 資料庫的資訊，請參閱本文的“第 1 步：如何移動使用者資料庫”一節中引用的 Microsoft 知識庫文章。如果您移動 msdb 資料庫，則不必執行“第 4 步：如何移動作業、警報和運算子”或“第 5 步：如何移動 DTS 包”。

參考

有關更多資訊，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：

320125 (http://support.microsoft.com/kb/320125/) 如何移動資料庫圖表

from: http://support.microsoft.com/kb/314546/zh-cn