SOS!请教数据库大虾们怎样将SQL server 2000的数据库移到 SQL server 7上?

[人在天涯]

sp_attach_db 报错

The header for file 'f:\mssql7\data\iipm-mpri_Data.mdf' is not a valid database file header. The PageAudit property is incorrect.

我想是版本不同导致的格式不同,不知道有什么办法解决

多谢

 

[Rationalist]

Try Export/Import

Export all the database objects into SQL Script, run them in SQL Server 7, you may need modify the script slightly.

Better to know what kinds of database objects you want to transform, such as data rows, data tables, user functions, store procedures and users.

 

[Rationalist]

There are a variety of methods actually:-

Please pay attention to Item 4 and 6 for your case.

0. the fastest way is to detach/attach db data file, you already tried this
and it won't work for version difference.

1. The second fastest method for whole databases is to use the DUMP DATABASE and
LOAD DATABASE commands. You need to make sure that the databases are the
same size and made up of the same segment fragments in the same order. If
you do an sp_help_revdatabase on both this will allow you to check the
required DDL for this. You can DUMP and LOAD from a local tape device and
transport the tape if you do not have a network connection. (With SQL
7.0/2000 the commands are BACKUP DATABASE and RESTORE DATABASE). You may have
the same problem as Itme 0 with different database version.

2. If you only want tables/data you can use the SQL BCP.EXE tool. This is
a command-line program and is fully documented in the books-online. It
works on one table at a time and allows you to create a flat file on disk.

3. For stored-procedures/views etc. there is an old command-line based tool
called DEFNCOPY.EXE that works like BCP.

4. SQL Enterprise Manager comes with a built-in GUI transfer function.
This allows transfer of all objects between two databases/servers but
requires a network connection between the two.

5. The transfer tool supplied with SQL Enterprise Manager is exposed via
the DMO interface and can be called using the SQLOLE calls from TSQL or your
own VB program for automation purposes.

6. For SQL 7.0 and above the obvious choice is to use Data Transformation
Services. You can transfer and data by simply using the Import/Export Wiazrd
to build a package based on the DataPump/Transform Data Task. The
TransferObjects task can be used to move all objects as well as tables, and
supports the transfer of keys, indexes and pemissions as well as just data.
In SQL 2000 there is also the Transfer Database Task which greatly
simplifies the task of transferring an entire database.

7. 3rd-party DBMS management tools no doubt offer similar/better
transfer/scripting tools to the above.

 

[ottolily]

好心人哪！

 

[人在天涯]

先谢谢Rationalist大虾的回复,DTS行不同因为两个server之间的连接太差了,我会试试你说的别的办法

最初由 Rationalist 发布

1. The second fastest method for whole databases is to use the DUMP DATABASE and
LOAD DATABASE commands. You need to make sure that the databases are the
same size and made up of the same segment fragments in the same order. If
you do an sp_help_revdatabase on both this will allow you to check the
required DDL for this. You can DUMP and LOAD from a local tape device and
transport the tape if you do not have a network connection. (With SQL
7.0/2000 the commands are BACKUP DATABASE and RESTORE DATABASE). You may have
the same problem as Itme 0 with different database version.

2. If you only want tables/data you can use the SQL BCP.EXE tool. This is
a command-line program and is fully documented in the books-online. It
works on one table at a time and allows you to create a flat file on disk.

3. For stored-procedures/views etc. there is an old command-line based tool
called DEFNCOPY.EXE that works like BCP.

4. SQL Enterprise Manager comes with a built-in GUI transfer function.
This allows transfer of all objects between two databases/servers but
requires a network connection between the two.

5. The transfer tool supplied with SQL Enterprise Manager is exposed via
the DMO interface and can be called using the SQLOLE calls from TSQL or your
own VB program for automation purposes.

6. For SQL 7.0 and above the obvious choice is to use Data Transformation
Services. You can transfer and data by simply using the Import/Export Wiazrd
to build a package based on the DataPump/Transform Data Task. The
TransferObjects task can be used to move all objects as well as tables, and
supports the transfer of keys, indexes and pemissions as well as just data.
In SQL 2000 there is also the Transfer Database Task which greatly
simplifies the task of transferring an entire database.

7. 3rd-party DBMS management tools no doubt offer similar/better
transfer/scripting tools to the above.

 

[yh_abc]

backup and restore may also work