SqlServer修改数据库文件及日志文件存放位置

https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/move-user-databases?view=sql-server-ver16

--查看当前的存放位置

select database_id,name,physical_name as CurrentLocation,state_desc,size

 from sys.master_files

where database_id=db_id(N'CarsemERPArchive');

----查看文件目录，准备复制到新变目录

--修改文件的存放位置下次启动生效

--testDb为数据库名，

alter database CarsemERPArchive modify file ( name = CarsemERPArchive, filename = 'L:\archiveDB\data\CarsemERPArchive.mdf');

alter database CarsemERPArchive modify file ( name = CarsemERPArchive_log, filename = 'L:\archiveDB\log\CarsemERPArchive_log.ldf');

----start DB offline---

ALTER DATABASE CarsemERPArchive SET OFFLINE WITH ROLLBACK IMMEDIATE; 

---复制文件到新的位置

ALTER DATABASE CarsemERPArchive SET ONLINE;