MSSQL: Move DB files or tempdb

1. Check where  is tempdb stored

USE tempdb
GO
EXEC sp_helpfile
GO

2. Change the folder

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

You can apply this for any DB which you want to move to the other folder. Changing does not take effect immediately, but will be used the next time the database starts up.

Set the database offline:
ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

Use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions.

Move/Copy the files to the new location:
Just copy the DB files to the new location

Bring the database online:
ALTER DATABASE foo SET ONLINE;

3. Work / monitor tempdb

Read: http://technet.microsoft.com/en-us/library/cc966545.aspx#EDAA