Exporting SQL Server 2000 database to 7.0

[Sarahz]

Hello all,

I'm new to using SQL Server and I have a question about compatibility issues. I created a database in SQL Server 2000 (SP3) and I need to export it using DTS to a remote server that's running SQL Server 7.0. Are there any compatibility issues I should be aware of? I've seen a lot about upgrading 7.0 to 2000, but haven't had much luck finding information on this particular issue in Books Online or elsewhere.

I did set the database compatibility level to 70 to force SQL Server 7.0 behavior on my local database and have not had any problems, so I'm *assuming* the database itself would be fine on the remote server. Of course, I'd rather find out now whether to expect any problems than during exporting.

Thanks for your help!

 

[tlbroadbent]

You should be able to copy the database to SQL Server 7 but you will have to use DTS. You cannot do Backup/Restore or Detach/Attach. You can also script the database and all objects, export the data, create the DB and objects on SQL Server 7, and import the data. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Sarahz]

Thanks so much for your quick response! Just to make sure I understand, once the database is copied to 7.0, I cannot backup or restore it at all?

 

[tlbroadbent]

You can backup and restore on SQL 7. What I meant is that the file formats of SQL 7 and SQL 2000 are different. A backup file created by SQL 2000 cannot be restored on SQL 7. A backup created on SQL 7 can be restored on SQL 2000. The same principle is true for Detach/Attach. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Sarahz]

Okay, that makes sense. I understand. Thanks for your help!