Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itsonlyme4
Posting Yak Master
109 Posts |
 Posted - 2010-01-26 : 12:18:10
|
I installed a new Named Instance on my SQL Server 2005 box. Once the install was done and the service pack was applied, I began to do my usual cleanup ... moving the system databases etc... now my instance will not start. I know what I did.. I just can't figure out how to fix it!!! When I ran my alter statements to move the MSDBData.mdf and Model.mdf to their new locations, I ran the alter statements like this: ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'F:\MSSQL\OVOPS\Datafiles\MSDBData.mdf' )GOALTER DATABASE Model MODIFY FILE ( NAME = Modeldev, FILENAME = 'F:\MSSQL\OVOPS\Datafiles\MSDBData.mdf' )GO OBVIOUSLY I named the MSDB and the MODEL .MDF files the same!!!! DOH!!!!Not realizing what I did.... I stopped the instance, moved the system .mdl and .ldf files to their new locations and when I tried to start the instance it will not start. Does anyone have any ideas on how I can get myself out of this pickle???? |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2010-01-26 : 14:12:17
|
| Nothing to worry. Move the data files from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA to 'F:\MSSQL\OVOPS\Datafiles\.Start SQL Server service. RegardsSRIWith RegardsBSR |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 14:24:11
|
| And how are MODEL and MSDB databases going to use a single MDF ? |
|
|
|
itsonlyme4
Posting Yak Master
109 Posts |
Posted - 2010-01-26 : 15:30:25
|
| I'm sure they can't use a singel file.. which is why my instance won't start |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-26 : 15:56:33
|
| it would have raised an error if you pointed them at the same file.the ALTER DATABASE MODIFY FILE would have failed.something else is going on.Don't suppose you took a BACKUP before you did that...? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 16:26:51
|
I didn't even ask that because I know I wouldn't have done ... Install, Patch, Configure ... nope, no backups yet  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-26 : 16:33:33
|
well, the good news it's a brand new instance so not as big of a deal to destroy and rebuild it, but there is hopefully a less "brute force" fix.  i can't see it pointing 2 dbs at the same physical file. i've done that by accident before and the statement errored out |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-26 : 16:37:28
|
| "i can't see it pointing 2 dbs at the same physical file. i've done that by accident before and the statement errored out"That's good to know. I did wonder if there was / should be a Unique constraint on the filename ... |
|
|
|
itsonlyme4
Posting Yak Master
109 Posts |
Posted - 2010-01-27 : 09:01:04
|
| I did have backups but not much use if I can't get the instance up :)I ended up getting the instance up in minimal\single user mode and then reran my Alter statements. thank you all for your help!!!! |
|
|
|
|
|
|
SQL Server 2005 instance will not start