Advertisements
Home > DBA Stuff > SQL DBA – Move tempDB to another drive in simple steps

SQL DBA – Move tempDB to another drive in simple steps


 
Today we will see how to move your SQL Server tempDB database from its default location to an another drive. Reason could be anything like Disk maintenance, Failure recovery, etc. This can be done by using the normal ALTER DATABASE statement, we will see how.
 


 

–> Script: Let’s first check the location of master DB:

USE tempdb
GO

SELECT * FROM sys.database_files

Move TempDB 01
 

–> Now execute the ALTER DATABASE statement with MODIFY FILE option:

USE master;  
GO

ALTER DATABASE tempdb   
MODIFY FILE (
	NAME = tempdev, 
	FILENAME = 'E:\SystemDatabases\tempDB\tempdb.mdf'
);
GO

ALTER DATABASE tempdb   
MODIFY FILE (
	NAME = templog, 
	FILENAME = 'E:\SystemDatabases\tempDB\templog.ldf'
);
GO

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.
 

–> Now Restart the SQL Server services from SSMS Object Explorer or by going to RUN –> services.msc.

As soon as the SQL Server services are started, the SQL engine re-creates the tempDB database files. So there is no need to physically move them to the new location.
 

–> To confirm the new location, just execute following query and check the path:

Move TempDB 02
 


Advertisements
Categories: DBA Stuff Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: