Migrating Microsoft SQL Server instance 32 bit to 64 bit machine

Posted on 2011-04-18
Medium Priority
Last Modified: 2012-05-11
Hi Experts,
I am a system admin and I would like to migrate a 32 bit version of sql server 2008 standard to a 64 bit version (New Hardware) due to the sunsetting of existing hardware.  
What is the best way of doing this avoiding the pitfalls?

Thanks in advance
Question by:chrislindsay
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 600 total points
ID: 35419804

A good approach is to take a full backup of your database and restore it on the new server.

Author Comment

ID: 35419837
Hi what about all the jobs \ security settings etc.  Is it a matter of migrating all the components manually ?
Is there no migration tool?
LVL 17

Accepted Solution

Chris Mangus earned 450 total points
ID: 35420669
You can also backup and restore msdb which will bring over all scheduled jobs.  Any custom security is something you will have to handle manually.  

When moving to other hardware you could use the Copy Database wizard but I don't suggest it.
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 450 total points
ID: 35421818
A backup/restore of the MSDB can cause other issues though - it's breaks some kind of broker that stops DBMail from working, and you have to reset it using a few backdoor SQL queries. The MSDB contains all the SQL Agent jobs, and those can be scripted.

I'd move your logins first (there's a Microsoft script for it: http://support.microsoft.com/kb/918992/), and then backup/restore your databases. Once that's done, execute any needed scripts to create SQL agent jobs, maintenance plans, or any special configurations (DBMail, replication, etc).

Author Closing Comment

ID: 35996513
All good partial solutions.  Thanks

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question