Restore an Sql Server 2005 DB backup to Sql Server 2000

Question

Is there a way to restore an Sql Server 2005 DB to Sql Server 2000? I'm unable to attach it either. Any tools, ideas etc?

Answer 1

Because SQL 2005 has more new features/syntax than SQL 2000. You can not do the restore directly.

I'll do it this way. Generate the schema script from SQL 2005 first. Run the script on SQL 2000 to create the db schema. Then import the data from SQL 2005 to SQL 2000 by using SSIS or Import/Export tools.

Answer 2

I found this utility from Microsoft called Microsoft SQL Server Database Publishing Wizard 1.1 http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en. It generates a single script that contains all the schema and the data and can be run on SQL Server 2000. It recreates the database. It worked for me anyway.

Answer 3

The best way to do this is to restore the back up to a SQL Server 2005 instance, then do an export of the data to the SQL 2000 instance. There are several way you can do the export. You can either convert all the data out to flat files, use a package, or script the db to SQL and run the scripts on the SQL 2000 database.

Answer 4

www.sqlaccessories.com provide some great tools that enable you to do this (using their stuctural/data diff tools). I've not tried doing it directly from the management studio.

Of course, if the db using sql server 2005 specific features, you'll have to do some manual tweaking.

From their blurb: "You can even synchronize databases that are on different versions of MS SQL Server, e.g. one on SQL Server 2005, and the other on SQL Server 2008"

I can confirm that this works.