Couple of days ago, tried to restore a SQL database backup that I got from my client. I was restoring it to my SQL 2008 R2 database engine. I followed the following steps:
1. Right click the databases folder and then chose Restore Database from the drop down list
2. I got the below popup so I choose the proper name for the database and selected the backup file
but I got surprised with a not meaningful message from SQL server saying “Specified cast if not valid (SqlManagerUI)”.
Although this error message doesn’t give much info, but it is telling that the SQL 2008 is not being able to read the database backup headers. So there was couple of possible causes for this:
1. The database backup is corrupted.
2. This is a backup from a newer version of SQL Server
3. This backup is password protected.
In my situation it was 2nd cause, so I only restore it on SQL 2012 Engine and things went fine with me.