SQL 将Sql Server 2005数据库导入Sql Server express 2008

Importing Sql Server 2005 database into Sql Server express 2008

提问by Matthew Campbell

Is there any way to import a database backup from 2005 into 2008 express edition. What I've had to resort to is doing a script the database, then import all the data through DTS. Whenever I tried to import straight from a backup file it says something about not being to import into a new version of sql server or I'll get the below error.

有什么办法可以将2005年的数据库备份导入2008年速成版。我不得不求助于为数据库编写脚本，然后通过 DTS 导入所有数据。每当我尝试直接从备份文件导入时，它都会说一些关于不导入新版本的 sql server 的内容，否则我会收到以下错误。

title: Microsoft SQL Server Management Studio

标题：Microsoft SQL Server 管理工作室

Specified cast is not valid. (SqlManagerUI)

指定的演员表无效。(SqlManagerUI)

回答by Arvo

SQL 2005 backups should be restored on 2008 without problem (have done that myself many times). What exact error message did you got about versions? The other error (Specified cast is not valid) seems to be Management Studio error, not server error. Have you full rights on 2008 server?

SQL 2005 备份应该可以在 2008 上正常恢复（我自己已经做过很多次了）。您收到了哪些有关版本的确切错误消息？另一个错误（指定的演员表无效）似乎是 Management Studio 错误，而不是服务器错误。你有 2008 服务器的全部权限吗？

Look at this question - any-reason-to-have-sql-server-2005-and-2008-installed-on-same-machine- there are talked both about restoring SQL2005 backups and about attaching SQL2005 data files.

看看这个问题 - any-reason-to-have-sql-server-2005-and-2008-installed-on-same-machine- 讨论了恢复 SQL2005 备份和附加 SQL2005 数据文件。

(Of course you cannot attach production databases. To attach non-production databases just detach them, make file level copy of these and attach copies to new server. Orginal files need to attach to original server too :))

（当然你不能附加生产数据库。要附加非生产数据库只需将它们分离，制作这些的文件级副本并将副本附加到新服务器。原始文件也需要附加到原始服务器:)）

回答by Philippe Grondier

I imagine that you are in a development process where your data will have to be regularly copied to you SQL 2008 server.

我想您正处于开发过程中，您的数据必须定期复制到 SQL 2008 服务器。

You can then think of configuring a replication between the SQL 2005 server (publisher) and SQL Server Express 2008 (suscriber). Depending on your requirements, you have the choice between snapshot or merge replication. If no update is done on the suscriber side, go for snapshot.

然后您可以考虑在 SQL 2005 服务器（发布者）和 SQL Server Express 2008（订阅者）之间配置复制。根据您的要求，您可以选择快照复制或合并复制。如果在订阅者端没有更新，就去快照。

Once you want to have your 2008 server running independantely from the publisher, just delete the replication.

一旦您想让您的 2008 服务器独立于发布者运行，只需删除复制即可。

回答by ScaryDBA

A valid SQL Server 2005 Express backup file should be able to be restored to SQL Server 2008 Express. If the SQL Server 2005 backup is from the Standard or Enterprise versions, you might hit problems restoring it to Express. The user context that the backup was created from should not affect the ability to restore that backup.

有效的 SQL Server 2005 Express 备份文件应该能够恢复到 SQL Server 2008 Express。如果 SQL Server 2005 备份来自标准版或企业版，则在将其还原到 Express 时可能会遇到问题。创建备份的用户上下文不应影响恢复该备份的能力。

One thing you can do is to try running the restore operation as a verification, without actually trying to run the restore. That will tell you if the backup file is valid or not. You can use this syntax:

您可以做的一件事是尝试运行还原操作作为验证，而无需实际尝试运行还原。这将告诉您备份文件是否有效。您可以使用以下语法：

RESTORE VERIFYONLY
FROM yourbackupfile.bak

回答by ScaryDBA

If possible, I would also suggest trying to simply detach the original database from 2005 and then attaching the file at the 2008 edition.

如果可能，我还建议尝试简单地从 2005 年分离原始数据库，然后附加 2008 年版本的文件。

While I have only tried this with the standard edition myself, it has worked perfectly with the compatibility mode keeping the database set to 2005.

虽然我自己只在标准版中尝试过这个，但它与将数据库设置为 2005 的兼容模式完美配合。

Have you tried running the Upgrade Advisor http://www.microsoft.com/download/en/details.aspx?id=11455- it might be able to highlight problems for upgrading the original database.

您是否尝试过运行升级顾问http://www.microsoft.com/download/en/details.aspx?id=11455- 它可能能够突出显示升级原始数据库的问题。