0

We are going to move an sql instance to a new physical server (SQL SERVER 2008 r2). The answer to this question explains how Agent jobs and ssis-packages will be moved by restoring the master and msdb-databases to the new server.

My question is if the order of restoring system vs user databases has anything to say? Is it OK to restore master and msdb before user databases, even though this will restore agent jobs and ssis-packages that point to user databases not yet restored/created?

Thanks for any help.

br Gert

Improve this question

1 Answer 1

Reset to default
1

my understanding is that the order is not important... as long as the agent is not running until all db's are restored that have jobs interacting with them.

Yes, there is a dependency, but that dependency only exists upon execution of the jobs.

I would be interested in feedback subsequent to the restore of the master db.

I assume also that you are moving an existing (SQL SERVER 2008 r2) instance...

Improve this answer
4
  • Hi. Thanks for your answer. Yes, it is from-to SQL SERVER 2008 R2. We will upgrade to 2014, but will do this after the restore.
    – GHauan
    Commented Jan 7, 2015 at 12:04
  • 1
    Hi again. Some feedback after our restore. We restored the system dbs first. What happened then, was that all the user dbs was listed, but without any real contents. Then we could restore over the "existing" user db.
    – GHauan
    Commented Feb 3, 2015 at 10:04
  • Thanks for the update. Did your user databases SIDs need to be corrected? Did the logins have to be recreated? So, post master restore, the databases were "there" but only showed up as "suspect" or something?
    – Alocyte
    Commented Feb 3, 2015 at 12:02
  • We did not have to do anything with the SIDs, and all users and jobs were restored with the msdb, however the linked servers did not work, they needed to be recreated. I don't think the databases were suspect (can't really remember), just empty since the files were not there. One importent thing though: when restoring system-dbs from old server, make sure that the disk paths (i.e F:\ E:\ etc) also exists on the new server, or you will get some problems.
    – GHauan
    Commented Feb 3, 2015 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.