none
How can I move the system databases?

    Question

  • I have been attempting to detach and reattach the model database.  I tried to set the Trace Flag 3608, but it is having no effect.

    Just to be sure I am setting the flag correctly, here is the startup parameters string in my Configuration Manager:

    -dD:\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\MSSQL.1\MSSQL\DATA\mastlog.ldf;-T3608

    Has the process of moving system databases changed in 2005?
    Wednesday, October 26, 2005 4:56 PM

Answers

  • The following procedure will work in SQL Server 2005 for user databases and all system databases except master.
    1. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' ).
    2. Stop the MSSQLSERVER service or the shutdown the system to perform maintenance.
    3. Move the file to the new location.
    4. Restart the MSSQLSERVER service or the server.

    In my experiments with these steps, I ran into the same problem as you (unable to restart the MSSQLSERVER service).  I discovered that in my ALTER DATABASE statement, I accidently used the logical name for model (modeldev) as the os_file_name instead of the actual physical name model.mdf.  So when the service tried to start, it couldn't find a file name modeldev.mdf in the new location.  Maybe you did something similar?

    Regards,

     

    Tuesday, November 1, 2005 11:49 PM
    Moderator
  • http://groups.google.com/group/microsoft.public.sqlserver/browse_frm/thread/0b7d7df0d981bf73/f072ad960e8ca7a5?hl=en#f072ad960e8ca7a5

    Monday, November 7, 2005 7:22 PM
  • Yes, you're correct. In SQL Server 2005, the only way to move the master database is by changing the startup parameters using the SQL Server Configuration Manager tool.  However, a word of caution here. The Resource database (a new system database in SQL Server 2005) depends on the location of the master database.  If you move the master database, you must also move the Resource database to the same location.

    The current topics in Books Online state that the Resource database cannot be moved. These topics will be updated (available in December for download) and will include the following instructions for moving both the master and resource databases.

    To move the master database, follow these steps.

    1.      From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

    2.      In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.

    3.      In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.

    4.      Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.

    The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

    -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

    If the planned relocation for the master data file is E:\SQLData and the planned relocation for the log file is F:\SQLLog, the parameter values would be changed as follows:

    -dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lF:\SQLLog\mastlog.ldf

    5.      Stop the MSSQLSERVER service.

    6.      Physically move the files to the new location.

    7.      Restart the MSSQLSERVER service.

    8.      Verify the file change.

    SELECT name, physical_name, state_desc

    FROM sys.master_files

    WHERE database_id = DB_ID('master');

    To move the Resource database, follow these steps.

    1. Stop the MSSQLSERVER service if it is started.
    2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>\binn\sqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL. This will start the instance of ssNoVersion for master-only recovery.

    3.   Run these queries. Change the FILENAME path to match the new location of the master database:
     ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');

    4. Make sure the Resource database is set to read-only by running this query: 
       ALTER DATABASE mssqlsystemresource SET READ_ONLY; 
    5.  Physically move the files to the new location.

    6.    Restart the MSSQLSERVER service.


    Regards,

    Monday, November 7, 2005 8:11 PM
    Moderator
  • Hi,

        The following link shud be useful for detaching and attaching the sysdb's.

     

       http://msdn2.microsoft.com/en-us/library/ms345408.aspx

     

     

    Monday, May 28, 2007 2:40 AM
    Moderator

