如何移動已存在的SQL Server 2005中的user database

有時候主機的硬碟空間不足或是因為要調整硬碟配置, 會動到資料庫的存放位置, 這裡有一篇 kb 在處理這個作業.

移動使用者資料庫

內容說明蠻詳細的, 我這裡簡單列出操作的方式:

1. 先找出該 db 的所有檔案, 利用以下指令(其中USERDB是假設的使用者資料庫名稱):

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'USERDB')

(基本上至少會有兩個, 一個是放 data 的 mdf, 一個是放 log 的 ldf 檔案的所在位置)

2. 接下來將資料庫先離線:

ALTER DATABASE USERDB SET OFFLINE;

(可能會花一些時間)

3. 再來就是將資料庫的實體檔案搬移到指定位置, 這裡假設是 d:\sqldata\

4. 接下來就是將資料庫的 metadata 利用 alter database 指令進行調整:

ALTER DATABASE USERDB
    MODIFY FILE
 ( NAME = USERDB,
                  FILENAME = 'd:\sqldata\USERDB.mdf');
ALTER DATABASE USERDB
    MODIFY FILE
( NAME = USERDB_log,
                  FILENAME = 'd:\sqldata\USERDB_log.ldf');

5. 將資料庫上線:

ALTER DATABASE USERDB SET ONLINE;

6. 再次確認資料庫檔案所在位置:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'USERDB')

7. 進行資料庫測試, 例如 select 一些資料, 確認沒問題後, 即完成.

這個作業其實偶而會用到, 而且機會不低, 尤其是檔案大小成長速度快的, 非常實用, 給大家參考, 另外這篇雖然是 SQL Server 2005 的資料, 不過 SQL Server 2000 也同樣適用. 當然, 使用 backup / restore 的方式也沒問題, 只是用這個方式會快得多了. 另外使用 detach / attach db 的方式也是一種做法(後面資料也有)

延伸閱讀:
如何在執行 SQL Server 的電腦之間移動資料庫
简化在同一时段移动SQL Server系统数据库的过程
如何使用 SQL Server 中的卸離和附加功能將 SQL Server 資料庫移到新位置 (這篇資料也包含了移動系統資料庫, 包含 SQL Server 7.0, 2000, 2005)