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?
Best Answer
Have you tried with backup and restore? Make a backup in SQL Server 2005 and use it to restore the db to the R2 instance. At the end of the restore it should be upgraded automatically.
Or is it any different for SBS 2003 and can't make a backup?
To be able to transfer also the logins you could get help from the following KB articles:
PS: posted initially as comment because I never saw a SBS close enough :-).