How do you recover SQL Server from a detached msdb database?

Tags:
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SylvJumaga
    Apparently I also cannot type: mdsb = msdb Sorry for the confusion.
    0 pointsBadges:
    report
  • MyTokenHandle
    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 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following