Restore master from server A to cluster SQL server

  • I am going to implement SQL server clustering, move DBs from Server A to SQL cluster Server. I was told that the master can not be restored from Server A to cluster server. Why? Can msdb be restored?

  • You can but it's probably not supported and I suggest if you're asking the question then maybe you shouldn't < grin >

    The only real reson I can think you'd want to move master is to move the logins and this can be scripted.

    Moving master is quite easy but is unsupported and if you get it wrong you'll be in serious trouble. Msdb can be restored as a normal database but you'll probably have to reset the scource database otherwise you won't be able to edit the jobs. You might find scripting the jobs a better option. = a clean new build on your cluster.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Also, don't restore msdb from one server to another.  I did this once and made a bit of a mess 🙂  The sysjobs table contains a column called originating_server that contains the server name.  If I remember correctly, SQL Agent wouldn't even start up and I had to restore a copy of the original msdb.

    Linda

  • Yup the trick is to update the originating server column to your correct (new) server and the jobs will then work ok.

    You must also make sure your database collations match otherwise you may get some strange results.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply