How to move tempdb database files and user databases to new location?

Using the steps below one can change the location of the datafiles in SQLServer for user databases and tempdb.

1) First find the current location of the filename and name:
select name, filename from sys.sysfiles
name filename
—————— ———————————–
tempdev C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
templog C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf

2) Run the ALTER DATABASE command to change path
ALTER DATABASE tempdb MODIFY FILE (Name=tempdev, FILENAME=’E:\SQLServer\Data\tempdb.mdf’);
ALTER DATABASE tempdb MODIFY FILE (Name=templog, FILENAME=’E:\SQLServer\Log\templog.ldf’);

3) Stop and Start SQLServer Services
net stop MSSQLSERVER

4) Move the file from the old location to new location

5) Start SQLServer services
net start MSSQLSERVER

6) Verify path of the file has changed
select name, filename from sys.sysfiles

name filename
—————— ———————————–
tempdev E:\SQLServer\Data\tempdb.mdf
templog E:\SQLServer\Log\templog.ldf

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