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

Article translations Article translations
Article ID: 285165 - View products that this article applies to.
This article was previously published under Q285165
Expand all | Collapse all

SUMMARY

If a database that is running in SQL Server 6.5 or SQL Server 7.0 compatibility mode is detached or backed up from a SQL Server 7.0 server and then attached or restored to a SQL Server 2000 server, the original compatibility mode is preserved in the .mdf database file.

This is also true if a database is re-attached or restored to a SQL Server 7.0 server.

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.

Properties

Article ID: 285165 - Last Review: October 30, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbinfo KB285165

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com