1.查询目前TempDB的位置
select * FROM sys.master_files WHERE database_id = DB_ID('tempdb');
2.迁移的到目标的权限问题，需要赋予sqlserver服务启动账号的权限读写，不然改完之后重启数据库时会报错误代码1814
3.在做cluster模式下，需要将SQLserver属性将新加的盘加入其中，不然会包如下错误
Cannot use file 'G:\TempDB\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
4.进行语句修改
select name,'ALTER DATABASE tempdb MODIFY FILE (NAME= '+name+' , FILENAME= '''+REPLACE(physical_name,'d:\MSSQL15.MSSQLSERVER\MSSQL\DATA\','G:\TempDB\') +''')'from sys.master_files where database_id=db_id('tempdb')
ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev , FILENAME= 'G:\TempDB\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp2 , FILENAME= 'G:\TempDB\tempdb_mssql_2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp3 , FILENAME= 'G:\TempDB\tempdb_mssql_3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp4 , FILENAME= 'G:\TempDB\tempdb_mssql_4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp5 , FILENAME= 'G:\TempDB\tempdb_mssql_5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp6 , FILENAME= 'G:\TempDB\tempdb_mssql_6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp7 , FILENAME= 'G:\TempDB\tempdb_mssql_7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp8 , FILENAME= 'G:\TempDB\tempdb_mssql_8.ndf')
5.修改过程中出现等待时间长，是因为有活动的tempdb,需要改成单用户模式进行，最后再改回多用户模式
单用户
use master
alter database 数据库名
set single_user
with rollback immediate
多用户
use master
alter database 数据库名
set multi_user
6.修改完成后，SQL服务重启
7.将原来的TempDb的文件手动删除
标签：TempDB,MODIFY,SQLServer,tempdb,FILENAME,FILE,迁移,ALTER From： https://www.cnblogs.com/cheng-qiang/p/17115334.html