移動資料庫物理檔案 Move Physical Files

1. 確定 tempdb 資料庫的邏輯檔名。

USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.

2. 使用 ALTER DATABASE 更改每個檔案的位置。

USE master
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLDatatempdb.mdf')
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLDatatemplog.ldf')
GO

3. 停止並重新啟動 SQL Server。

要移動使用者資料庫，請使用 sp_detach_db 和 sp_attach_db。
EXEC sp_detach_db 'pubs', 'true'
-- copy to others drive
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:Program FilesMicrosoft SQL ServerMSSQLDatapubs.mdf',
@filename2 = N'c:Program FilesMicrosoft SQL ServerMSSQLDatapubs_log.ldf'

在SQL Server 2005中，似乎使用者資料庫也可以用alter database來移動。指令碼：

use databasenName
select name,physical_name from sys.database_files
go

/*** Getting ready to go down to bussiness ****/
--ponint to master db
use Master
go
-- Rollback all trans
--Ensure all users are notify of the down time

alter database DatabaseName set single_user with rollback immediate
go
-- Set databae Offline
alter database DatabaseName set Offline
go
--- Cut and paste the files from the original location to the new location
-- Be sure to write the name and new path exactly
alter database DatabaseName
Modify file (Name = logicalName,Filename = 'DriveLetter:newPathDataFileName.mdf/ndf or ldf')--Create one per every file
go
---Set database backonline
alter database DatabaseName set Online
go
--Set Database back to multi_user
alter database DatabaseName set multi_user
go