SQL Server database restore error: specified cast is not valid. (SqlManagerUI)

sql-server sql-server-2008 database-restore

I am using SQL Server 2008 R2 Standard (version 10.50.1600.1) for my production website and SQL Server Express edition with Advanced Services (v10.50.1600.1) for my localhost as a database.

Few days back my SQL Server crashed and I had to install a new 2008 R2 Express version on my localhost. It worked fine when I restored some older versions taken from Express edition but when I try to restore database from .bak file which is taken from production server it is causing the following error:

Error: Specified cast is not valid. (SqlManagerUI)

and when I try to restore the database using command

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'

It generates a different error

Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'Publications' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I have cross checked the versions. They all seem matching to me as shown in the image below

Previously I was able to restore a database from standard version to express edition but now it fails. I deleted the database and tried to recreate it. That fails, too.

I am not sure what I am doing wrong. I would appreciate help in this regarding

Issue was resolved as it seems .bak file was corrupt. When I tried it with a different file it worked.

im not pro in this, but quick question, is x86 and x64 arhitecture in database compatible?

I had previous restore database at that time i didn't face any such issue. for some reason now it generating errors i am not sure if database server collation is creating any problems

Try adding ,REPLACE to the T-SQL command to overwrite the existing AlHabtoorPublications database.

I hit same issue at work today.. Seems like checking file size post doing FTP transfer not sufficient. Seems like the file is very sensitive. Solved the issue by zipping the file before executing the transfer.

Scenerio I took database backup on SQL SERVER 2008 and tried restoring it on SQL SERVER 2008 R2. Ideally, it should work fine but while selecting backup file, SQL Management Studio 2208 R2 was raising error "Specified cast is not specified. (SqlManagerUI)". Reason & Troubleshoot This was due to .BAK file corruption during FTP transfer (transfer mode is set to ASCII). Always, remember to set FTP transfer mode to BINARY while transferring database .BAK file.

Subhash Makkena

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

One would need to use "Generate Script" instead.

This was my problem. It seems like it wouldn't be too hard for MS to give a more helpful message for some of these cases.

Pinal Dave explains the reasons here -blog.sqlauthority.com/2015/06/01/…

Irshad

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.

Odd, I reused your original statement. In any case, the only thign I added was the last line: , REPLACE

Actually, the Failed: 38 indicates reached end of the file. (In a command window, run NET HELPMSG 38). That usually indicates a corrupt backup: stackoverflow.com/questions/5656363/…

I tested restore with bit older .bak file which was restore without any issue. It seems this particular .bak file is corrupt

Might have been a result of the crash. It could have botched up parts of the drive. Glad to hear you managed to resolve the issue with a different backup though :)

You script generate following error Msg 3203, Level 16, State 1, Line 1 Read on "C:\Publications.bak" failed: 38(failed to retrieve text for this error. Reason: 15105) Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Chris Peters

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!

Keren Caelen

Below can be 2 reasons for this issue:

Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2 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

SQL Server database restore error: specified cast is not valid. (SqlManagerUI)

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US