I just encountered this error when restoring a SQL Server FULL backup file I got from one our clients - Specified cast is not valid. (SqlManagerUI)
This less-than-helpful error wasn't good enough for me to come to a conclusion so I decided to restore the database using a T-SQL command(Here is the command I used: RESTORE DATABASE CRM FROM DISK = 'E:\CRM.bak') and I got a different error which is shown below.
The media family on device 'E:\CRM.bak' is incorrectly formed. SQL Server cannot process this media family.
The first thing that came to my mind after looking at the above error was that the backup file is corrupted. So I decided to verify using VERIFYONLY and HEADERONLY commands and below is the output.
The two observations above made me lean more towards believing that the backup file is corrupted or incomplete, BUT the issue turned out to be something else – The backup I got from our client was taken on SQL Server 2012 instance and I was trying to restore it to SQL Server 2008R2. I had no idea about the version of SQL Server our client is using because they send us a backup file every quarter and we just restore it and generate audit reports for them.
I definitely think that the error message should have been more descriptive, but had it been so, I wouldn't have written this blog post. Hope this helps others.