Moving databases files from one folder to another.
Also includes transferring databases across servers

This task can be split into two categories
1. Moving user databases
2. Moving system databases - master, msdb, tempdb, model

Of these moving the user databases is trivial compared to moving system databases.

Background knowledge
SQL Server databases are held in disk files.
As a minimum they require one data file and one transaction log file.
The data file defaults to extension .mdf (further files default to .ndf).
The transaction log file defaults to extension .ldf.
User databases are self contained and so can be moved easily.
Tempdb is created on every restart using the model database as a template
The file locations can be obtained from the table sysfiles in the database.
A database cannot be detached if users are connected.

Caution
Before detaching a database make sure you have a backup - preferrably test restore the backup before the detach.

1. Moving user databases
Manual Move
Make a note of the physical filenames for the database.
Make sure there are no users in the database - if there are then kill them
	select * from master..sysdatabases where dbid = db_id('mydbname')

Detach the database (can use enterprise manager)
	sp_detach_db 'mydbname'
Move the physical files to the new directory
Attach the database (can use enterprise manager)
	sp_attach_db 'mydbname', 'file1loc', 'file2loc', ...
Where filenloc are the physical file locations in the order in which they appear in sysfiles.

Scripted Move

Moving system databases
System databases usually require sql server to be started with trace flag 3608.
This omits the recovery of all databases except master.
It means you cannot do much with the server but allows the location of system databases to be altered.

To start the SQL Server with trace flag 3608
In enterprise manager right click on the server and select properties, click on startup parameters.
Add -T3608 to the list of parameters and save.
Restart SQL Server

Master
The location of the master database is defined by startup parameters.
In enterprise manager right click on the server and select properties, click on startup parameters.
Here you will see the entries for the master data and log files (and errorlog).
Change these and close down the SQL Server.
Move the files to the location specified.
Restart the SQL Server.

MSDB
Ensure that the SQLServer Agent is not set to start automatically with SQL Server
Restart SQL Server with trace flag 3608
Move the MSDB database by detach and attach as with a user database.
Restart SQL Server without trace flag 3608.

TempDB
TempDB is moved via an alter database command
In Master
alter database tempdb modify file (name = tempdev, filename = 'fileloc')
alter database tempdb modify file (name = templog, filename = 'fileloc')
Restart sql server to create the files in the new location.

Model
Restart SQL Server with trace flag 3608
Move the Model database by detach and attach as with a user database.
Restart SQL Server without trace flag 3608.