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