SQLServer修改系统库的路径

当前系统库路径为C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data，指定迁移到路径D:\db

--修改到指定路径
ALTER DATABASE distribution MODIFY FILE (NAME = ‘distribution‘,  FILENAME = ‘D:\db\distribution.mdf‘)
ALTER DATABASE distribution MODIFY FILE (NAME = ‘distribution_log‘,  FILENAME = ‘D:\db\distribution.LDF‘) 

注意，如果是tempdb系统库迁移，可能会有多个ndf日志文件

--修改到指定路径
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘tempdev‘,  FILENAME = ‘D:\db\tempdb.mdf‘)
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘templog‘,  FILENAME = ‘D:\db\templog.ldf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp2‘,  FILENAME = ‘D:\db\tempdb_mssql_2.ndf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp3‘,  FILENAME = ‘D:\db\tempdb_mssql_3.ndf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp4‘,  FILENAME = ‘D:\db\tempdb_mssql_4.ndf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp5‘,  FILENAME = ‘D:\db\tempdb_mssql_5.ndf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp6‘,  FILENAME = ‘D:\db\tempdb_mssql_6.ndf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp7‘,  FILENAME = ‘D:\db\tempdb_mssql_7.ndf‘) 
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘temp8‘,  FILENAME = ‘D:\db\tempdb_mssql_8.ndf‘)  

迁移

关闭MSSQLSERVER服务

将相关文件拷贝到新的地址

开启MSSQLSERVER服务

验证

use distribution
go
 
--查询逻辑名与当前路径
SELECT name, physical_name 
FROM sys.database_files

 原C盘的文件可以正常删除了。

SQLServer修改系统库的路径

标签：mic   name   信息   ros   开启   容量   src   pdb   bsp