Migrating a SQL Server 2000 Database to SQL Server 7.0

I need to migrate my SQL Server 2000 database for use on a SQL Server 7.0 database. I haven't used any SQL Server 2000-specific features. What's the best way to move this database back to SQL Server 7.0?

You can migrate the database in three ways, depending on the volume of data involved and how often you will perform this move. If you have small amounts of data, you can set up a linked server (SQL Server 7.0 linked to SQL Server 2000). On the SQL Server 7.0 machine, use SELECT INTO against the linked server to move data table by table. Or if you have a lot of data, use the SQL Server 2000 bulk copy program (bcp) to pull data out of SQL Server 2000 in Native format and write the data into SQL Server 7.0. You can easily write a bit of code to generate an appropriate batch file to copy the data to SQL Server 7.0, as the following code sample shows:

SELECT 'bcp.exe "' + TABLE_CATALOG + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']" OUT  "C:TEMP' + TABLE_NAME + '.bin" -n -T'FROM INFORMATION_SCHEMA.TABLES-- To generate the statements for input at the -- SQL Server 7.0 serverSELECT 'bcp.exe "' + TABLE_CATALOG + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']" IN  "C:TEMP' + TABLE_NAME + '.bin" -n -T'FROM INFORMATION_SCHEMA.TABLES

Using Native mode with the -n switch is release-specific, so to move data in character mode, use the bcp.exe that comes with the earliest SQL Server release (in this case, SQL Server 7.0) or use the -c flag. If you're crossing character-set boundaries, consider using the -w flag so that you can apply Unicode data formats. However, using Unicode data formats can considerably expand the size of the transferred files and increase the processing required to do the task. Use Enterprise Manager 2000 to script out definitions for all objects (e.g., tables, indexes, primary keys, foreign keys, triggers, stored procedures). Then, run the scripts against SQL Server 7.0 to create the empty schema. Finally, if moving the data is an ongoing requirement, consider a third way to migrate your data—replication from SQL Server 2000 to SQL Server 7.0.

A faster alternative to bcp for loading data is BULK INSERT. However, BULK INSERT's error handling isn't as strong as bcp's error handling, and typically you would use BULK INSERT only with very clean data. To use BULK INSERT to load data, modify the earlier sample SELECT statement as follows:

SELECT 'BULK INSERT ' + TABLE_CATALOG + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] FROM "C:TEMP' + TABLE_NAME + '.bin" WITH (DATAFILETYPE="native")' FROM INFORMATION_SCHEMA.TABLES

Before you use any of these methods to migrate your data, examine your data. If the data contains IDENTITY columns or NULL values on fields whose default values you need to retain, remember to handle them appropriately. Use the -E flag to control the way bcp.exe handles identities (or use KEEPIDENTITY for BULK INSERT). And use the —k flag for bcp and NULLS (or use the KEEPNULLS setting for BULK INSERT). You need to consider these settings when you use the IDENTITY field to generate surrogate keys for linking tables because if the value changes, the link between the rows could be lost.

One final comment: These examples assume the use of the default SQL Server. If you're unloading the data from or loading the data into a named instance of SQL Server or from a remote SQL Server, you should add the -S flag to the generated bcp.exe statements to specify the server name.