Migrating a database from SQL Server 2005 to SQL Server 2000 (Updated)

Today I had to copy a new database from my test environment, which is SQL 2005, to a server in the production environment which showed out to be SQL 2000. This was bad luck, as a downward migration is not really supported in the SQL environment and accompanying tools, according to much googling around.

My googling led me in the absolute wrong directions, having me write about  Peter A. Brombergs Egghead CafĂ©  C# SQL Server Database Export Utility, SQLAdmin.DLL, etc to script the elements of a database, both schema, data and stored procedures. This article, unfortunately, is completely out of date now, as are many others concerning the use of the DMO ActiveX object in C#.

In .Net 2.0 we have instead (drumroll, ta-da): SQL Management Objects aka SMO. This is an incredibly comprehensive framework embedding all the SQL management functionality, including the scripting of DDL, in managed code. There are quite a few samples out there to google for.

But the really good news is that Microsoft has created a free wizard called the Database Publishing Wizard around all this functionality – the wizard will apparently be integrated in VS2008, currently runs on its own. Scott Guthrie has a description here, but it is relly simple to usee so just go ahead and download it. This wizard has the option of generating a script with a complete export of a database in either SQL 2000 or SQL 2005 format. I have tested a script that downgrades my database and haven’t run into any problems.

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s