Monday, August 20, 2012

How to Move the tempdb in SQL Server

database Any time we setup SQL Servers here at work, I need to do this very task and can never remember the syntax. So, here it is in case anyone else has memory loss too.

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'Y:\SQLLogs\templog.ldf')
GO






Once you run these SQL statements, you will need to stop and restart the SQL Server service. If you want to see the new location of the files, you can run the following script or look at the folder you told it to move to. ;-)

SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')






Hope this helps someone!

No comments: