sqlserver迁移数据库文件存储位置的详细教程_MsSqlserver

业务背景：由于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 语句失败。

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