sql server修改数据库文件位置的详细步骤记录

目录

• 背景：
• 步骤
• 1.备份数据库
• 2.先将所需更改的数据库的路径先查询出来再进行关闭
• 3.关闭该数据库
• 4.再次查询发现已经是离线
• 5.移动数据库文件（文件位置我移到D盘，根据个人情况。文件夹可以不需要建那么长）
• 6.开启数据库时提示报错信息
• 7.手动拖过去即可
• 8.然后移动过去又报操作系统错误5：“5（拒绝访问。）”
• 9.这是需要将移动到D盘的数据文件中user的权限改为完全控制，（2个文件都需要这么操作，不然也会报错）然后再次执行
• 10.执行成功
• 总结

BACKUP DATABASE [数据库名]
TO DISK ='D:\Backup\数据库名.bak'

USE  [数据库名];
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(' [数据库名]');

USE master;
GO
ALTER DATABASE [数据库名] SET OFFLINE WITH ROLLBACK IMMEDIATE; 
GO

USE  [数据库名];
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(' [数据库名]');

USE master;
GO
ALTER DATABASE ReportServer
MODIFY FILE(NAME = 'ReportServer', FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf');
GO
ALTER DATABASE ReportServer
MODIFY FILE (NAME = 'ReportServer_log', FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ReportServer_log.ldf');
GO

USE master;
GO
ALTER DATABASE ReportServer SET ONLINE;
GO