How do you recover SQL Server from a detached msdb database?
Home > IT Answers > SQL Server > How do you recover SQL Server from a detached...
myTokenHandle
0 pts.
0
Q:
How do you recover SQL Server from a detached msdb database?
SQL Server
I've been moving SQL Server databases from one array to a new, freshly created drive array. I've managed to move all the user databases successfully, but when it came to the system databases, I misread the Microsoft "How To" document on moving system databases.

I successfully stopped and restarted SQL with the traceflag 3608, detached model, msdb and a database containing the newly migrated Analysis Services Repository; I then copied the data files (.mdb & .ldb) to their new home (note: all datafiles still exist in the original location - and I took a SQL backup of all databases before starting); unfortunately I missed the next step, which would have been to reattach the databases to SQL and instead blundered on to stop SQL, remove the trace flag and fire it up again. Obviously, it doesn't work.

So, I now have a situation where I can't do anything with SQL server because it resolutely refuses to have anything to do with me. I'm sure that all I need to do is (somehow) reattach msdb (don't care where from), and start up SQL - but I don't know how - and searching the Microsoft site has revealed an awful lot about backup/restore - but that's not what I want or need to do (I suspect).

Can you point me in the right direction?

Many Thanks

Chris.
ASKED: Jan 3 2006  6:53 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
SylvJumaga
0 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Hi!

I was with you right up until the "fire it up again" step. Do you mean that the SQL Server service will not start up, now?

I haven't specifically moved the "mdsb" database, but I have moved a lot of client databases with attach-reattach procedures in the past, so maybe I can help.
Last Answered: Jan 4 2006  11:03 AM GMT by SylvJumaga   0 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

SylvJumaga   0 pts.  |   Jan 4 2006  11:26AM GMT

Apparently I also cannot type:

mdsb = msdb

Sorry for the confusion.

 

myTokenHandle   0 pts.  |   Jan 5 2006  5:39AM GMT

Yes, that’s right. SQL would not start up. It reported an error locating file ID 3.

Fortunately, I’ve resolved this problem. I started SQL using the command line string ’sqlservr -T3608 -f’,
then reattached the databases in Query Analyser. Then, as no databases were being reporting in Enterprise Manager, I had to delete the SQL Server Registration from Enterprise Manager for that server, and re-register the server.

…And now, everything’s back to normal!

Thanks for your replies!

 
0