Post reply

Migrate 2005 system databases to 2012

  • October 12, 2012 at 8:51 am

    #393377

    Hello,

    I have two physical servers, one running SQL Server 2005 and one SQL Server 2012. I have been tasked with migrating the 2005 databases to 2012 using the BACKUP.....RESTORE method. Although this doesn't seem too difficult for "user" databases my main concern is with the system databases - master, msdb and model.

    Can anyone tell me if it is at first possible, secondly do I need to do it and thirdly how do I do it?

    Thanks for looking.

    Greg

  • October 12, 2012 at 10:56 am

    #1548766

    greg.huxley (10/12/2012)

    Hello,

    I have two physical servers, one running SQL Server 2005 and one SQL Server 2012. I have been tasked with migrating the 2005 databases to 2012 using the BACKUP.....RESTORE method. Although this doesn't seem too difficult for "user" databases my main concern is with the system databases - master, msdb and model.

    Can anyone tell me if it is at first possible, secondly do I need to do it and thirdly how do I do it?

    Thanks for looking.

    Greg

    You don't migrate system databases. What you will need to do is script out objects such as logins, jobs, linked servers, etc and apply them to the new instance.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 12, 2012 at 11:31 am

    #1548777

    Perry is correct. You wouldn't move model. It's set for each version. If you need some objects out of there, script and move them.

    For master, just linked servers and logins are there. You can script both out (sp_help_revlogin for logins) and run that on the new instance.

    For jobs/plans/agent alerts, scripting is needed. I would review all jobs and alerts and make sure they apply to the new instance before moving them blindly.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 15, 2012 at 2:53 am

    #1549024

    Perry, Steve

    Thank you for the information. Glad I checked before attempting! Are there any dependencies on the order in which I do the migrations i.e. do I move the databases first and then create all the logins, jobs, etc or does it not really matter?

    Thanks,

    Greg

  • October 15, 2012 at 5:18 am

    #1549073

    greg.huxley (10/15/2012)

    Perry, Steve

    Thank you for the information. Glad I checked before attempting! Are there any dependencies on the order in which I do the migrations i.e. do I move the databases first and then create all the logins, jobs, etc or does it not really matter?

    Thanks,

    Greg

    Since most of the objects you script out will likely have a dependency on a user database (for example a login with a default database or a job step that uses a particular dataabse) I would move all the user databases first and then apply logings, jobs, packages, etc, etc

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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