Although it is not an activity that I regularly do, I needed to write a script to move TempDB. This was whilst I was testing different RAID arrays on a SAN. The TempDB had quite a few files and I got bored writing out the commands over and again, so came up with a script that will print out the commands to alter the files for TempDB, and if necessary will execute the command also. This will also work for user databases, the caveat being that whilst TempDB will re-create your TempDB on an instance restart, the user database files will have to be moved whilst the database needs to be offline. So that’s some manual grunt that cannot be scripted here. Yeah I know, sucks to be a DBA sometimes…

USE tempdb;
GO

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @EXEC NVARCHAR(MAX) = ''
DECLARE @RUN BIT = 0
DECLARE @newdatalocation NVARCHAR(256) = 'L:\temptempdb\'
DECLARE @newLogLocation NVARCHAR(256) = 'M:\tempdb_log'
DECLARE @fileName NVARCHAR(64)
DECLARE @fileLocation NVARCHAR(256)

DECLARE cur CURSOR
FOR
SELECT f.NAME
,f.filename
FROM sys.sysfiles f

OPEN cur

FETCH NEXT
FROM cur
INTO @fileName
,@fileLocation

PRINT @fileName
PRINT @fileLocation

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'ALTER DATABASE ' + DB_NAME() + ' modify FILE (
NAME = ' + f.NAME + CHAR(10) + ',filename = ' + '''' + CASE
WHEN RIGHT(f.filename, 3) = 'ldf'
THEN @newLogLocation
ELSE @newdatalocation
END + '' + f.NAME + '' + RIGHT(f.filename, 4) + '''' + ')' + CHAR(10)
FROM sys.sysfiles f
WHERE f.NAME = @fileName

PRINT @SQL

SELECT @EXEC = @EXEC + @SQL

FETCH NEXT
FROM cur
INTO @fileName
,@fileLocation
END

CLOSE cur;

DEALLOCATE cur;

IF @RUN = 1
BEGIN
EXEC @EXEC
END

Happy Scripting!