Post reply

Moving Master database using command line (Sql Server 2005)

  • July 10, 2014 at 1:31 pm

    #397040

    Is it possible to move a master database data and log files from one directory to another(Same Server) using the command line?

    The version of SQL Server is 2005.

    Thanks.

  • July 11, 2014 at 3:09 am

    #1728640

    To move master you'll also need to move the resource database too. Move master using the following

    • move the MASTER DB first.
    • edit the SQL server service and change the paths for the MASTER data and log files to the new location.
    • The resource DB mdf and log must be in the same location as master.mdf
    • Stop the sql instance and agent and copy the database files

      (master and mssqlsystemresource DB files) to their new locations.

    • start the sql instance using the following

      NET START MSSQLSERVER /f /T3608 --case sensitive

    • set the MSSQLSYSTEMRESOURCE DB paths using the following T-SQL via a SQLCMD shell. Syntax is

      SQLCMD -Sserver\instancename

      ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data ,

      FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.2

      \MSSQL\Data\mssqlsystemresource.mdf' )

      ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log ,

      FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.2

      \MSSQL\Data\mssqlsystemresource.ldf' )

    • then run

      ALTER DATABASE mssqlsystemresource SET READ_ONLY

    • stop the sql instance from the same command window using

      NET STOP .........

    • restart SQL instance normally

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 11, 2014 at 8:14 am

    #1728763

    Thanks so much for your response, I appreciate it.

    I think to edit the SQL server service and insert the paths to the new location of the Master data and log files, I have to use the SQL Server Configuration Manager.

    Unfortunately, I am unable to connect to the sql server configuration Manager using the GUI. I get the following error when I try to connect to it:

    Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.

    The proposed solution to the problem was to recompile the Mofcomp file. I did the recompilation but the error still persist.

    Is there a way to edit the sql server service using a script?

  • July 11, 2014 at 11:31 am

    #1728844

    You can edit the parameters via the registry, take a backup of the keys before you change them

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 11, 2014 at 1:25 pm

    #1728880

    That's exactly what I did. Thanks for all your help.

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

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