sqlserver将database从C盘迁移到E盘

--备份
backup database hyyy
to disk ='E:\Microsoft SQL Server\BackUp\hyyy.bak';

--查询路径
select name as [databaseName],physical_name as[file Path]
,type_desc as [file type],state_desc
from sys.master_files
WHERE database_id=DB_ID('hyyy')
--hyyy    C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\hyyy.mdf    ROWS    ONLINE
--hyyy_log    C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\hyyy_log.ldf    LOG    ONLINE

--关闭数据库
USE master;
go
alter database hyyy set OFFLINE with rollback IMMEDIATE;
go
--移动数据库
alter database hyyy modify file(name ='hyyy',filename ='E:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\hyyy.mdf');
alter database hyyy modify file(name ='hyyy_log',filename ='E:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\hyyy_log.ldf')

--开启数据库
alter database hyyy set online

参考博客：https://www.jb51.net/database/323722bj5.htm