INF: Attaching or Restoring a SQL Server 7.0 Database To SQL Server 2000 Does Not Change the Compatibility Mode (285165)

MORE INFORMATION

Attaching or restoring a SQL Server 7.0 database to SQL Server 2000 represents a one-way upgrade process that makes modifications to the internal database structure, but does not change the database compatibility setting. This behavior was chosen for the movement of individual databases in order to reduce the chance that a change in behavior in the newer version would break an existing application.

This differs from the behavior observed in an upgrade of an entire SQL Server installation, where it is assumed that you want to have all databases with SQL Server 7.0 compatibility mode changed to SQL Server 2000 compatibility mode.

To illustrate the behavior, follow these steps:

1. Create a sample database named "old" on a SQL Server 7.0 server. Set the compatibility level of the database to that of SQL Server 6.5, and then detach the database to take it offline. For example:

   USE master
   go
   
   CREATE DATABASE old
   ON 
   ( NAME = old_dat,
     FILENAME = 'c:\mssql7\data\old.mdf' )
   go
   
   EXEC sp_dbcmptlevel old, 65
   EXEC sp_detach_db old
   					

   The results are:

   The CREATE DATABASE process is allocating 0.75 MB on disk 'old_dat'.
   The CREATE DATABASE process is allocating 0.49 MB on disk 'old_log'.
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.
   Successfully detached database 'old'.
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.
   					

2. Copy the two database files, old.mdf and old_log.ldf, to a directory on the SQL Server 2000 server. In this example, C:\Data is the destination directory.
3. Attach the database to the SQL Server 2000 server, then display the current compatibility level.

   EXEC sp_attach_db 'old', 'c:\data\old.mdf', 'c:\data\old_log.ldf'
   EXEC sp_dbcmptlevel old
   					

   Note that the compatibility level is preserved even after the upgrade to the file structure completes.

   Converting database 'old' from version 515 to the current version 539.
   Database 'old' running the upgrade step from version 515 to version 524.
   Database 'old' running the upgrade step from version 524 to version 525.
   Database 'old' running the upgrade step from version 525 to version 526.
   Database 'old' running the upgrade step from version 526 to version 527.
   Database 'old' running the upgrade step from version 527 to version 528.
   Database 'old' running the upgrade step from version 528 to version 529.
   Database 'old' running the upgrade step from version 529 to version 530.
   Database 'old' running the upgrade step from version 530 to version 531.
   Database 'old' running the upgrade step from version 531 to version 532.
   Database 'old' running the upgrade step from version 532 to version 533.
   Database 'old' running the upgrade step from version 533 to version 534.
   Database 'old' running the upgrade step from version 534 to version 535.
   Database 'old' running the upgrade step from version 535 to version 536.
   Database 'old' running the upgrade step from version 536 to version 537.
   Database 'old' running the upgrade step from version 537 to version 538.
   Database 'old' running the upgrade step from version 538 to version 539.
   To achieve optimal performance, update all statistics on the 'old' database by running sp_updatestats.
   The current compatibility level is 65.
   					

Similar behavior is also observed if the database is backed up from the SQL Server 7.0 server and then restored to a SQL Server 2000 server.