SQL 2000 SP3 - compatibility problem

[Sunny4Ever]

We upgraded all our SQL 2000 servers to SP3 last week due to the Sapphire worm. Now we are getting an issue where when we create a backup of a database on a 2000 SP3 and try to restore it to a SQL 7 server it is failing with "wrong version" errors. Pre SP3 we had no trouble with this operation.

Any ideas would be great as this is pretty serious.

Thanks,
Sunny

 

[tlbroadbent]

I don't believe you ever restored a SQL Server 2000 backup to SQL Server 7. It has never been possible. Restoring SQL 7 backup to SQL 2000 is possible. You'll have to use DTS to move the database from 2000 to 7. Terry L. Broadbent - DBA
SQL Server Page:

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

If you want to get the best answer for your question read faq183-874.

 

[Sunny4Ever]

Hi Terry

I was under the impression that within the properties of each DB you can set the compatiblity. If you set it to 7.0, my current understanding is that you can restore the DB (after perfroming a new backup), into the 7.0 installation?

If this isn't the case, what is the purpose of this option.

Thanks

 

[tlbroadbent]

SQL BOL explains the compatibility option.

"Sets certain database behaviors to be compatible with the specified earlier version of Microsoft SQL Server."

The reason you cannot restore a SQL Server 2000 database to SQL Server 7 is that 1) the physical file format changed 2) the system databases were changed. 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