SQL Server database restore error: specified cast is not valid. (SqlManagerUI)
Sql ServerSql Server-2008Database RestoreSql Server Problem Overview
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.
Sql Server Solutions
Solution 1 - Sql Server
Could be because of restoring SQL Server 2012 version backup file into SQL Server 2008 R2 or even less.
Solution 2 - Sql Server
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.
Solution 3 - Sql Server
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!
Solution 4 - Sql Server
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