0

I'm in the process of moving from SQL 2005 to SQL 2008 R2. Moving the production DBs and logs is straightforward, through either detach/attach or backup/restore. (new hardware, so I have the old system running in parallel with the new one during the move)

I'm wondering about the system DBs, though (master & msdb). What's the best way to move their key contents over, retaining as much old-system data as I can, including logins, users, account passwords and so on? (I know I can't just do a backup/restore).

No clusters, mirrors, replication, linked servers or SSIS involved. There are some standard maintenance jobs.

Is running the scripts created using Tasks --> Generate Scripts enough? Are there parts of the generated scripts that aren't portable from SQL 2005 to 2008 R2?

After getting the system DBs moved, are there other steps I should take to complete the migration and avoid things like orphaned logins? Are there old-system settings that I need to set manually?

RickNZ
  • 150
  • 6

1 Answers1

0

Don't. Your new instance will have its own system databases. Treat them as incompatible. But your salvation will be to script out what you care about or use SSIS which has, IIRC, move login and move jobs tasks (sure about the first one, iffy about the second). For logins, if they're windows logins, it's as easy as just a create login [domain\user] from windows and any orphans will be resolved. If you have SQL logins, you'll have to jib jab around with getting the hashed password and SID out of sys.sql_logins and doing a create login [yourlogin] with sid = <some guid>, password = 0xfacefeed hashed. Good luck!

Ben Thul
  • 3,024
  • 17
  • 24
  • I know the new instance has its own system DBs. My question is how to most effectively move their contents from SQL 2005 to 2008. "Don't" doesn't help, and creating everything by hand is error-prone and time consuming. There must be a better way. – RickNZ Feb 23 '13 at 13:27
  • As I said above, there is at least an SSIS task for moving logins and getting the jobs is literally a right-click away in SSMS. – Ben Thul Feb 23 '13 at 14:24