SQLServer 数据库还原错误: 指定的强制转换无效

我正在使用 SQL Server 2008 R2标准(版本10.50.1600.1)为我的生产网站和 高级服务(v10.50.1600.1)的 SQL Server Express 版本作为我的本地主机的数据库。

几天前，我的 SQLServer 崩溃了，我不得不在本地主机上安装一个新的2008R2Express 版本。当我从 Express 版本恢复一些旧版本时，它工作得很好，但是当我试图从从生产服务器恢复 .bak文件中的数据库时，它会导致以下错误:

错误: 指定的强制转换无效。(SqlManagerUI)

当我尝试使用命令恢复数据库时

Use Master
Go
RESTORE DATABASE Publications
FROM DISK = 'C:\Publications.bak'
WITH MOVE 'Publications' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.mdf',--adjust path
MOVE 'AlPublications_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.ldf'

它会产生一个不同的错误

味精3154,16层，4号州，1号线
备份集包含现有“发布”数据库以外的数据库的备份。
味精3013,16层，状态1，线路1
恢复数据库异常终止

我已经交叉检查了这些版本，它们看起来都与我相符，如下图所示

以前我能够将数据库从标准版本恢复到快速版本，但现在它失败了。我删除了数据库，试图重建它。那也不行。

我不知道我做错了什么。我希望在这方面得到帮助

问题解决了 ，就像它看起来的那样。 bak 文件损坏了。当我用另一个文件尝试它时，它工作了。

437862 次浏览
小开
最佳答案

The GUI can be fickle at times. The error you got when using T-SQL is because you're trying to overwrite an existing database, but did not specify to overwrite/replace the existing database. The following might work:

Use Master
Go
RESTORE DATABASE Publications
FROM DISK = 'C:\Publications_backup_2012_10_15_010004_5648316.bak'
WITH
MOVE 'Publications' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.mdf',--adjust path
MOVE 'Publications_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\DATA\Publications.ldf'
, REPLACE -- Add REPLACE to specify the existing database which should be overwritten.
小开

Finally got this error to go away on a restore. I moved to SQL2012 out of frustration, but I guess this would probably still work on 2008R2. I had to use the logical names:

RESTORE FILELISTONLY
FROM DISK = ‘location of your.bak file’

And from there I ran a restore statement with MOVE using logical names.

RESTORE DATABASE database1
FROM DISK = '\\database path\database.bak'
WITH
MOVE 'File_Data' TO 'E:\location\database.mdf',
MOVE 'File_DOCS' TO 'E:\location\database_1.ndf',
MOVE 'file' TO 'E:\location\database_2.ndf',
MOVE 'file' TO 'E:\location\database_3.ndf',
MOVE 'file_Log' TO 'E:\location\database.ldf'

When it was done restoring, I almost wept with joy.

Good luck!

小开

Could be because of restoring SQL Server 2012 version backup file into SQL Server 2008 R2 or even less.

小开

Below can be 2 reasons for this issue:

  1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2

  2. Backup media is corrupted.

If we run below command, we can find actual error always:

restore headeronly
from disk = 'C:\Users\Public\Database.bak'

Give complete location of your database file in the quot

Hope it helps


提交答案