How to Move SQL server system databases to a new location

Moving the model database

SQL Server 2005 and SQL Server 2000

In SQL Server 2005 and in SQL Server 2000, you cannot detach system databases by using the sp_detach_db stored procedure.  When you try to run the sp_detach_db 'model' statement, you receive the following error message:

Server: Msg 7940, Level 16, State 1, Line1
System databases master, model, msdb, and tempdb cannot be detached.

To move the model database, you must start SQL Server together with the -c option the -m option, and trace flag 3608.  Trace flag 3608 prevents SQL Server from recovering any database except the master database.

Note You will not be able to access any user databases after you do this.  You must not perform any operations, other than the following steps, whily yuou use this trace flag.  To add trace flag 3608 as a SQL Server startup parameter, follow these steps:

1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter: -c -m -T3608

If you are using SQL Server 2005, you can use SQL Server Configuration Manager to change the startup parameters of the SQL Server service.

It is also possible to start SQL Server from a command prompt and add these flags to the command line (find path - for a default instance):
c:\program files\microsoft sql server\mssql.1\binn\sqlservr.exe -c -m -T3608

After you add the -c option, the -m option, and trace flag 3608, follow these steps:

1. Stop and then restart SQL Server.
2. Detach the model database by using the following commands:
   use master go sp_detach_db 'model' go
3. Move the Model.mdf and Modellog.ldf files from the old location to the new location
4. Reattach the model database by using the following commands:
   use master o sp_attach_db 'model','e:\sqldata\model.mdf','e:\sqldata\modellog.ldf' go
5. Remove -c -m -T3608 from the startup parameters or ctrl-c to end command prompt (stopping SQL Server)
6. Stop and then restart SQL Server.  You can verify the change in file locations by using the sp_helpfile stored procedure.  For example, use the following command:
   use model go sp_helpfile go