Backup SQL 2008 DB to restore on SQL 2000 .NET

Question

We have cutomers interested in sending their Sql 2008 data to other customers with SQL 2000. Now obviously this does not currently work, so I'm attempting to create a "generic" backup process that allows us to backup and restore our data on Server 2000 and up (since SQL2000 is the least common denominator of all or customers). Right now we are generating our backups this way (C#):

 Server server = new Server( GetServerConnection( serverName, userName, password ) );
 Backup backup = new Backup();
 backup.Action = BackupActionType.Database;
 backup.Database = GetDatabasePrefix() + databaseName;
 backup.Devices.AddDevice( fileName, DeviceType.File );
 backup.Initialize = true;

 backup.SqlBackup( server );

My first question is, is there any way to "force" the backup to be run as SQL 2000, even if the database is SQL 2005 or 2008?

Also, we were thinking of avoiding this by using the bcp procedure on the database to package up and unpackage the tables and zip them together. In the zip process I'd put a header flag in to declare it a "generic" backup. Does this seem reasonable, or is there a simpler way of doing this? Since the application is the same regardless of server, all of the columns and tables should be the same. Thanks for the help!

Answer 1

It's not possible to restore a backup from a newer version of SQL Server to an older version.

You can generate SSMS 2000 SQL scripts from SSMS 2008:

• Open Tools -> Options in SSMS 2008
• Go to SQL Server Object Explorer -> Scripting
• Set "Script for server version" to 2000
• Right click your database, choose Tasks -> Generate Scripts
• Script all objects in your database
• Execute the script on your 2000 server

Then link the servers together, and copy the data like:

insert ChefDb.dbo.MyTable from ChefServer.ChefDb.dbo.MyTable

Alternatively, consider purchasing RedGate SQL Data Compare.

Answer 2

If you are porting data only, BCP or a custom SSIS package sounds like your most likely options that will be reusable. You may want to peek at the SSIS import/export wizard, it is pretty simple to use and may accomplish what you want.