Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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 :-).
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 & 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.
dbcc checkdb advice. It is a very old legacy CRM application I have been left carrying the can for - but a previous migration from version 7 went pretty smoothly (though I can't remember how it was done). Limits of Express are all fine and yes, the testing fun starts now :)
Jul 21 '11 at 21:42