SQL Server database restore error specified cast is not valid SqlManagerUI

Question

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

User · Answer

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!

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] SQL Server database restore error: specified cast is not valid. (SqlManagerUI)

Examples related to sql-server

Examples related to sql-server-2008

Examples related to database-restore