The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob

  Wednesday, September 19, 2018

 


Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

 

Halloween_PumkinPatch 2007 _19.JPG

  bob Links
The myth of laying d...
They who have drunk beer…fall on their back…but there is peculiarity in the effects of the drink made from barley…for they that get drunk on other intoxicating liquors fall on all parts of their body, they fall on the left side, on the right side, on their faces, and on their backs. But it is only those who get drunk on beer that fall on their backs with their faces upward. - Aristotle
Wider View Insert

Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
SQL Server
     Temporary Database
     Views
     Backup
     SQL Mail
     DTS - Data Transformation Services
     Memory
     Security
     Excel
     Jobs SQL Agent
     MSDE
     Install
     DBCC
     User Management
     System Databases
          Crib Sheet - SQL Ser...
          Moving the MSDB Data...
          Moving the Model Dat...
          Moving the master da...
     BCV
     Deadlocks
     Temp Tables
Windows OS

Insert Category

Will be added as a sub-category of, System Databases
Moving the Model Database

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