是否可以在 sql server 2005 中恢复 Sql Server 2008 备份

Is it possible to restore Sql Server 2008 backup in sql server 2005

提问by Nayana Setty

Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005?

是否可以将 SQL Server 2008 数据库的备份还原到 SQL Server 2005 的实例上？

I need to work on an sample application for which database backup is in sql server 2008. But I'll not be able to install 2008. So is it possible to restore that back up in 2005?

我需要处理一个示例应用程序，该应用程序的数据库备份在 sql server 2008 中。但我将无法安装 2008。那么是否可以在 2005 中恢复该备份？

回答by Garry Shutler

No. It is not possible to restore a database from a backup of a newer version.

不可以。无法从较新版本的备份中恢复数据库。

If you are dead set on it, I think your best option is to selet the database in the Object Explorer in SQL 2008, right-click, select Tasks->Generate Scripts.In the options dialog emable about everything, including Script Data.

And make sure you select "Script for SQL 2005".

如果你对它死心塌地，我认为你最好的选择是在 SQL 2008 的对象资源管理器中选择数据库，右键单击，选择任务-> 生成脚本。在选项对话框中启用所有内容，包括脚本数据。

并确保选择“SQL 2005 脚本”。

Source

来源

When importing the objects into your target server, if the objects are large you may find that you can't open the SQL file via Management Studio (with a completely useless "The operation could not be completed" error, no less). That's okay, just load the file via sqlcmd.

将对象导入目标服务器时，如果对象很大，您可能会发现无法通过 Management Studio 打开 SQL 文件（出现完全无用的“无法完成操作”错误，不少）。没关系，只需通过sqlcmd.

回答by Monte Chavis

One important thing is missing in all answers and that is the fact that Generate Scripts in SSMS doesn't order the scripts correctly.

所有答案中都缺少一件重要的事情，那就是在 SSMS 中生成脚本没有正确排序脚本。

Scripts have to be ordered in the correct dependency order so that child tables are created after parent tables and such.

脚本必须按正确的依赖顺序排序，以便在父表等之后创建子表。

This is not an issue for small databases where its easy to reorder the scripts manually but it can be a huge issue when dealing with databases that have 100+ objects.

这对于小型数据库来说不是问题，因为手动重新排序脚本很容易，但在处理具有 100 多个对象的数据库时，这可能是一个大问题。

My experience is that its most convenient to use third party tools that can read backup and generate scripts in the correct order. I'm using ApexSQL Diffand Data Diff from ApexSQL but you can't go wrong with any popular vendor.

我的经验是使用第三方工具最方便，可以按照正确的顺序读取备份和生成脚本。我正在使用来自 ApexSQL 的ApexSQL Diff和 Data Diff，但任何流行的供应商都不会出错。

回答by Hazem Fergany

Yes it is possible

对的，这是可能的

Using the export in the SQL Server 2008. Go to All Programs--> Microsoft SQL Server 2008--> Import and Export Data

在 SQL Server 2008 中使用导出。转到All Programs--> Microsoft SQL Server 2008--> Import and Export Data

• Then SQL Server Import AND Export Wizardwindow will be opened. Press Next

• Choose a Data source (in your case from SQL Server 2008). Choose a Destination( in your case from SQL Server 2005).

• Select Copy data from one or more tables or view

• Select the source's tables and destination's tables

• Click Next& Finish

• 然后SQL Server Import AND Export Wizard窗口将被打开。按Next

• 选择一个数据源（在您的情况下来自 SQL Server 2008）。选择一个目标（在您的情况下是从 SQL Server 2005）。

• 选择 Copy data from one or more tables or view

• 选择源表和目标表

• 点击Next&Finish

to complete.

去完成。

回答by Mitch Wheat

No, not directly. SQL Server 2008 database backups are not backward compatible with SQL Server 2005. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This articledescribes the process in detail.

不，不是直接的。SQL Server 2008 数据库备份与 SQL Server 2005 不向后兼容。但是，使用 SQL Server 2008 Management Studio，您可以在 SQL Server 2005 模式下编写数据和架构的脚本。该文章详细地描述了过程。

回答by scorpiophd

I have had this problem for a long while.

我有这个问题很长时间了。

You cannot restore SQL2008 backups onto an SQL2005 instance.

您无法将 SQL2008 备份还原到 SQL2005 实例上。

And for me, workarounds like import/export wizzard or to script the database from SQL2008 using the generate scripts with the for SQL2005 option won't work. Because simply, my databases cross-reference each other inside their views and stored procedures or udfs. They do not befall to my responsibility completely and so I cannot consolidate them into 1 database. They are a set of 6 dbs that refer to each other directly inside their views and stored procedures. When I transfer them from one SQL2005 instance onto another, I usually do full-backup/restore.

对我来说，像导入/导出向导这样的解决方法或使用带有 for SQL2005 选项的生成脚本从 SQL2008 编写数据库脚本将不起作用。因为简单地说，我的数据库在它们的视图和存储过程或 udfs 中相互交叉引用。它们不完全属于我的责任，因此我无法将它们合并到 1 个数据库中。它们是一组 6 个 db，在它们的视图和存储过程中直接相互引用。当我将它们从一个 SQL2005 实例转移到另一个实例时，我通常会进行完整备份/恢复。

