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 you're trying to overwrite an existing database, but did not specify to overwrite/replace the existing database. The following might work:

Use MasterGoRESTORE 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 FILELISTONLYFROM DISK = ‘location of your.bak file’

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

RESTORE DATABASE database1FROM DISK = '\\database path\database.bak'WITHMOVE '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!