How to migrate from SQL Server 2000 to SQL Server 2008 R2 Express Edition I have been trying to migrate a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition

I naively tried using the SQL Server Export and Import wizard. This has worked to a degree, but seemed to try and import views as tables, and left stored procedures out altogether.

What is the least painful way of performing this sort of migration?
Whichever method you use, you'll need to be aware of any restrictions on SQL Server 2008 R2 Express that might be a show-stopper eg 10GB maximum size of any one database, single CPU, memory restrictions etc

Ideally, you'll also have tested how the application works with the new version of SQL Server. If it's a vendor product, do they support the new SQL version?

Backup and restore is the most robust method of moving a database as it leaves the original database online (if the SQL Server is left online), and it's part of what DBAs do every day. You'll need to set up the users on the new server etc.... and if it doesn't work, your original is still online. Backup and restore can be done through the GUI Management Studio.

Detach and re-attach is another option where you separate the data &amp; log files from the original SQL Server, and physically copy them to the new SQL Server - and then attach them. It can be done through the GUI. This method is probably a bit less refined than backup and restore - just my opinion.

Whichever method you use, run DBCC CHECKDB on the new server to ensure all is well, and don't forget to schedule regular backups, checkdb, and reindexing etc.