Post reply

Moving data and log files

  • September 14, 2010 at 3:22 am

    #240276

    Hi,

    I'm currently moving data and log files for databases from one location to another. I have moved all the user databases and I'm now moving the system ones. I've managed to move the model database files ok but I'm currently having problems moving the files for the msdb database. As outlined by Microsoft, I've carried out the following steps.

    - Opened SQL Server Configuration Manager and gone into SQL Server 2005 Services

    - Right-clicked, opened Properties, gone into the Advanced tab and then clicked inside Startup Parameters

    - Within here, I've amended the text to -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -c -m -T3608 with the 3 flags at the end

    - Stopped and restarted the service within Configuration Manager

    - I then ran the SQL statment sp_detach_db 'msdb' to detach the database but it is still coming up with the message Msg 7940, Level 16, State 1, Line 1

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

    Any ideas why this isn't working?

  • September 14, 2010 at 3:37 am

    #1220540

    the process you are describing is for moving the master database not msdb.

    msdb can be moved with the alter database modify file command and restarting SQL in the normal way. i.e.

    ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'new location\MSDBData.mdf' )

    ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = 'new location\MSDBLog.ldf' )

    When you move the master do not add the single mode parameters to the properties tab, start SQL from the command line with the command:

    NET START MSSQLSERVER /f /T3608

    when you move master you MUST move the resource database to the same location.

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

  • September 14, 2010 at 9:56 am

    #1220797

    George is right. Please check the below link

    http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx

    Thank You,

    Best Regards,

    SQLBuddy

  • September 15, 2010 at 10:23 am

    #1221503

    Thanks for your replies, this has now worked fine.

  • September 15, 2010 at 11:07 am

    #1221544

    thanks for the feedback

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

Viewing 5 posts - 1 through 4 (of 4 total)

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