How to Relocate MSSQL tempdb Files via SQLCMD

How to Relocate Microsoft's SQL tempdb Files:

Using SQLCMD.EXE		To change the location using the command-line tool SQLCMD.EXE, open a command-prompt window with Administrator privileges, and cd to the instance's Binn directory. Then follow one of the three methods shown below: Note: In the examples below,  'MY_SERVER' is the name of the server running SQL.  'SQL_INSTANCE' is the name of the SQL instance.
First Method: Scripted		Create the two script files, 'ChangeLocation.sql' and 'ReportLocation.sql', with the content shown in the tables above. Modify the file path specification appropriately. To execute the scripts, open a command prompt and enter the commands shown below: sqlcmd -S "MY_SERVER\SQL_INSTANCE" -i "ChangeLocation.sql"Changed database context to 'master'. The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. sqlcmd -S "MY_SERVER\SQL_INSTANCE" -i "ReportLocation.sql" name physical_name ------------------ ------------------- tempdev R:\temp\tempdb.mdf templog R:\temp\templog.ldf (2 rows affected)
Second Method: Interactive		Execute the commands in an interactive SQLCMD session, as shown below (modify the file path specification appropriately): sqlcmd -S "MY_SERVER\SQL_INSTANCE" 1> USE master 2> go Changed database context to 'master'. 1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf') 2> go The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. 1> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf') 2> go The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. 1> quitsqlcmd -S "MY_SERVER\SQL_INSTANCE" 1> SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb') 2> go name physical_name ------------------ ------------------- tempdev R:\temp\tempdb.mdf templog R:\temp\templog.ldf (2 rows affected)   1> quit
Third Method: Command-Line Query		The query commands can be concatenated for use in a single argument on the command line, as shown below (modify the file path specification appropriately): sqlcmd -S "MY_SERVER\SQL_INSTANCE" -Q"USE master;ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf');ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf');" Changed database context to 'master'. The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started. sqlcmd -S "MY_SERVER\SQL_INSTANCE" -Q"SELECT name, physical_name;FROM sys.master_files;WHERE database_id = DB_ID('tempdb');" name physical_name ------------------ ------------------- tempdev R:\temp\tempdb.mdf templog R:\temp\templog.ldf (2 rows affected)