Post reply

Trouble trying to move mssqlsystemresource db

  • May 31, 2007 at 7:03 am

    #117261

    All,

    I have managed to move all system databases on my 2005 Default Instance but when trying to move the mssqlsystemresource database I am getting the error "Could not locate entry in sysdatabases for database mssqlsystemresource "

    I have started SQL Server with trace flag -T3608 and then ran the ALTER DATABASE command to move the db to the same location as the master db.

    Regards

    Carl

  • May 31, 2007 at 7:24 am

    #709984

    From what I understand it moves with the master database.  See the "Moving System Databases" topic in Books Online.

  • May 31, 2007 at 7:37 am

    #709990

    I've moved the master database but the mssqlsystemresource is still in the default directory.

  • May 31, 2007 at 8:33 am

    #710019

    Did you change the startup parameter in the SQL Server Properties dialog?

  • May 31, 2007 at 8:34 am

    #710022

    Carl

    The mssqlsystemresource database has to be moved to the same location as master, but it doesn't automatically get moved with it.  Start the server with the parameters /f /T3608, then run this:

    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'x:\mydir\mssqlsystemresource.mdf');

    GO

    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FIELNAME='y:\mydir\mssqlsystemresource.ldf');

    GO

     

    Move the database files to their new location, restart SQL Server and make the database read-only like this:

     

    ALTER DATABASE mssqlsystemresource SET READ_ONLY;

     

    John

     

  • May 31, 2007 at 8:40 am

    #710027

    John,

    I have started the SQL Server with the parameters using SQL Configuration Manager and adding -c -m -T3608 into the startup parameter list and when I try and run the ALTER DB commands get the error about not being able to locate mssqlsystemresource in sysdatabases.

    Regards

    Carl

  • May 31, 2007 at 8:53 am

    #710031

    Carl

    I'm not sure about using -c -m.  I've only tried it with -f.  See if that works for you.

    John

  • May 31, 2007 at 9:07 am

    #710040

    John,

    Thanks for your help. I was getting confused using the -c -m switches. I used them switches for moving the model and msdb databases.

    I have been following the instruction from the link below which states to use the -f switch!!:

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

    The instructions were not fully correct as I had to stop the SQL Service before moving the .mdf and .ldf files to their new locations.

    Thanks again

    regards

    Carl

  • June 1, 2007 at 3:25 am

    #710257

    My advice is leave master.mdf, the mssqlsystemresource files and distmdl files in the default location.

    Our SQL2005 SP1 standard was to move all these files to specific locations, with the ldf files on a different drive to the mdf, just as we do with our user databases.  This worked fine.

    Then came SP2.

    The SP2 install process places new version of mssqlssytemresource and distmdl in the original location for these files.  It then restarts SQL and expects these new versions to be the ones used.  Needless to say, this caused us a few problems while we (i.e. me!) tried to get a working SP2 system with mssqlssytemresource in our preferred locations.  In the end, I decided the only way to get a stable build was to keep master, mssqlsystemresource and distmdl in their default locations. 

    In doing all this, I also found that a working mssqlsystemresource db is critical to getting SQL started.  As it cannot be backed up and restored in the same way as other DBs, we have decided to keep a copy of the mdf and ldf files of master, mssqlsystemresource and distmdl on a separate drive, so we can copy them back quickly if needed.  This copy should be done when SQL is down, and repeated whenever a SP or hotfix is applied.

    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

  • June 1, 2007 at 3:43 am

    #710261

    To be honest this is my first hands-on experience with SQL Server 2005.

    I installed SQL Server, installed SP2 and lastly moved the system databases.

    Should I install SQL Server, move system dbs and finally apply service packs?

  • June 1, 2007 at 3:51 am

    #710262

    My advice is leave master, mssqlsystemresource and distmdl files in their default locations.  I have had no problems in moving model, msdb and tempdb.

    SP2 changes the way SQL Server deals with mssqlsystemresource, and I think the MS advice on how to move it applies to a RTM or SP1 system only.  SP2 puts the new version of mssqlsystemresource and distmdl in the original location for this database, regardless of any changes you have made prior to SP2.  Therefore I assume that SP3 (if there is one...) will do the same until proved otherwise.  Because of this, our standard is now that mssqlsystemresource and distmdl are not moved from their default locations.

    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

  • June 1, 2007 at 5:00 am

    #710279

    hi,

      resourcedb in sql 2005 is a read-only DB.if u type sp_helpdb or select * from sys.databases or in the Management studio it will not be visible .......

    only its ldf and mdf files are available in default path......my advice is stop sqlserver and change the new path where masters ldf nad mdf resides in the startup parameters.Also simultaneously move the systemresource ldf and mdf file to the new location with master,now start mssqlserver.It shud start up perfectly.type sp_helpfile to get confirm the new location of masters.

    jus manually go to the concerned path and check the systemresource files path.

    Rgds

    Deepak

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

  • June 1, 2007 at 5:43 am

    #710293

    Let me know why you want to move system databases ?

    If you are upgrading from SQL server 2000 to 2005 then simply upgrade the user databases either by ataching or by restoring it. Moving the system database is not a good idea !!

    May be I did not undetstood completely your question?

    Minaz Amin

    "More Green More Oxygen !! Plant a tree today"

  • June 1, 2007 at 6:42 am

    #710307

    Minaz.

    It is a fresh install of SQL Server 2005 and I am moving the system databases onto seperate logical drives.

    C: - binary files

    D: - data files

    E: - log files

    Regards

    Carl

  • June 1, 2007 at 7:07 am

    #710319

    I completely agree with Ed.  Keep the master.mdf & mssqlsystemresource.mdf & .ldf files in their default location!  By all means split the master.ldf to E: - no problem.  (I think you helped me originally... thanks Ed)

    I've recently finished fine tuning a fantastic script I found on sqlservercentral by Vince Iacoboni. http://www.sqlservercentral.com/columnists/vIacoboni/2605.asp

    The script installs eight named instances of SQL 2005 SE, moves the system databases & then installs SP2 and hotfixes it to build 3054.  Takes about 75 mins!

    This script has saved us so much time.

Viewing 15 posts - 1 through 15 (of 18 total)

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