SQLServer TempDB迁移

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的文件手动删除