Skip to content

Instantly share code, notes, and snippets.

@petesql

petesql/move_temptb_sql_server.sql

Created October 22, 2022 12:34
Show Gist options
  • Save petesql/9f43f86145f4971b2cdffd07ba293952 to your computer and use it in GitHub Desktop.
Save petesql/9f43f86145f4971b2cdffd07ba293952 to your computer and use it in GitHub Desktop.
Download ZIP
Generate script, alter database move tempdb in SQL Server
Raw
move_temptb_sql_server.sql
-- generate script, alter database move temp db
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''D:\mssql_temp_db\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END + ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');
@petesql
Copy link
Author

petesql commented Oct 22, 2022 •
edited
Loading

My blog post that includes the use of this script: Move TempDB in SQL Server, 20/10/2022

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment