March 1, 2012 at 6:47 am
Hi,
This is probably one of those questions you'll be asking yourself 'why?'. I'm in the position where i'm going to be migrating a physical SQL 2005 box to a new virtual SQL 2008 R2 box and am currently trying to find the best way to do this. I think for the user databases I can detach the db's from the old box and attach to the new box and they'll be upgraded during the process (I may need to update db version and check for orphaned users). But, what about the system db's? The scripting method would probably work but would have me script out all the objects (linked servers, replication, logins, etc.) and re-run them on the new box, but can I (or even would I want to) detach and attach the system db's as per the user db's? I have a test box to give it all a go on, but i'd like to start with an approach that's likely to work.
Any advice?
Mike
March 14, 2012 at 12:10 pm
Hi,
As u will be installing new version of SQL Server u will have the system databases created for u after the installation. U just need to upgrade ur previous version of your user databases to newer versions and sync out the logins and linked server if any.
Thanks
March 15, 2012 at 2:56 am
you dont want to be detaching/attaching backup/restoring system databases across different versions, if you where moving 2005 to 2005 you would be ok, but as this is 2005 to 2008 R2 it is something that shouldnt be done due to changes in the structures of the system databases in different versions.
you could use SSIS to transfer the logins taking across their SID's which will resolve the orphan users part, but things like jobs, linked servers, replication will need to be scripted and re-deployed
March 15, 2012 at 3:23 am
Thanks for clarifing the situation.
I thought there were issues transferring system dbs and have actually gone via the route you suggest.
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply