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

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

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

The GUI can be fickle at times. The error you got when using T-SQL is because youre 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 FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESS2008R2MSSQLDATAPublications.mdf,--adjust path
    MOVE Publications_log TO C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESS2008R2MSSQLDATAPublications.ldf
, REPLACE -- Add REPLACE to specify the existing database which should be overwritten.

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

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 pathdatabase.bak
WITH
MOVE File_Data TO E:locationdatabase.mdf,
MOVE File_DOCS TO E:locationdatabase_1.ndf,
MOVE file TO E:locationdatabase_2.ndf,
MOVE file TO E:locationdatabase_3.ndf,
MOVE file_Log TO E:locationdatabase.ldf

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

Good luck!