Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 11 April 2017

How to move Tempdb to New Drive Location in SQL Server

How to move Temp db to New Drive Location in SQL Server

We are now going to see demo of  temp db move to new location


Old location:F:\Tempdb\tempdev.mdf and F:\Tempdb\templog.ldf

New location:D:\TempDB Source


Current temp db location see below


database file and log file and backup file location see below


we can see the new temp db location path



you can see old temp db location of mdf and ldf






USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\TempDB Source\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\TempDB Source\templog.ldf');



Stop and Restart SQL Server Service




after successful restart services temp db automatically created on new path we altered

you can see temp db mdf and ldf located on new path




but after movement we should need to remove existing tempdb MDF and LDF manually because it should existing on old path


we should need to remove old temp db mdf and ldf from old location


You can see we have successfully removed Old Temp db MDF and LDF from old path




That is it

Keep Blogging and Reading!!!!!!!!!!!!!