All replies

  • Have you tried these steps from SQL 2005 BOL

    To move a file as part of a planned relocation, follow these steps.

    1. Run ALTER DATABASE database_name SET OFFLINE.

    2. Move the file to the new location.

    3. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name'.

    4. Run ALTER DATABASE database_name SET ONLINE.
    Tim S

    Edit: I have not tried the above steps myself.


    Wednesday, October 26, 2005 5:18 PM
  • I ran:
    ALTER DATABASE model SET OFFLINE

    This is the error message I get:
    Msg 5058, Level 16, State 6, Line 1
    Option 'OFFLINE' cannot be set in database 'model'.

    Wednesday, October 26, 2005 5:26 PM
  • ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf')

    Resulted in the below message; I think you run the above command stop the SQL server and move the file and re-start. ( UN-TESTED USE AT OWN RISK)

    The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.

    Wednesday, October 26, 2005 5:36 PM
  • "UN-TESTED USE AT OWN RISK"

    Words to live by.  I couldn't restart the MSSQLSERVER service after running the ALTER DATABASE command.  I ended up having to rebuild the master database.  Oh well.  I was able to move everything else, so it's not the end of the world if I can't move this one.

    Thanks for the assistance.

    Wednesday, October 26, 2005 7:22 PM
  • The following procedure will work in SQL Server 2005 for user databases and all system databases except master.
    1. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' ).
    2. Stop the MSSQLSERVER service or the shutdown the system to perform maintenance.
    3. Move the file to the new location.
    4. Restart the MSSQLSERVER service or the server.

    In my experiments with these steps, I ran into the same problem as you (unable to restart the MSSQLSERVER service).  I discovered that in my ALTER DATABASE statement, I accidently used the logical name for model (modeldev) as the os_file_name instead of the actual physical name model.mdf.  So when the service tried to start, it couldn't find a file name modeldev.mdf in the new location.  Maybe you did something similar?

    Regards,

     

    Tuesday, November 1, 2005 11:49 PM
    Moderator
  • http://groups.google.com/group/microsoft.public.sqlserver/browse_frm/thread/0b7d7df0d981bf73/f072ad960e8ca7a5?hl=en#f072ad960e8ca7a5

    Monday, November 7, 2005 7:22 PM
  • Yes, you're correct. In SQL Server 2005, the only way to move the master database is by changing the startup parameters using the SQL Server Configuration Manager tool.  However, a word of caution here. The Resource database (a new system database in SQL Server 2005) depends on the location of the master database.  If you move the master database, you must also move the Resource database to the same location.

    The current topics in Books Online state that the Resource database cannot be moved. These topics will be updated (available in December for download) and will include the following instructions for moving both the master and resource databases.

    To move the master database, follow these steps.

    1.      From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

    2.      In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.

    3.      In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.

    4.      Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.

    The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

    -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

    If the planned relocation for the master data file is E:\SQLData and the planned relocation for the log file is F:\SQLLog, the parameter values would be changed as follows:

    -dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lF:\SQLLog\mastlog.ldf

    5.      Stop the MSSQLSERVER service.

    6.      Physically move the files to the new location.

    7.      Restart the MSSQLSERVER service.

    8.      Verify the file change.

    SELECT name, physical_name, state_desc

    FROM sys.master_files

    WHERE database_id = DB_ID('master');

    To move the Resource database, follow these steps.

    1. Stop the MSSQLSERVER service if it is started.
    2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>\binn\sqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL. This will start the instance of ssNoVersion for master-only recovery.

    3.   Run these queries. Change the FILENAME path to match the new location of the master database:
     ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');

    4. Make sure the Resource database is set to read-only by running this query: 
       ALTER DATABASE mssqlsystemresource SET READ_ONLY; 
    5.  Physically move the files to the new location.

    6.    Restart the MSSQLSERVER service.


    Regards,

    Monday, November 7, 2005 8:11 PM
    Moderator
  • OK, I tried the procedure above and successfully moved the master database.  However, when I got to the part about moving the Resource database, I got stumped on #3.  Where do I run the queries? 

     

    I ran the command from #2 and it opened the database in single user mode through the command window, but I am confused about what you mean by "ssNoVersion".  Is this a utility that I can use to run queries?  When I try to connect to the database using SQL Server Management Studio after that point I just get an error that only one administrator can log into the database during single user mode.  The command window won't allow me to type queries.  So how do I run the queries?

    Thursday, May 24, 2007 3:26 AM
  • >> I am confused about what you mean by "ssNoVersion". 

     

    Sorry about that.  ssNoversion should be "SQL Server" .  

     

    >>When I try to connect to the database using SQL Server Management Studio after that point I just get an error that only one administrator can log into the database during single user mode.  The command window won't allow me to type queries.  So how do I run the queries?

     

    You need to start Management Studio without creating a connection.  This allows you to open a query window as the only connection to the instance of SQL Server.  Here are the instructions to do that.

    1. If you still have Management Studio open,  right-click the server (aka the instance of SQL Server)  in Object Explorer, and select Disconnect.  This kills the connection and will allow you to open a query window in single-user mode.  

    However, if you have already closed Management Studio, start Management Studio and in the Connect To Server dialog window, click Cancel.  This opens Management Studio with no connection to an instance of SQL Server.

     

    2. From the toolbar, click File, point to New and select Query with Current Connection.  This will open the Conntect To Server dialog window again.  Click Connect.  A query window is opened with this single connection and you can then enter the statements defined in the remaining steps of the procedure.

     

    Hope that gets you going. 

     

    Regards,

    Gail

    Thursday, May 24, 2007 5:36 PM
    Moderator
  • Gaile,

     

    I tried again with the new instructions and I keep getting the following error.  It doesn't matter if I use Windows or SQL authentication when I try to log in, I get the same message either way.

     

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to BLACKJACK.

    ------------------------------
    ADDITIONAL INFORMATION:

    Login failed for user '<Domain>\<Username>'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I am in the administrators group on the local machine.  I noticed in the command window that when I tried to connect as user sa, the logon attempt was done with the user "NT AUTHORITY\SYSTEM" (if this helps you).
    Friday, May 25, 2007 5:02 AM
  • If you were able to start Management Studio without creating a connection but you are still getting error 18461 when connecting with a query window, then some other application or process has taken the single connection when SQL Server was started. 

     

    There are a couple options to consider.

     

    Option 1:  Disable/shut down the application/process that has the connection

    1. Stop the SQL Server service and restart it.  This will start SQL Server in multi-user mode.

    2. From Management Studio, run EXEC sp_who  from a query window and see if you can figure out what application/processes are connected.  You'll need to shut down any application/process that are making a connection.  Or, you can disable remote connections temporarily on the server so that the app/process cannot access SQL Server when you restart the service.  

    You can disable remote connections by using the Surface Area Configuration tool.  From the Surface Area Configuration tool, select Surface Area Configuration for Services and Connections. Click Remote Connections and then select Local connections only. Click Apply. Stop the MSSQLSERVER service and then restart it using the  single-user mode command  NET START MSSQLSERVER /f /T3608. 

    You should then be able to make your single-user connection to a query window.  It might be easier to connect to SQL Server by using the sqlcmd utility.  To do this, open a command prompt and enter sqlcmd.   It will connect to SQL Server using your Windows account. If it connects successfully, you'll get a 1> prompt.  Simply enter your ALTER DATABASE statements at the 1> prompt and then press Enter GO, Enter, etc.  Type Exit to close sqlcmd. 

     

    Option 2: Use the Dedicated Administrator Console (DAC) connection.

    Instead of Option 1, you could connect to SQL Server using the DAC utility.  The following instructions  will only work if you are a sysadmin and the app/process that has the current connection is NOT using a sysadmin login.  It won't hurt to try this option.  If the current connection is a sysadmin login, you'll just get error 18461 again and you're no worse off.

     

    1. From a command prompt, type sqlcmd –A –d master .  Like the sqlcmd utility, you'll get a 1> prompt.  Enter your ALTER DATABASE statements at the 1> prompt and then press Enter GO, Enter, etc.  Type Exit to close the DAC. 

     

    Option 3:  Move the master database back to it's default location.

    If options 1 and 2 don't work, you could reverse your previous steps and move the master database back to the default location.  This would at least get you back to where you were and give you a chance to figure out what connections are being made to your server.

     

    Hope that helps,

     

    Gail

    Friday, May 25, 2007 9:08 PM
    Moderator
  • Hi,

        The following link shud be useful for detaching and attaching the sysdb's.

     

       http://msdn2.microsoft.com/en-us/library/ms345408.aspx

     

     

    Monday, May 28, 2007 2:40 AM
    Moderator