January 9, 2009 at 8:36 am
The question was a bit tricky as it mentions moving, but doesn't ask that. It asks for system databases, the idea being to clue people in to the fact there is a hidden database whose filename is mssqlsystemresource.mdf.
I have corrected the typo and reworded the question slightly to make it more clear. All points have been awarded back as of right now.
January 9, 2009 at 8:39 am
This question brings up a real bugbear for me. Why move the system databases at all? why not put them where you want them in the first place as part of the install. Saves a lot of hassle later on. I can also see no need to separate data and logs for system databases. I should think up an avatar to reflect this.
Tempdb is of course the exception to this. Perhaps this is why its easy to move tempdb but not the other system databases 😉
I did not know it had been made impossible to move the resource database in 2008, but glad to hear it has.
not getting at the question here, it serves a different purpose - just don't want people to get the idea that install followed by db move is the way to do it
---------------------------------------------------------------------
January 9, 2009 at 8:52 am
George, they should be placed according to some standard when you install, but two issues. One is people not familiar with SQL, or perhaps with your standard (or as part of app install) set up SQL Server and don't realize.
2nd, people make mistakes. You need an option to fix things, and you should know how to do that.
January 9, 2009 at 8:54 am
Bleh! Heres me thinking 'well- you can move the resourceDB in 2005, so you can probably move it in 2008'
Heh. Wrong!
Good to know though!
January 9, 2009 at 9:14 am
Steve Jones - Editor (1/9/2009)
George, they should be placed according to some standard when you install, but two issues. One is people not familiar with SQL, or perhaps with your standard (or as part of app install) set up SQL Server and don't realize.2nd, people make mistakes. You need an option to fix things, and you should know how to do that.
Steve, agreed, and yes you need to know how just in case, but I've seen a number of posts where people have said they have just installed SQL now how do they move the databases, so I want to make people aware there's a better way to do it, which I do whenever I get a chance! (like this thread). Its a little crusade of mine, sort of like Jeff's RBAR but soooooo much smaller.
Part of this is the concern over separating out System db logs, as it causes so many problems later on with upgrades.
did you see the edit to my first post? I wasn't criticizing the QOTD.
---------------------------------------------------------------------
January 9, 2009 at 9:53 am
Today's question is not clear and the answer is wrong. The question states, 'You have installed SQL Server 2008 and need to move the system databases to a new drive to comply with corporate standards. Which of the following are system database files you can move?', the question says nothing about using SSMS and if SQL Server 2008 is like it's predicessors then you must start SQL Server with a trace flag to move the MSDB and model databases. So therefore you would not be able to move the MSDB and model databases inside SSMS.
I could not find an updated MS article for SQL Server 2008 but for 2005 and below here is what I follow 'How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server'
January 9, 2009 at 10:29 am
Well thats good to know you can't move the Mssqlsystemresource in 2008. In 2005 it was very important to keep it with master.mdf.
I also noticed for the answers it has msdb.mdf and the correct name is msdbdata.mdf
January 9, 2009 at 10:34 am
Correct, it says nothing about using SSMS, but you're saying that you can't move them in SSMS? It doesn't say anything about moving databases. It asks which ones can you move.
How much more clearer does it need to be?
January 9, 2009 at 11:05 am
🙁 I used an old copy of BOL for 2008 which had a set of instructions for moving the Resource Database. Man... burned!
January 9, 2009 at 11:23 am
http://msdn.microsoft.com/en-us/library/ms345408.aspx
the link above says that resource db can not be moved and it's updated December 2008.
I have SQL Server 2008 BOL August 2008 installed and it clearly says that It can be moved however it's not recommended. I have searched for newer BOL 2008 update I was not able to find it. So the tricky question tricked me and I relied on the BOL that I had installed which failed me. So another lesson learned. I hope MS will release updated BOL 2008 soon.
January 9, 2009 at 11:36 am
I thought this was a good tricky one. 😛
The Redneck DBA
January 9, 2009 at 1:29 pm
This database is movable in 2005. Is it not in 2K8? I know its movable because the person before me at this job moved it and when I applied a cumulative update to SQL it failed since it was not in the default install location. So the question answer is wrong, it is movable technically speaking (atleast in 2005), but it may not be desirable to move.
January 9, 2009 at 1:38 pm
dmc (1/9/2009)
This database is movable in 2005. Is it not in 2K8? I know its movable because the person before me at this job moved it and when I applied a cumulative update to SQL it failed since it was not in the default install location. So the question answer is wrong, it is movable technically speaking (atleast in 2005), but it may not be desirable to move.
The original question indicated a SQL Server 2008 database. Anyhow, I think some of us learned some valuable information from this discussion. We now should know not to "step in it" by moving the resource database if we are still on SQL Server 2K5. Since that is the real value of these questions of the day and their subsequent discussions, then this was a very good question.
Thanks Steve.
January 9, 2009 at 6:07 pm
you are welcome and glad you learned a few things. I know I did.
January 12, 2009 at 1:01 am
Can I also point that the msdb database data file is Msdbdata.mdf and not msdb.mdf? Just being picky, theoretically the msdb.mdf answer should be incorrect then because sql server 2008 doesn't come with msdb.mdf, but msdbdata.mdf instead. Unless BOL is wrong. Can't check, as I'm still on SQL 2000 :hehe:
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply