文章編號: 224071 - 上次校閱: 2011年5月14日 - 版次: 15.0

如何使用 SQL Server 中的卸離和附加功能將 SQL Server 資料庫移到新位置

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。
本文曾發行於 CHT224071

在此頁中

全部展開 | 全部摺疊

結論

本文將告訴您，如何變更任何 Microsoft SQL Server 2005、SQL Server 2000 或 SQL Server 7.0 資料庫的資料檔案和記錄檔的位置。

如需有關如何在 SQL Server 2005 中移動系統資料庫的詳細資訊，請參閱《SQL Server 線上叢書》中的＜移動系統資料庫＞(英文) 主題。如果要檢視這個主題，請造訪下列 Microsoft Developer Network (MSDN) 網站：
http://msdn2.microsoft.com/zh-tw/library/ms345408.aspx (http://msdn2.microsoft.com/zh-tw/library/ms345408.aspx)
回此頁最上方

其他相關資訊

變更部分 SQL Server 系統資料庫位置的必要步驟與變更使用者資料庫位置的必要步驟是不同的。我們將會另行說明這些特殊案例。

注意 SQL Server 7.0 系統資料庫和 SQL Server 2000 不相容。不要將 SQL Server 7.0 master、model、msdb 或散發資料庫附加到 SQL Server 2000。如果您使用的是 SQL Server 2005，則只能將 SQL Server 2005 資料庫附加到執行個體。本文中的所有範例均假定 SQL Server 是安裝在 D:\Mssql7 資料夾中。此外，這些範例亦假定所有資料檔案和記錄檔均位於預設的 D:\Mssql7\Data 資料夾中。這些範例會將所有資料庫的資料檔案和記錄檔移至 E:\Sqldata 資料夾。
回此頁最上方

先決條件

  • 請從所有資料庫的目前位置來製作它們的目前備份，特別是 master 資料庫。
  • 必須具有系統管理員 (sa) 權限。
  • 您必須知道資料庫的所有資料檔案和記錄檔的名稱和目前位置。

    注意 您可以使用 sp_helpfile 預存程序來判斷資料庫所使用的所有檔案的名稱和目前位置：
    use <database_name>
go
sp_helpfile
go
  • 您應具有所移動的資料庫的專用存取權。如果您在過程中遇到問題，且無法存取已移動的資料庫或無法啟動 SQL Server，請檢查 SQL Server 錯誤記錄檔，並參閱《SQL Server 線上叢書》以取得有關所遇到的錯誤的詳細資訊。
回此頁最上方

移動使用者資料庫

下例將會移動名為 mydb 的資料庫。此資料庫包含一個資料檔案 Mydb.mdf 和一個記錄檔 Mydblog.ldf。如果您正在移動的資料庫有更多的資料檔案或記錄檔，請在 sp_attach_db 預存程序中的逗點分隔清單中指定檔案。不論資料庫包含多少檔案，sp_detach_db 程序都不會改變，因為 sp_detach_db 程序不會列出檔案。
  1. 如下所示卸離資料庫：
    use master
   go
   sp_detach_db 'mydb'
   go
  2. 接下來，請將資料檔案和記錄檔從目前的位置 (D:\Mssql7\Data) 複製到新的位置 (E:\Sqldata)。
  3. 重新附加資料庫。指向新位置的檔案，如下所示：
    use master
  go
  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
  go
    使用 sp_helpfile 預存程序來確認檔案位置的變更：
    use mydb
   go
   sp_helpfile
   go
    filename 資料行值應該反映出新位置。
