Back up the original file before transferring move tempdb from its current location on disk to a different disk location. Because tempdb is recreated every time the MSSQLSERVER service is started, you do not need to move data and log files physically. 1 Determine the logical file name of the tempdb database and the current location on the disk. SELECT name, physical_namefrom sys.master_fileswhere database_id = db_id (' tempdb '); GO 2 use Alter DATABASE to change the location of each file using Master; Goalter DATABASE tempdbmodify FILE (NAME = tempdev, FILENAME = ' E:\SQLData\tempdb.mdf '); Goalter DATABASE tempdbmodify FILE (NAME = templog, FILENAME = ' E:\SQLData\templog.ldf '); GO 3 Stop and restart SQL Server. 4 Verify file changes. SELECT name, physical_namefrom sys.master_fileswhere database_id = db_id (' tempdb ');
SQL Server transfers the physical storage location of the tempdb database
