Monday, February 4, 2013

How to Relocate MSSQL TempDB via SQL

How to Relocate Microsoft's SQL tempdb Files:

QUERY TO CHANGE FILE LOCATION (ChangeLocation.sql)
USE master 
go 
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf') 
go 
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf') 
go

QUERY TO REPORT FILE LOCATION (MyReportScript.sql)
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb')
go

No comments: