迁移数据库文件位置

 

 

--1，修改文件逻辑位置

	--1)查询文件逻辑名，
	SELECT name logical_name, physical_name AS CurrentLocation, state_desc
	FROM sys.master_files
	WHERE database_id = DB_ID('test')

	--2）修改
	ALTER DATABASE test MODIFY FILE ( NAME = Test, FILENAME = 'M:\Program Files\SQL Server2008R2\Test.mdf' )
	ALTER DATABASE test MODIFY FILE ( NAME = Test_log, FILENAME = 'M:\Program Files\SQL Server2008R2\Test_log.ldf' )

--2，脱机或停数据库
ALTER DATABASE test SET OFFLINE

 

–3，物理移动数据库文件到新位置。 –4，联机或启动数据库 ALTER DATABASE test SET ONLINE –5，查看效果 SELECT name logical_name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID('test') –参考：http://msdn.microsoft.com/zh-cn/library/ms345483(v=sql.90).aspx