April 21, 2010 at 11:57 pm
Hi,
I currently have a SQL 2005 instance running on a 64 bit platform. We need to migrate the entire instance (including databases, dts packages, jobs and security) to SQL 2008 on a new server which will be on a 32 bit platform.
Can anyone suggest the easiest and safest way to go about this. I know I will need to export the DTS packages to a file system, copy them over and import them on the new server which should be pretty straight forward. Would the best way to transfer the user databases be to detach them, and then retach them on the new server or back them up and then restore on the new server and with this in mind, how do I go about handling the system databases? Regarding jobs and security, what would be the best approach?
Please remember, I need to replicate the entire environment on the new server. Please let me know if I am missing out anything.
Any help would be much appreciated, thanks in advance.
April 22, 2010 at 6:16 am
You've actually already outlined most of the work involved. SSIS packages can be moved as files. To move the agent jobs, you just script them out and then run the scripts on the new server (fix any changes to paths, etc., in the script). To migrate the logins, take a look at sp_help_revlogin. To migrate the databases, yes, you can use detach/attach, but I prefer to use backup/restore so that the original databases are left alone and online, just in case. With an upgrade from 2005 to 2008 you chances of hitting an issue are extremely low, but extremely low is not zero. After the upgrade, do a manual update of the statistics. You should be good to go.
I'd suggest doing all this in a test environment once to see what you missed or what goes wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2010 at 2:40 am
I will give that a go then. Many thanks, much appreciated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply