Moving TempDB in an easier way

Background: It is a well-known best practice to have the TempDB files on their own disk. Problem: It is not always the case that TempDB is placed to the right disk, and sometimes there is a need to move the TempDB to a new location. The problem is that when we move the TempDB files we have to alter the database, then restart the SQL Server service so the TempDB can be re-created at the new location. However, if the TempDB files fail to be created, then the entire SQL Server instance will not start. Solution: I have written the following script which will help you move the TempDB files in an easier way and will help you avoid down time.

Read the DISCLAIMER first, though:

This script is developed by Feodor Georgiev, rights reserved, but not limited. Use at your own risk and after proper review and testing. The following script helps you move the tempdb to a new location. The script should be executed in a ”Results to text” mode in the Management Studio, and it will print out step-by-step instructions on how to prepare and move the tempdb. The script does not make changes to your system, it only prints out instructions and a script which will help you move the TempDB files in a easier way.

Here is the script. Remember to run it in ‘Results to Text’ and save it to a text file for future reference.

SET NOCOUNT ON PRINT '****************************************************************************' PRINT 'This script is developed by Feodor Georgiev, rights reserved, but not limited. Use at your own risk and after proper review and testing. The following script helps you move the tempdb to a new location. The script should be executed in a ''Results to text'' mode in the Management Studio, and it will print out step-by-step instructions on how to prepare and move the tempdb. The script does not make changes to your system, it only prints out instructions and a script which will help you move the TempDB files in a easier way. ' PRINT '****************************************************************************' PRINT '' -- set the destination folder for the tempdb declare @tempDBNewFolder nvarchar(500) set @tempDBNewFolder = 'E:SysTempdb' -- make sure you enter the trailing slash '' -- check if the folder exists declare @file_path nvarchar(500) declare @file_exists int set @file_path = @tempDBNewFolder + 'nul' exec master.dbo.xp_fileexist @file_path, @file_exists output print 'Step 1: Folder '+isnull(@tempDBNewFolder,'NULL')+' '+ case when @file_exists = 1 then 'exists' else 'DOES NOT EXIST!!! Do not make changes to the database! ' end -- find the sql server account DECLARE @ServiceaccountName varchar(250) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEMCurrentControlSetServicesMSSQLSERVER', N'ObjectName', @ServiceAccountName OUTPUT, N'no_output' PRINT '' PRINT 'Step 2: make sure that the ' + @ServiceaccountName + ' account has proper permissions to read and write to the ' + @tempDBNewFolder + ' folder.' -- generate the alter database script PRINT '' PRINT 'Step 3: Copy the following script which will alter the TempDB location. ' PRINT 'NOTE: Make sure that the step 1 and 2 bove are complete correctly, otherwise your SQL Server may not start after restarting the serrvice.' PRINT '' SELECT 'USE master GO' UNION ALL SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = '+ mf.name + ', FILENAME = '''+ @tempDBNewFolder + reverse(left(reverse(physical_name), charindex('', reverse(physical_name)) -1))+''') GO' from sys.master_files mf WHERE [database_id] = 2 PRINT '' PRINT 'Step 4: After copying the above script, paste it in a new window of the Management Studio and review the script before executing. ' PRINT '' PRINT '!!! EXECUTE AT YOUR OWN RISK !!! ' PRINT '' PRINT 'Step 5: WHAT IF module: After making changes to the TempDB, you will need to restart the SQL Server service. If the service does not start, you have two options: 1) buy a one-way ticket to a warm country and stay there forever :) 2) open the CMD as an administrator, run ''NET START ServiceName /f /T3608'' (no quotes and replace the ServiceName) and then use ''SQLCMD –S MachineInstance'' to connect to the machine and run a script to alter the tempdb files to a proper location. '

What is important here is the method of moving the TempDB: there are many sites offering the script to move the files, but none of them mention that it is a good idea to check if the folder exists and that the SQL Server service account has permissions to read and write to it. Good luck!