如何更改数据库文件的物理存储位置？

Question

在SQL Server应用中，有时候会碰到这样的情况：数据库文件存放的磁盘空间不够，需要把部分的文件挪到其他磁盘上去以分担磁盘开销；老的磁盘读写I/O速度太慢，需要用的新磁盘代替以提高读写速度等。

Answer 1

下面以AdventureWorks2008R2数据库为例，演示了移动数据库文件的操作过程：

 

1. 查看当前数据库文件物理存储位置。

USE AdventureWorks2008R2; 
GO 
SELECT 
 DB_NAME(database_id) AS Database_Name, 
 name AS Logical_Name, 
 physical_name AS Physical_Name, 
 type_desc AS Type_Description 
FROM sys.master_files WHERE database_id = DB_ID(); 
GO

2. 将数据库更改成离线状态。

USE master; 
GO 
ALTER DATABASE AdventureWorks2008R2 SET OFFLINE; 
GO

3. 根据上面的获取的数据库文件物理存储位置，手动将目标文件移动到目标路径。

4. 更新数据库文件物理位置。

USE master; 
GO 
ALTER DATABASE AdventureWorks2008R2 
MODIFY 
FILE (NAME = AdventureWorks2008R2_Data, 
FILENAME = 'C:\AdventureWorks2008R2\AdventureWorks2008R2_Data.mdf'); 
GO

5. 将数据库更改成在线状态。

USE master; 
GO 
ALTER DATABASE AdventureWorks2008R2 SET ONLINE; 
GO

6. 执行第一步操确认文件被成功移动到新的物理位置。

Answer 2

一般这样的情况用Detach 和 attach 命令比较多。没必要更改数据库文件

 1.USE MASTER

  GO

  ALTER DATABASE ‘dbname’  SET OFFLINE;

  GO

2.USE MASTER

GO

sp_detach_db ‘dbname’

GO

3.把数据库文件拷贝到对应的目录上

4.USE MASTER

GO

sp_attach_db ‘dbname’, 'C:\*\sqlDB.mdf', 'C:*\sqlDB_log.ldf'

GO

5.USE MASTER

GO

ALTER DATABASE 'dbname' SET ONLINE

GO

 

Answer 3

Change file path with 'alter database' is safer than detach/attach, and 'alter database' will not change dbid while detach/attach may change that.