Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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 :-).

share|improve this answer
    
Thought you might like to know I did this migration last week and it worked flawlessly - thanks a million. One thing I should mention (with blushes) - SBS2003 (not R2) has SS2000 not 2005 so I've belatedly updated my question with that info. –  Jack Douglas Nov 9 '11 at 14:55
add comment

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.

share|improve this answer
    
Thanks especially for the 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 :) –  Jack Douglas Jul 21 '11 at 21:42
    
+1 for remembering the scheduling, as there is no SQL Agent. –  SqlACID Jul 21 '11 at 23:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.