Moving System Databases

  • Steve Jones - Editor (3/21/2008)


    I'll reword and change it around.

    Hi Steve,

    I hope everything went allright with the kid's tooth 🙂

    I have to agree with Joshua Perry on the new version of the question. All answer possibilities involve detaching and attaching - and whereas it IS possible to move a system database in that way, and I did indeed post a link to an MSKB article describing some gotchas with that approach, there is a far easier way documented in Books Online:

    1) ALTER DATABASE to modify the files for msdb;

    2) Shutdown the server;

    3) Physically move the database files;

    4) Restart the server;

    5) Check that all has gone right.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • In addition to the question changing, it seems one of the answers (C) was correct for SQL 7.0 (as described in 224071 - Moving the MSDB database - SQL Server 7.0) and the version of SQL was not specified in the question.

    Just adding my 2 cents to get my point back 😉

  • Not that I care, but it is one confusing question & answer

    I have actually done this, and this is my SQL script for all-system-DB's-except-master and they did work, no trace flags used

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

    -- STEP 1Run and STOP SQL Server

    -- model

    ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'X:\SQLDATA\model.mdf')

    ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'Y:\SQLLOGS\modellog.ldf')

    -- msdb

    ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'X:\SQLDATA\msdbdata.mdf')

    ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'Y:\SQLLOGS\msdblog.ldf')

    --tempdb

    --USE master

    ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQLDATA\tempdb.mdf')

    ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'Y:\SQLLOGS\templog.ldf')

    -- STEP 2COPY physical files

    -- STEP 3Start SQL Server

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Just curious, does the script assume that you have already made a copy of the original on X to drive Y? I'm guessing that the server has to be stopped for that to happen. Is there a way to move them without stopping the production server - on the fly so to speak?

    Jamie

  • The question was specifically for SQL 2005. So no -T3608 is required.

    If you move a SQL 2000 model and msdb, the flag is required. And, if you move these two dbs together, there is a sequence issue when re-attaching: you have to attach model first.

    As for moving mssqlsystemresource files to a folder different from where master db files reside, there was a bug in the RTM -- you wouldn't be able to restart SQL after applying SP1. Microsoft says it was fixed in SP2. Can any one confirm it was in fact fixed?

  • I took a pot-shot at the answer, because all of them looked to be wrong. Needless to say the answer I chose was marked wrong...

    It is not necessary to detach msdb in order to move it, but if you go down this route than SQL Agent must be stopped before you try to detach msdb.

    If you have detached msdb, remove the trace flags as specified in the 'correct' answer, and restart SQL you will have a fine mess to deal with. I would always leave the -T3608 flag on until msdb is safely attached again. The other flags are simply not needed to get the job done.

    The best approach is given in BOL - do a ALTER giving the new locations for the database files, stop SQL, move the files, and restart.

    Personally, I always keep mssqlsystemresource files in the same folder as master, and leave them in the vanilla install location. The SP2 apply puts a new version of mssqlsystemresource on to your system, and it only puts it in the vanilla install location. If you have previously moved master or mssqlsystemresource you can have big problems in applying SP2. Because of this, I do not assume that future SPs or CUs will be able to cope with anything other than vanilla locations for these critical files. The last thing I need is for a future upgrade to fail because I have moved master or mssqlsystemresource to different locations so they give a zero % performance improvement but comply with standards for user databases. I also take a copy of master and mssqlsystemresource .mdf and .ldf files, and keep them handy in case I need to restore these DBs. This backup copy should be refreshed after any SP or CU install. IMHO is is a severe design fault that SQL needs a working mssqlsystemresource DB, but we have no way in SQL of taking a backup or applying a restore.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • ALTER might be preferred, but if it's not an answer, you don't get to suggest it 😉

    The reference given, shows that SQL 2005 requires trace flag 3608. Perhaps that's not the case, but please comment on the reference. I'm not sure I want to put out an answer I can't reference. If it's not needed, then does it hurt?

    The question says SQL Server 2005.

Viewing 7 posts - 16 through 21 (of 21 total)

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