• Article

Copying Databases from SQL Server 7.0 or Earlier

This topic discusses how to migrate databases from old versions of SQL Server that cannot be upgraded to the current version.

Note

When you install SQL Server 2008 R2, any existing SQL Server 2000 or SQL Server 2005 databases are automatically upgraded (SQL Server 2008 databases are already compatible with SQL Server 2008 R2). To copy an upgraded database, you can use any of the copy methods supported for SQL Server 2008 databases. For more information, see Copying Databases to Other Servers.

SQL Server 7.0 Databases

You can convert a SQL Server version 7.0 database to SQL Server 2008 R2 by using one of the following methods:

  • Upgrade a SQL Server 7.0 database to SQL Server 2000 or SQL Server 2005 by attaching the database to an instance running either. Then you can upgrade the database to SQL Server 2008 R2. Generally, this is the preferred method.

    For information about using attach to upgrade a SQL Server 2000 or SQL Server 2005 database, see How to: Upgrade a Database Using Detach and Attach (Transact-SQL).

  • Use the SQL Server Import and Export Wizard to copy data between multiple instances of SQL Server. This wizard works with any the source and destination for which there is a provider, although data conversion issues might occur, depending on the data source. For more information, see Using the SQL Server Import and Export Wizard to Move Data.

  • Migrate data from a database that was created in SQL Server 7.0 as follows:

    1. Using version 7.0 of bcp, export the data into a data file by using a bcpout command.

    2. Using the version of bcp.exe in SQL Server 2008 R2 (version 10.50) of bcp, import the data from the data file by using a bcp in command. If the data file contains native data formats, specify the -V 70 and –n options, which tell the bcp in operation to use the SQL Server 7.0 native data types.

    For more information, see Importing Native and Character Format Data from Earlier Versions of SQL Server.

SQL Server 6.0 or SQL Server 6.5 Databases

To migrate data from a SQL Server version 6.0 or SQL Server version 6.5 database, use the bcp utility in that version of SQL Server to export the data into a data file in character mode (bcp out). You can then import the character data into a SQL Server 2008 R2 database. However, SQL Server 6.0 and SQL Server 6.5 native data formats are not supported by SQL Server 2008 R2. This means that the version of bcp.exe in SQL Server 2008 R2 does not support the -6 command-line option, or the 60 and 65 options of the –V command-line option.

Note

Database backups that were created by using SQL Server 6.5 or earlier are in an incompatible format and cannot be restored in SQL Server 2005 and later versions.

Database Compatibility Level After Upgrade

The compatibility levels of the tempdb, model, msdb and Resource databases are set to 100 after upgrade. The master system database retains the compatibility level it had before upgrade, unless that level was less than 80. If the compatibility level of master was less than 80 before upgrade, it is set to 80 after upgrade. New user databases will inherit the compatibility level of the model database.

If the compatibility level of a user database was 80 or 90 before upgrade, it remains the same after upgrade. If the compatibility level was 70 or less before upgrade, in the upgraded database, the compatibility level is set to 80, which is the lowest supported compatibility level in SQL Server 2008 R2.

Note

For information on using a SQL Server 7.0, SQL Server 2000, or SQL Server 2005 database on SQL Server 2008 R2, see ALTER DATABASE Compatibility Level (Transact-SQL).