Development

Moving SQL Server 2005 Express databases to SQL Server 2000

8 Comments

23/06/2010 UPDATE
The database publishing wizard is integrated within Visual Studio 2008/2010.

Further information can be found here.

As you might well imagine, the on disk structure for SQL Server 2005 differs from that of SQL Server 2000.

Indeed, restoring a SQL Server 2005 [Express] database backup for use with SQL Server 2000 isn’t really the done thing, as this post confirms.

After a little head-scratching with the SQL Server 2005 Express data export and scripting options, I deemed it necessary to create a SQL script that was not only capable of creating the database structure, but was also able to create all the INSERT statements necessary to recreate the data too. And it had to create SQL suitable for SQL Server 2000’s dialect…

Further head-scratching led me to the Microsoft SQL Server Database Publishing Wizard. It does exactly what I needed and allowed me to move a SQL Server 2005 database back down to SQL Server 2000, as these screenshots confirm:



So, in a nutshell, here’s what I did:

  1. Ran the Database Publishing Wizard against my SQL Server 2005 Express database.
  2. Created a SQL Server 2000-compliant SQL script that contained all the SQL statements required to create the database. The SQL script also created all the INSERT statements required to populate the tables in the database.
  3. Created a new blank database in SQL Server 2000.
  4. Ran the SQL script from step 2 against the SQL Server 2000 database – using the Query Analyser.

Of course, there’s often more than one way to skin a cat, your mileage may vary.

Technorati Tags: , , , , , ,

8 thoughts on “Moving SQL Server 2005 Express databases to SQL Server 2000”

  1. If I had to do this, I would look at using “SQL Compare” and “SQL Data Compare” from Red-gate and getting them to sync a block SQL server 2000 database with the SQL 2005 database. However these tools are not free and we already have “SQL Compare” to help with creating our database upgrade scripts.

    Using Microsoft SQL Server Database Publishing Wizard is a VERY clever way of doing this without having to buy in and learn any 3rd party tools.

  2. The Red Gate products are on my list to look and and review over the remainder of this year. They look to be a very comprehensive set of tools.

  4. Pingback: Frank gives me a mention over at The Code Project at The Social Programmer

  5. Do you sample scripts available? Sounds great, but having some sample scripts would save me from re-inventing the wheel.

    Please feel free to email me directly. Great article. Thank you!!

  6. Thank you. This was an incredibly easy and painless way of achieving this much-frowned-upon exercise!

  8. I found this post very helpful as I was striving to find information how to create sql queries for database, actually I needed more than general
    sql queries information and I found it here. Thanks for your post.

Comments are closed.