注意「Microsoft 知識庫」文件 922804 將會說明在網路可存取的儲存裝置上的 SQL Server 2005 資料庫。 如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
922804? (http://support.microsoft.com/kb/922804/ ) FIX:After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database
請試想以下問題。此外，請考量當資料庫在 SQL Server 2005 中卸離時，套用至該資料庫的權限。如需詳細資訊，請參閱《SQL Server 線上叢書》中「保護資料和記錄檔的安全」的＜卸離及附加資料庫＞一節。如果要檢視這個主題，請造訪下列 Microsoft Developer Network (MSDN) 網站：
http://msdn2.microsoft.com/zh-tw/library/ms189128.aspx (http://msdn2.microsoft.com/zh-tw/library/ms189128.aspx)
回此頁最上方

移動範例資料庫

如果要移動 SQL Server 2000 或 SQL Server 7.0 中的 pubs 範例資料庫和 Northwind 範例資料庫，或移動 SQL Server 2005 中的 AdventureWorks 範例資料庫和 AdventureWorksDW 範例資料庫，請依照與移動使用者資料庫相同的程序執行。
回此頁最上方

移動 model 資料庫

SQL Server 7.0

  1. 確定 SQL Server Agent 目前沒有執行。
  2. 依照和移動使用者資料庫相同的程序執行。

SQL Server 2005 和 SQL Server 2000

在 SQL Server 2005 和 SQL Server 2000 中，您無法使用 sp_detach_db 預存程序來卸離系統資料庫。當您嘗試執行 sp_detach_db 'model' 陳述式時，收到下列錯誤訊息：
伺服器：訊息 7940，層次 16，狀態 1，行 1
無法卸離 master、model、msdb 以及 tempdb 系統資料庫。
如果要移動 model 資料庫，您必須同時啟動 SQL Server 以及 -c 選項、-m 選項和追蹤旗標 3608。追蹤旗標 3608 可防止 SQL Server 復原 master 資料庫以外的資料庫。

注意 在執行這項操作之後，將無法存取任何使用者的資料庫。當您使用此追蹤旗標時，除了下列步驟，不得執行任何作業。如果要新增追蹤旗標 3608 做為 SQL Server 啟動參數，請依照下列步驟執行：
  1. 在 SQL Server Enterprise Manager 中，用滑鼠右鍵按一下伺服器名稱，然後按一下 [屬性]。
  2. 在 [一般] 索引標籤上，按一下 [啟動參數]。
  3. 新增下列參數：
    -c -m -T3608
如果您是使用 SQL Server 2005，可以使用 SQL Server 組態管理員來變更 SQL Server 服務的啟動參數。如需有關如何變更啟動參數的詳細資訊，請瀏覽下列 Microsoft Developer Network (MSDN) 網站：
http://msdn2.microsoft.com/zh-tw/library/ms190737.aspx (http://msdn2.microsoft.com/zh-tw/library/ms190737.aspx)
在加入 -c 選項、-m 選項和追蹤旗標 3608 之後，請依照下列步驟執行：
  1. 停止然後重新啟動 SQL Server。
  2. 使用下列命令卸離 model 資料庫：
    use master
   go
   sp_detach_db 'model'
   go
  3. 將 Model.mdf 和 Modellog.ldf 檔案從 D:\Mssql7\Data 資料夾移動到 E:\Sqldata 資料夾。
  4. 使用下列命令重新附加 model 資料庫：
    use master
   go
   sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
   go
  5. 從 SQL Server Enterprise Manager 或 SQL Server Configuration Manager 的啟動參數中移除 -c -m -T3608。
  6. 停止然後重新啟動 SQL Server。您可以使用 sp_helpfile 預存程序來確認檔案位置的變更。例如，使用下列命令：
    use model
   go
   sp_helpfile
   go
回此頁最上方

移動 MSDB 資料庫

SQL Server 7.0

注意 如果您在移動 msdb 和 model 資料庫時使用此程序，必須先重新附加 model 資料庫，然後再重新附加 msdb 資料庫。請依照下列步驟執行：
  1. 確定 SQL Server Agent 目前沒有執行。
  2. 依照和移動使用者資料庫相同的程序執行。
注意 如果 SQL Server Agent 正在執行，則 sp_detach_db 預存程序將不會成功，而您會收到下列訊息：
伺服器：訊息 3702，層次 16，狀態 1，行 0
無法卸除資料庫 'msdb'，因為目前正在使用中。
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請聯絡您的系統管理員。

SQL Server 2005 和 SQL Server 2000

如果要移動 MSDB 資料庫，您必須同時啟動 SQL Server 以及 -c 選項、-m 選項和追蹤旗標 3608。追蹤旗標 3608 可防止 SQL Server 復原 master 資料庫以外的資料庫。如果要加入 -c 選項、-m 選項和追蹤旗標 3608，請依照＜移動 model 資料庫＞一節中的步驟執行。在加入 -c 選項、-m 選項和追蹤旗標 3608 之後，請依照下列步驟執行：
  1. 停止然後重新啟動 SQL Server。
  2. 確定 SQL Server Agent 服務目前沒有執行。
  3. 如下所示卸離 msdb 資料庫：
    use master
go
sp_detach_db 'msdb'
go
  4. 將 Msdbdata.mdf 和 Msdblog.ldf 檔案從目前位置 (D:\Mssql8\Data) 移動到新位置 (E:\Mssql8\Data)。
  5. 從 Enterprise Manager 的啟動參數方塊中移除 -c -m -T3608。
  6. 停止然後重新啟動 SQL Server。

    注意 如果您嘗試透過同時啟動 SQL Server 以及 -c 選項、-m 選項和追蹤旗標 3608 來重新附加 msdb 資料庫，可能會收到下列錯誤訊息：
    伺服器：訊息 615，層次 21，狀態 1，行 1
    找不到資料庫的資料表識別碼 3，名稱 'model'。
  7. 如下所示重新附加 msdb 資料庫：
    use master
go 
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' 
go
注意 如果您同時使用這項程序及移動 model 資料庫，就是嘗試在卸離 model 資料庫時卸離 msdb 資料庫。當您執行這項操作時，必須先重新附加 model 資料庫，然後再重新附加 msdb 資料庫。如果您先重新附加 msdb 資料庫，當您嘗試重新附加 model 資料庫時，會收到下列錯誤訊息：
訊息 0，層次 11，狀態 0，行 0
在目前的命令上發生嚴重錯誤。如果有任何結果，都必須捨棄。
在這種情況下，您必須卸離 msdb 資料庫，重新附加 model 資料庫，然後再重新附加 msdb 資料庫。

在您移動 msdb 資料庫之後，可能會收到下列錯誤訊息：
錯誤 229：EXECUTE 使用權限在物件 'ObjectName'、資料庫 'master'、擁有者 'dbo' 上被拒絕。)
發生這個問題是因為擁有權鏈結中斷。msdb 資料庫和 master 資料庫的資料庫擁有者是不同的。在這種情況下，msdb 資料庫的擁有權已經改變。如果要解決這個問題，請執行下列 Transact-SQL 陳述式。使用 Osql.exe 命令列公用程式 (SQL Server 7.0 和 SQL Server 2000) 或 Sqlcmd.exe 命令列公用程式 (SQL Server 2005) 都可以執行這項操作：
USE MSDB 
Go 
EXEC sp_changedbowner 'sa' 
Go
如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
272424? (http://support.microsoft.com/kb/272424/ ) INF: Object Ownership Chain Checking Across Databases Depends on the Login That Is Mapped to the Object Owners
回此頁最上方

移動 master 資料庫

  1. 請在 SQL Server Enterprise Manager 中變更 master 資料檔案和 master 記錄檔的路徑。

    注意 您也可能會在此處變更錯誤記錄檔的位置。
  2. 在 Enterprise Manager 中用滑鼠右鍵按一下 SQL Server，然後按一下 [內容]。
  3. 按一下 [啟動參數] 以查看下列項目：
    -dD:\MSSQL7\data\master.mdf -eD:\MSSQL7\log\ErrorLog -lD:\MSSQL7\data\mastlog.ldf
    -d 是 master 資料庫資料檔案的完整路徑。

    -e 是錯誤記錄檔的完整路徑。

    -l 是 master 資料庫記錄檔的完整路徑。
  4. 如下所示變更這些值：
    1. 移除 Master.mdf 和 Mastlog.ldf 檔案的目前項目。
    2. 新增指定新位置的項目：
      -dE:\SQLDATA\master.mdf
      -lE:\SQLDATA\mastlog.ldf
  5. 停止 SQL Server。
  6. 將 Master.mdf 和 Mastlog.ldf 檔案複製到新位置 (E:\Sqldata)。
  7. 重新啟動 SQL Server。
注意 如果您是使用 SQL Server 2005，請使用 SQL Server Configuration Manager 來變更 master 資料檔案和 master 記錄檔的路徑。
回此頁最上方

移動 tempdb 資料庫

您可以使用 ALTER DATABASE 陳述式來移動 tempdb 檔案。
  1. 如下所示使用 sp_helpfile 來判斷 tempdb 資料庫的邏輯檔案名稱：
    use tempdb
go
sp_helpfile
go
    name 資料行中含有每個檔案的邏輯名稱。這個範例使用 tempdev 和 templog 預設檔案名稱。
  2. 如下所示使用 ALTER DATABASE 陳述式，指定邏輯檔案名稱：
    use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
    您應該會收到下列確認變更的訊息：
    訊息 1
    檔案 'tempdev' 修改於 sysaltfiles。請在重新啟動 SQL Server 之後刪除舊的檔案。
    訊息 2
    檔案 'templog' 修改於 sysaltfiles。請在重新啟動 SQL Server 之後刪除舊的檔案。
  3. 在 tempdb 中使用 sp_helpfile 將會在重新啟動 SQL Server 後才確認這些變更。
  4. 停止然後重新啟動 SQL Server。
回此頁最上方

?考

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
912397? (http://support.microsoft.com/kb/912397/ ) The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid
274188? (http://support.microsoft.com/kb/274188/ ) PRB：線上叢書的疑難排解孤兒使用者主題不完整
246133? (http://support.microsoft.com/kb/246133/ ) 如何在 SQL Server 的執行個體之間傳送登入和密碼
168001? (http://support.microsoft.com/kb/168001/ ) PRB：還原傾印之後發生使用者登入及 (或) 權限錯誤

如需詳細資訊，請參閱下列叢書：
Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit
Microsoft Press, 2001
Microsoft Corporation
MCSE Training Kit:Microsoft SQL Server 2000 System Administration (http://www.microsoft.com/mspress/books/4885.aspx)
Microsoft Press, 2001
Microsoft Corporation
Microsoft SQL Server 2000 Resource Kit (http://www.microsoft.com/mspress/books/4939.aspx)
Microsoft Press, 2001
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
回此頁最上方
關鍵字：?
kbsqldeveloper kbinfo KB224071
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。