If I were to script them, even with the with dependencies option I would get errors at re-creation time as db1 will not find views inside db3 because it so happened that I executed the create db1 script first. If I tried db3 first I get similar exceptions.

如果我要编写它们的脚本，即使使用 with dependencies 选项，我也会在重新创建时出错，因为 db1 不会在 db3 中找到视图，因为碰巧我首先执行了 create db1 脚本。如果我先尝试 db3，我会遇到类似的异常。

The only way to script them so that I won't have such dependency exceptions, is to figure out a sequence that works and script them partially in that manner: say: db1_tables followed by db2_tables followed by db2_views followed by db1_views, sp, udfs etc.

为它们编写脚本以便我不会出现此类依赖项异常的唯一方法是找出一个有效的序列并以这种方式部分编写脚本：例如：db1_tables 后跟 db2_tables 后跟 db2_views 后跟 db1_views、sp、udfs 等.

I have such a sequence. And when I need to create a new set of such 6 dbs, I executed the smaller partial scripts in sequence.

我有这样的顺序。当我需要创建一组新的 6 个 dbs 时，我按顺序执行了较小的部分脚本。

This explains why the generate scripts, with dependencies and with data and set to SQL2005 version scripts, will just not work for me.

这解释了为什么带有依赖项和数据并设置为 SQL2005 版本脚本的生成脚本对我不起作用。

The import/export wizzard is better in my case because it will copy tables and then you still have to script all views, sp, udfs etc.

在我的情况下，导入/导出向导更好，因为它会复制表，然后您仍然需要编写所有视图、sp、udfs 等的脚本。

What I really need is a conversion tool for SQL2008 backup files, to convert them to SQL2005 format. Then my problem will go away. Or some kind of a tool that would allow restore from SQL2008 full-backup files, without asking me too many questions.

我真正需要的是 SQL2008 备份文件的转换工具，将它们转换为 SQL2005 格式。那么我的问题就会消失。或者某种允许从 SQL2008 完整备份文件恢复的工具，而不会问我太多问题。

If anyone knows such tools and have used them, let me know.

如果有人知道并使用过此类工具，请告诉我。

回答by MP1963

You can use DBSave, it's a great freeware tool to backup and restore ms sql server on different machines. It's verry simple to setup and to use.

您可以使用DBSave，它是一个很棒的免费软件工具，可以在不同的机器上备份和恢复 ms sql server。设置和使用非常简单。

回答by thecoop

No you can't, but tools like red gate's SQL Compare/Data Compare can read backup files directly & transfer the info across to a live database, dealing with any syntax or settings that aren't compatible on SS2005

不，你不能，但是像 red gate 的 SQL 比较/数据比较这样的工具可以直接读取备份文件并将信息传输到实时数据库，处理任何与 SS2005 不兼容的语法或设置

回答by T.J. Crowder

Having had no luck with the Import/Export stuff (flat file exports failed on import claiming charset mapping issues [even though same charset used throughout] and/or truncation issues [even though source and destination had exact same structure]), and having had no luck with using the "generate scripts" option suggested by Garry Shutler(it generated a script with syntax errors), I was finallyable to copy the big table I wanted to copy from 2008 to 2005 using the SQL Server bcputility. So that's another option for this situation, although for an entire database it would be table-by-table and probably doesn't help with views and such.

导入/导出的东西没有运气（平面文件导出在导入时失败，声称字符集映射问题[即使在整个过程中使用相同的字符集]和/或截断问题[即使源和目标具有完全相同的结构]），并且有使用Garry Shutler 建议的“生成脚本”选项不走运（它生成了一个有语法错误的脚本），我终于能够使用SQL Serverbcp实用程序复制我想从 2008 年复制到 2005 年的大表。所以这是这种情况的另一种选择，尽管对于整个数据库，它会逐表进行，并且可能对视图等没有帮助。

The steps I used:

我使用的步骤：

1. On the source server, use "Script Table As...CREATE" to get the structure, run that on the target server.

2. On the target server, create a bcpformat file using your newly-created table:

   bcp database.owner.table format nul -f table.fmt -n

   (If you're not using Windows auth, you may need the -Uand -Poptions to specify username and password.)

3. Copy that format file to the source server (if necessary).

4. Export the data to file on the source server:

   bcp database.owner.table out table.dat -f table.fmt

   (Again, possibly with -Uand -P.)

5. Copy the data file to the target server (if necessary).

6. Import the data on the target server:

   bcp database.owner.table in table.dat -f table.fmt

   (Again, possibly with -Uand -P.)

1. 在源服务器上，使用“Script Table As...CREATE”来获取结构，在目标服务器上运行它。

2. 在目标服务器上，bcp使用新创建的表创建一个格式文件：

   bcp database.owner.table format nul -f table.fmt -n

   （如果您不使用 Windows 身份验证，您可能需要-U和-P选项来指定用户名和密码。）

3. 将该格式文件复制到源服务器（如有必要）。

4. 将数据导出到源服务器上的文件：

   bcp database.owner.table out table.dat -f table.fmt

   （同样，可能与-U和-P。）

5. 将数据文件复制到目标服务器（如有必要）。

6. 导入目标服务器上的数据：

   bcp database.owner.table in table.dat -f table.fmt

   （同样，可能与-U和-P。）

In hopes that proves useful to someone else.

希望这对其他人有用。