<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
MSSQLSYTEMRESOURCE NOT IN DATA DIRECTORY LIST...
12 posts, Page 1 of 2
1
2
»»
MSSQLSYTEMRESOURCE NOT IN DATA DIRECTORY LIST
Rate Topic
Display Mode
Topic Options
Author
Message
noel yu-371000
noel yu-371000
Posted Sunday, January 20, 2013 8:58 AM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, May 04, 2013 2:00 AM
Points: 4,
Visits: 182
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
Post #1409285
george sibbald
george sibbald
Posted Sunday, January 20, 2013 12:50 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 5,269,
Visits: 11,203
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.
---------------------------------------------------------------------
Post #1409299
noel yu-371000
noel yu-371000
Posted Sunday, January 20, 2013 3:05 PM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, May 04, 2013 2:00 AM
Points: 4,
Visits: 182
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.
Post #1409312
SQLRNNR
SQLRNNR
Posted Sunday, January 20, 2013 3:14 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1409316
SQLRNNR
SQLRNNR
Posted Sunday, January 20, 2013 3:21 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1409318
george sibbald
george sibbald
Posted Sunday, January 20, 2013 3:22 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 5,269,
Visits: 11,203
[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.
---------------------------------------------------------------------
Post #1409319
noel yu-371000
noel yu-371000
Posted Sunday, January 20, 2013 4:40 PM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, May 04, 2013 2:00 AM
Points: 4,
Visits: 182
Did you start the server as the instructions say in step 7? --
YES
Post #1409323
noel yu-371000
noel yu-371000
Posted Sunday, January 20, 2013 4:44 PM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, May 04, 2013 2:00 AM
Points: 4,
Visits: 182
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.
Post #1409324
SQLRNNR
SQLRNNR
Posted Sunday, January 20, 2013 10:41 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733,
Visits: 12,332
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1409356
george sibbald
george sibbald
Posted Monday, January 21, 2013 4:54 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 5,269,
Visits: 11,203
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!
---------------------------------------------------------------------
Post #1409486
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
