Upgrading SQL 2000 32-bit to SQL 2008 64-bit

Question

Hi, I have a number of databases on a Windows 2000 Server running the 32 bit version of SQL Server 2000. I need to transfer all the data and settings to a new server running the 64 bit version of SQL 2008 on Windows 2003 64 bit.

Is this as simple as backing up the databases and restoring to the new server. Will this work with the system databases (master, model, msdb) as well? If not, is there a way to automate the process of moving across the user accounts and permissions?

Is anybody aware of any other gotchas?

Many thanks for any help.

[EDIT] I've successfully tested an upgrade to SQL2008 32-bit, I'm mostly concerned with any differences in the 64-bit version as I don't have a 64 bit server I can test on at the moment e.g. I know you can't restore a SQL2000 master database to 2008; can you restore a SQL2008 32bit master to SQL2008 64bit? Downtime during the migration is not a problem.

Answer 1

To transfer logins, you should be able to use the 'Transfer Logins Task' described in Books Online here.

Ross Mistry has published a few articles on SQL Server 2008 Installation Strategies and Best Practices. Also this document, although directed at specific hardware, contains useful info.

EDIT: Have you looked at the Microsoft SQL Server 2008 Upgrade Advisor?

Answer 2

I would use the RedGate SQL Compare and SQL Data Compare. That would give you some assurances of the integrity of the database. However its not going to be an online migration of the data, you would have to prevent updates during the transition.

Answer 3

As far as 32 bit vs 64 bit, see this question. (Basically, you shouldn't have any problems).