sqlserver迁移数据库文件存储位置

业务背景：由于C盘爆满，需要将数据库文件迁移到别处比如D盘

下面以某一个数据库转移为示例：（可以用SSMS工具，新建查询配合使用）

1.查询数据库文件存储路径

sql语句：

-- 查询路径
USE QiangTest;
GO
SELECT name As [Database Name],
physical_name As [File Path],
type_desc As [File Type],state_desc
FROM sys.master_files
WHERE database_id= DB_ID('QiangTest');

也可以右键数据库 => 属性 => 文件 查看

2.关闭数据库（脱机）

sql语句：

-- 关闭数据库
USE master;
GO
ALTER DATABASE QiangTest SET OFFLINE WITH ROLLBACK IMMEDIATE; 
GO

3.转移数据库文件(做好备份)

因为不脱机会导致转移复制数据库文件时被使用

也可以在任务管理器停止MSSQLSERVER服务（不推荐），完成后记得开始服务

根据查询路径找到两个文件复制到新路径位置

4.更改数据文件路径

sql语句：

-- 更改路径
USE master;
GO
ALTER DATABASE QiangTest
MODIFY FILE(NAME = 'QiangTest', FILENAME = 'C:\DB\QiangTest.ndf');
GO
ALTER DATABASE QiangTest
MODIFY FILE (NAME = 'QiangTest_log', FILENAME = 'C:\DB\QiangTest_log.ldf');
GO

5.开启数据库

sql语句：

-- 开启数据库
USE master;
GO
ALTER DATABASE QiangTest SET ONLINE;
GO

6.其他的一些报错问题

(1)

解决方法：检查转移的数据库文件名要一致

(2)

消息 5120，级别 16，状态 101，第 29 行 无法打开物理文件“D:\db\Test\RedempWebTest.mdf”。操作系统错误 5:“5(拒绝访问。)”。 消息 5120，级别 16，状态 101，第 29 行 无法打开物理文件“D:\db\Test\RedempWebTest_1.ldf”。操作系统错误 5:“5(拒绝访问。)”。 消息 5181，级别 16，状态 5，第 29 行 无法重新启动数据库“RedempWebTest”。将恢复到以前的状态。 消息 5069，级别 16，状态 1，第 29 行 ALTER DATABASE 语句失败。

解决方法：这些问题都是权限问题，找到文件右键 => 属性 => 安全 => 找到组或用户名 => 编辑 => 添加完全控制权限