MSSQLSYTEMRESOURCE NOT IN DATA DIRECTORY LIST

  • We are planning to migrate our system DB (SQL 2005) on our cluster environment. We followed the instruction given by MS KB and MASTER DB was successfully migrated but when we executed the alter database the mssqlsystemresource, we got this error “Could not locate entry in sysdatabases for database 'mssqlsystemresource'. No entry found with that name. Make sure that the name is entered correctly.”

    I executed the SQL script "SELECT name, filename FROM master..sys.sysaltfiles", both mssqlsystemresource.mdf and mssqlsystemresource.ldf doesn't exit on the list. I verified the Data Folder, both are there.

    I tried copying both files into a different folder, it allows me. I also verified with other SQL 2005 servers, both files are not listed on the sys.sysaltfiles. Only our SQL 2008 Server, has both files on the sys.sysaltfiles.

    Is the files not attached on our SQL SERVER? Is it OK we just copy both files on where are MASTER DB is located?

    Please advise. Thanks

  • did you follow this process when moving the system databases? -

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

    the mssqlsystemresource database is a read only database microsoft do not want you to manipulate, so it does not appear iin the metadata about the databases.

    Why are you moving the system databases, what are you trying to achieve? In SQL20005 always keep the resource database in the same directory as the master database, and don't bother seperating the log from the data file.

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

  • did you follow this process when moving the system databases? (http://msdn.microsoft.com/en-us/library/ms345408(v=sql.90).aspx) --- YES, I followed as per what is instructed.

    Why are you moving the system databases, what are you trying to achieve? -- We need to replaced the drives where all the System DB are placed.

    In SQL20005 always keep the resource database in the same directory as the master database, and don't bother seperating the log from the data file. -- Yes, I know that.

  • Did you start the server as the instructions say in step 7?

    Start the instance of SQL Server in master-only recovery mode

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • If following those steps verbatim doesn't work, try this

    http://www.sqlnotes.info/2012/01/06/new-way-to-relocate-resource-database/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • [Why are you moving the system databases, what are you trying to achieve? -- We need to replaced the drives where all the System DB are placed.

    could you replace the drive but give it the same drive letter? Stop SQL, copy all the files on that drive to the new drive, then swap the drive letters so the new drive has the same letter as the old one had, restart SQL. will save a lot of moving files around.

    and by the way - query master.sys.master_files, sysaltfiles will be deprecated, but as i said , the resource database is not listed in the metadata.

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

  • Did you start the server as the instructions say in step 7? -- YES

  • If following those steps verbatim doesn't work, try this (http://www.sqlnotes.info/2012/01/06/new-way-to-relocate-resource-database/)

    I tried executing the SQL script "select name, filename from sys.sysaltfiles where dbid = 32767" -- No mssqlsystemresource db found

    Already check with my other SQL2005, same thing. But on SQL 2008 DB exist.

  • noel yu-371000 (1/20/2013)


    If following those steps verbatim doesn't work, try this (http://www.sqlnotes.info/2012/01/06/new-way-to-relocate-resource-database/)

    I tried executing the SQL script "select name, filename from sys.sysaltfiles where dbid = 32767" -- No mssqlsystemresource db found

    Already check with my other SQL2005, same thing. But on SQL 2008 DB exist.

    That is because it is hidden. You will not see that database in 2005 when selecting from sysaltfiles - that is expected behavior.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • odd how the resource database is not listed in sysaltfiles in SQL2005 but it is in SQL2008 (and SQL2008R2). Is this view present in SQL2012?

    MSSQLSystemresource is not listed in sys.master_files.

    quite useful in a way that is there, though can obviously cause confusion and use of sysaltfiles should not be relied upon going forwards.

    Hey! 5000 points! Maybe enough to be listened to now! 🙂

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

  • george sibbald (1/21/2013)


    Hey! 5000 points! Maybe enough to be listened to now! 🙂

    Congrats

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (1/21/2013)


    george sibbald (1/21/2013)


    Hey! 5000 points! Maybe enough to be listened to now! 🙂

    Congrats

    thanks Jason.

    OP, post your exact code whem moving the resource database please.

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

Viewing 12 posts - 1 through 11 (of 11 total)

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