Errors are a common occurrence in SQL Server, and they can often be a great source of learning. Recently, one of my blog readers encountered an interesting issue that I believe is worth discussing. In this article, we will explore the error message “Specified cast is not valid” and discuss how to resolve it.

The reader reached out to me with the following email:

Hi Pinal,

Your blogs have been very helpful to me in finding solutions to almost any SQL problem so far. This time, I am writing directly to see if you have time to provide some help to me.

I have a backup from a database in SQL Server 2008 R2. When I try to restore this backup to SQL Server, I get the following error: “Error: Specified cast is not valid. (SqlManagerUI)”

How can I resolve this error?

Thanks!

After exchanging several emails with screenshots, queries, and outputs, we were able to identify the cause of the issue. Here’s what happened behind the scenes:

The reader followed these steps to restore the database:

1. Open SQL Server Management Studio (SSMS)
2. Right-click on the target database and select “Tasks” > “Restore” > “Database…”
3. In the “General” tab, select “Device” as the source and click on the “…” button to browse for the backup file
4. Select the backup file and click “OK”

As soon as the “OK” button was clicked, the error message “Specified cast is not valid” appeared. Clicking on the red cross icon at the bottom left corner revealed the following 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)

From the above information, it was clear that SSMS was trying to populate the grid with details about the backup. To investigate further, I asked the reader to run the “Restore Headeronly” command manually. This command retrieves information about the backup file.

When we ran the command, we found the following output:

BackupName: DatabaseBackup

BackupDescription: Database backup

BackupType: Full

…

Based on this output, I identified two possible reasons for the “Specified cast is not valid” error:

1. The backup was taken on SQL Server 2012 and the “Restore Headeronly” command was executed on SQL Server 2008 R2. In this case, the solution is to restore the backup on the same or a higher version of SQL Server.
2. The backup media is corrupted. This is a more difficult situation because you would need to look for another good/restorable backup.

In the reader’s case, it was the first situation, so we were able to resolve the error easily.

Another possible reason for encountering the “Specified cast is not valid” error is when dealing with password-protected backups. In such cases, the “Restore Headeronly” command would return “*** PASSWORD PROTECTED ***” in the first column.

So, the next time you encounter a UI error in SQL Server, I recommend finding the corresponding T-SQL statement by using a profiler and running it directly to see the “real” error message.

Have you ever come across a situation where the UI was giving a misleading error? Please share your experiences in the comments section below.