羅 朝淇的部落格

本文將告訴您，如何變更任何 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 線上叢書》以取得有關所遇到的錯誤的詳細資訊。

回此頁最上方

移動使用者資料庫

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

use mydb
   go
   sp_helpfile
   go

注意「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)

回此頁最上方

移動範例資料庫

回此頁最上方

移動 model 資料庫

SQL Server 7.0

1.
確定 SQL Server Agent 目前沒有執行。

2.
依照和移動使用者資料庫相同的程序執行。

SQL Server 2005 和 SQL Server 2000

伺服器：訊息 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

1.
確定 SQL Server Agent 目前沒有執行。

2.
依照和移動使用者資料庫相同的程序執行。

注意 如果 SQL Server Agent 正在執行，則 sp_detach_db 預存程序將不會成功，而您會收到下列訊息：

伺服器：訊息 3702，層次 16，狀態 1，行 0
無法卸除資料庫 'msdb'，因為目前正在使用中。
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請聯絡您的系統管理員。

SQL Server 2005 和 SQL Server 2000

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

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

4.
如下所示變更這些值：

a.
移除 Master.mdf 和 Mastlog.ldf 檔案的目前項目。

b.
新增指定新位置的項目：

-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 資料庫

1.
如下所示使用 sp_helpfile 來判斷 tempdb 資料庫的邏輯檔案名稱：

use tempdb
go
sp_helpfile
go

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。