将备份从 SQL2K 恢复到 SQL2008R2 时出错
我正在 SQL 2008 R2 机器上恢复 SQL 2000 用户数据库，但有时会收到以下错误。这是 SQL 日志。
2010 年 11 月 22 日 09:32:00 | SPID61 |正在启动数据库“DB_OPP_P_2”。
2010 年 11 月 22 日 09:32:00 | SPID61 |这 数据库“DB_OPP_P_2”已标记 正在恢复并且处于这样的状态 不允许运行恢复。
2010 年 11 月 22 日 09:32:33 | SPID61 |正在启动数据库“DB_OPP_P_2”。
2010 年 11 月 22 日 09:32:33 | SPID61 |恢复正在数据库“DB_OPP_P_2”(51) 中写入检查点。 这只是一条信息性消息。无需用户执行任何操作。
2010 年 11 月 22 日 09:32:36 | SPID61 |错误：928，严重性：20，状态：1。
2010 年 11 月 22 日 09:32:36 | SPID61 |升级期间，数据库引发异常 156，严重性 25， 状态 1，地址 0000000002225A9F。使用异常编号来确定原因。
由于它显示异常发生在升级阶段，我相信它发生在恢复百分比达到 100% 之后。
我已经实现了此恢复的脚本重试，但尝试的 5 次都失败了。但是，如果我使用相同的恢复脚本手动重试，恢复就会完成。我已经在源上运行了 DBCC CHECKDB，并且没有返回错误。
数据库的兼容性级别为 80 (sql 2000)，版本为 539。
在有问题的数据库上运行升级顾问时，我看到 27 个对象具有旧的外连接 sintax *=, =*
不幸的是，我将无法更改进程以检查这是否是罪魁祸首。 另一种可能性是，有很多过程没有 BEGIN 和 END 来界定其扩展名，如果我批量创建它们，这会混淆 SQL。
更新：
- 我已成功恢复 SQL 2000 机器上的数据库。
- 我还在我的工作中实现了重试行为，但 5 次都失败了。
重试行为失败。但奇怪的是，如果我使用相同的恢复脚本重试，该作业尝试恢复会成功。
更新2： 我在迁移到 sql 2008 r2 时手动恢复了数据库。 所以我还没有解决这个问题，也不会解决它，因为在 sql 2008 r2 上这不会发生。 感谢您的帮助。
I'm restoring a SQL 2000 user database on a SQL 2008 R2 box, but sometimes I receive the following error. This is the SQL Log.
22/11/2010 09:32:00 | SPID61 | Starting up database 'DB_OPP_P_2'.
22/11/2010 09:32:00 | SPID61 | The
database 'DB_OPP_P_2' is marked
RESTORING and is in a state that does
not allow recovery to run.22/11/2010 09:32:33 | SPID61 | Starting up database 'DB_OPP_P_2'.
22/11/2010 09:32:33 | SPID61 | Recovery is writing a checkpoint in database 'DB_OPP_P_2' (51).
This is an informational message only. No user action is required.22/11/2010 09:32:36 | SPID61 | Error: 928, Severity: 20, State: 1.
22/11/2010 09:32:36 | SPID61 | During upgrade, database raised exception 156, severity 25,
state 1, address 0000000002225A9F. Use the exception number to determine the cause.
Since it shows the exception occurred during upgrade phase, I believe it happened after restore percent hit 100%.
I've implemented a script retry of this restore, and it failed all 5 times it tried. However if I retry manually with the same restore script the restore completes. I've run DBCC CHECKDB on the source and no error has been returned.
Compatibility level of the database is 80 (sql 2000) and version is 539.
While running Upgrade Advisor on the problematic database of the time I see 27 objects with the old outer join sintax *=, =*
Unfortunattly I won't be able to change the procs to check if this is the culprit.
One other possibility is that there are a lot of procs that doesn't have BEGIN and END delimiting its extensions, and if I create them in a batch this confuses SQL.
UPDATE:
- I've successfully restored the database on a SQL 2000 box.
- I also implemented a retry behavior on my job, that failed all 5 times.
The retry behavior failed. But the strange thing is that if I retry with the same restore script the job tried the restore succeeds.
UPDATE 2:
I've manually restored the database on my migration to sql 2008 r2.
So I haven't fixed this issue, and will not fix it, as on sql 2008 r2 this doesn't happen.
Thanks for your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
检查SQL Server 2000中数据库的兼容性：它们是65吗？
错误 156 基本上是错误的语法，通常是由兼容性不匹配引起的。 SQL Server 2008 不支持“65”
您是否尝试过 ？
编辑：
外部连接。嗯
刚刚注意到 SQL Server 2005 已停产 但对于 SQL Server 2008 已弃用。我的猜测是，
*0和0*语法正在破坏一些东西，无论升级顾问和 MSDN 怎么说。就我个人而言，自从我从 6.5 迁移到 SQL Server 7 以来，我就没有使用过它了...Check the compatibility of the databases in SQL Server 2000: are they 65?
Error 156 is incorrect syntax, basically, and it's usually caused by compatibility mismatch. "65" is not supported in SQL Server 2008
Did you try the upgrade advisor before backup/restore?
Edit:
Outer JOIN. Hmmm
Just noticed that for SQL Server 2005 it's discontinued but for SQL Server 2008 it's deprecated. My guess is that the
*0and0*syntax is breaking things, regardless of what the upgrade advisor and MSDN says. Personally I haven't used it since, er, I migrated to SQL Server 7 from 6.5...我在迁移到 sql 2008 r2 时手动恢复了数据库。
所以我还没有解决这个问题，也不会解决它，因为我已经转移到 sql 2008 r2 并且这种情况不会再发生了。
非常感谢您的帮助。
I've manually restored the database on my migration to sql 2008 r2.
So I haven't fixed this issue, and will not fix it, as I've moved to sql 2008 r2 and this doesn't happen anymore.
Thank you very much for your help.