Post reply

Moving master, model, msdb dbs on SQL 2005

  • July 23, 2007 at 8:18 am

    #153698

    I have restarted SQL with the new parameters -c -m -T3608 but I continue to get the error message:

    use

    master

    go

    sp_detach_db 'model'

    go

    Msg 7940, Level 16, State 1, Line 1

    System databases master, model, msdb, and tempdb cannot be detached.

     

    What's up with that? 

     

    I'm following the instructions from MS below : http://support.microsoft.com/kb/224071

    --------------------------------------------------------------------------

    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, while you 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. For more information about how to change the startup parameters, visit the following Microsoft Developer Network (MSDN) Web site:

    http://msdn2.microsoft.com/en-us/library/ms190737.aspx (http://msdn2.microsoft.com/en-us/library/ms190737.aspx)

    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 D:\Mssql7\Data folder to the E:\Sqldata folder.
    4.Reattach the model database by using the following commands:

    use master   go   sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'   go

    5.Remove -c -m -T3608 from the startup parameters in SQL Server Enterprise Manager or in SQL Server Configuration Manager.
    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

  • July 23, 2007 at 11:30 am

    #721587

    yes sometimes even i faced this issue but it always works from command prompt.........just start sql server in single use mode as follows,

    net start mssqlserver /c /m /T3608

    then detach the system dbs you will get it............cool

    [font="Verdana"]- Deepak[/font]

  • July 24, 2007 at 12:28 am

    #721772

    THat works!  Thanks, Deepak.

    I'm surprised such a fundamental bug exists even at SP2 level. And that there aren't hundreds of posts already on this problem.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply