Backport MS SQL 2000 database to version 7

by Ted Jardine Tuesday, September 26, 2006 11:19 PM

 

In looking around for the best/easiest way to backport a MS SQL 2000 database into MS SQL v.7, it seemed most of the responses basically resorted to either script it or "upgrade to 2000". For i.e. Microsoft says:

To work around this problem, use the data transfer utilities in SQL Server 2000, including Data Transformation Services (DTS) and the BCP utility, or create linked server queries to transfer data from a SQL Server 2000 database to a SQL Server 7.0 database. You can also upgrade from SQL Server 7.0 to SQL Server 2000, and then back up and restore the database on the computer that is running SQL Server 2000 server. (from http://support.microsoft.com/default.aspx?scid=kb;en-us;824423)

On the small chance that some other poor soul has to do the same, here's how I did it (as upgrading was unfortunately not an option for this client):

First, I scripted my tables, views, stored procedures, etc. as necessary, while making sure to check "Only script 7.0 compatible features." While I was aware of several items that were added to SQL Server 2000 (and thus avoided using them during development), two gotchas for me was finding out belatedly that SQL Server v.7 also does not support Cascading Updates/Deletes or SCOPE_IDENTITY(). The scripting tool still however includes these, so there's some manual editing of the scripts to modify them so they run on v.7 (and some development time later so that your app takes care of cascading deletes).

Then I wrote a batch file to BCP the data out of the SQL Server 2000 database:

@echo off
rem "Usage: MyBCPScript.bat Password"
@if "%1" == "" goto usage
echo Password : %1 pause
Echo coping tables from MySampleDatabase ...
bcp MyDb.dbo.Organization out d:\SQL\Organization.bcp -n -P%1
etc.... for all tables in database
goto end
:usage
echo Usage: MyBCPScript.bat Password
:end
echo Finished!
pause

Then I wrote a batch file to BCP the data into v.7:

@echo offrem "Usage: MyBCPScriptsRestore.bat Password"
@if "%1" == "" goto usage
echo Password : %1
pause
Echo resoring tables to v.7 database ...
bcp MyDb.dbo.Organization in D:\SQL\Organization.bcp -n -P%1
etc... for all tables in database
goto end
:usage
echo Usage: MyBCPScriptsRestore.bat Password
:end
echo Finished!
pause

Run the first script from the command line to export the data:

D:\MyBCPScript.bat UltraSecurePassword

...and run the second with:

d:\MyBCPScriptsRestore.bat UltraSecurePassword.

Presto! MS SQL2000 database backported with data to v.7! Of course, the order of tables to restore in the second script is important for any related data. Note that with the scripts, I now have the ability to get the latest data later by simply running the batch files again right before going live with the v.7 database. There are some other gotchas that I didn't mention, most due to my incorrect assumptions that kept forgetting that version 7 was released around the same time as Win98. Namely, v.7 doesn't run on XP or on Win2003. Argh. So up with a Virtual Server to take the time machine back and install an instance of Win2000, which does run SQL v.7. However, even then the Enterprise Manager doesn't work for altering a database without upgrading to the latest service pack (after wasted time determining the cause of an error message that seems to have nothing to do with needing a service pack update - http://support.microsoft.com/kb/246588/EN-US).

Btw, this post comes courtesy of Windows Live Writer. After TWO days of attempting to post on blogger.com and regularly getting a "Could not connect to Blogger.com. Saving and publishing may fail. Test connection now" warning just as I was about to post, I gave up. Lo and behold, the blogger api is still up and running so along comes Windows Live Writer to save the day!

Tags:

blog comments powered by Disqus

Month List