Export (0) Print
Expand All

Copying Databases from SQL Server 7.0 or Earlier

When you install SQL Server 2008, any existing databases are automatically upgraded. 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.

NoteNote

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

You can convert a SQL Server version 7.0 database to SQL Server 2008 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. 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 Importing and Exporting Data by Using the SQL Server Import and Export Wizard.

  • 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 (version 10.0) 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.

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 database. However, SQL Server 6.0 and SQL Server 6.5 native data formats are not supported by SQL Server 2008. This means that the version of bcp.exe in SQL Server 2008 does not support the -6 command-line option, or the 60 and 65 options of the –V command-line option.

NoteNote

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.

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.

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.

NoteNote

New user databases will inherit the compatibility level of the model database.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft