Mahmoud ElMansy: knowledge meant to be free

Moving sys DataBases Recreate missing TempDB


Simply to move your database files you can use script to change file location for any database.

And to make sure that the file path are changed run this script.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

Restart your instance and all done.
Now additionla scenarios
Moving TempDB
If you are moving “due to drive failure”.
If your SQL Instance is some how stopped and you want to move your tempdb that will not work
Cuase sql will create a new tempdb in the exact location that is “if you are lucky and sql service starts”
Because it might not start in the abscnce of tempdb.
In order to move tempdb you must start the SQL Service with -T3608 for the startup option.
The -T3608 as in MSDN
“Prevents SQL Server from automatically start and recovering any database except for the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read Committed snapshot, might not work. Use for Move System Databases and Move User Databases. Do not use During normal operation.”
Means “option start SQL server with no recovery except for master database ”
For more information about TraceFlag
Trace Flags (Transact-SQL)

/*
note it's key sensitave
type this in cmd
*/

NET START MSSQLSERVER /f /T3608
NET START MSSQL$instancename /f /T3608

We can also start sql instance with -T3608 by modifing “Startup Parameters” located in
> SQL Server Configuration Manager > SQL Server (instance_name) > Properties dialog box > Advanced
> Startup Parameters

add

;-T3608

To tha paramter list and restart SQL imstance now you can change tempDB Location and then remove trace Flace and restart sql instance again.
Moving Master DataBase
Or Moving Errorlog location Or sql server default database path
Just modify the insatance “Startup Parameters”

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf

The -d refere to the “master.mdf” file location.
The -l refere to the “astlog.ldf” file location.
Just change the file location to be

-dE:\SQLData\master.mdf
;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG
;-lE:\SQLData\mastlog.ldf

Save and close and restart sql server insance and you are done.