SQL SERVER – Restore Error: Specified cast is not valid (SqlManagerUI)

As I always say, Errors are a great source of learning and I am fortunate to have you as my readers. Because one of my blog reader reported an interesting issue which is worth blogging. This is his initial email:

Hi Pinal,
Your blogs have been very helpful to me to find solution of almost any SQL problem so far. This time, I am writing directly to see if you have time and provide some help to me.
I have a backup from database in SQL Server 2008 R2. When I want to restore this backup to SQL Server, I get this error: “Error: Specified cast is not valid. (SqlManagerUI)” How to I resolve this error?
Thanks!

We then exchanged various mails with screenshots, query, output and with his patience, we were able to find the cause of the issue. Here we go to what happened behind the scenes:

Here were the steps to restore the database:

As soon as OK was clicked, this was the error.

If we click on red cross icon at left bottom, we would see below

Here is the partial text of the message.

Specified cast is not valid. (SqlManagerUI)
------------------------------
Program Location:
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

As we can see above that SSMS is trying to populate the grid with the details about the backup. When I put profiler on my machine, it was running Restore Headeronly command on the selected file. So, I asked to run the command manually. You can refer my earlier blog for this SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

When we ran the command, we found below

Above could be because of two reasons (which I found)

1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
2. Backup media is corrupted.

Solution of 1^st one is easy – restore it on same or higher version. 2^nd one is a difficult situation because you need to look for another good/restorable backup. In my case, it was first situation so we were good.

Another possible reason of “Specified cast is not valid” would be password protected backups. In such backups, headeronly would return “*** PASSWORD PROTECTED ***” in the first column as shown below.

So, next time you ever see any UI error, find the T-SQL statement by using profiler and run that directly to see “real” error message.

Have you ever come across situation where UI was giving some misleading error? Please comment and share your knowledge.

Reference: Pinal Dave (https://blog.sqlauthority.com)