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)
- Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
- Backup media is corrupted.
Solution of 1st one is easy – restore it on same or higher version. 2nd 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)
24 Comments. Leave new
Pinal, Thanks a lot for enlightening up with two more options in “Error:: Specified cast is not valid”. I too had encountered this error before and by googling I got to know this issue was related to SQL version compatibility. But today after reading your blog I came to knew about corruption and password protected backups scnearios and using Restore HeaderOnly option.. Thanks again for the information..
Aashish – I also learned it recently.
Hi Pinal ji,
Log shipping out of sync, while i tried to take differential backup from primary server i got the below error. total db size is 580+ gb,full backup taken 2 months back.
Error :
Cannot perform a differential backup for database “xxxx”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Could you please help me to solve this error,and possible reason as well.
(if i take full backup and will restore at DR with standby option will work i hope but due to lack of space at DR i am searching other alternate solution) please correct me if i am wrong.
Please reply me ASAP
Thanks in advance…
looks like someone changed recovery model. Take a fresh full backup.
I too faced below error….
“Error : Cannot perform a differential backup for database “xxxx”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.”
quick question..
Did you take a FULL Backup of that database before taking differential backup?? If NOT, take a FULL backup and then run differential backup..
The definition of differential backup means that saving only the difference in the data since the last full backup.
As per my knowledge, you must have to take full backup before running differential backup….
You are the man, Pinal.
Thanks Rob!
I also agree that you are the man. I have learned so much from you.
Thanks Michelle.
specified cast is not valid. (sqlmanagerui) when restore database ,, what can i do sir???
above process not success please help me
How can i restore the dataabase header only from sql server 2012 to sql server2008R2.
Nice blogs this is so helpful for me.
Pinal, your blog is always extremely helpful. Many thanks.
Pinal sir,Nice blog and find perfect solution.Thanks for sharing your knowldge.
Hi I am facing with the restoring a database Says ” back up media set is not completed family count 2 misssing sequence 1 please help me with this.
It means that when you took the backup, you have spitted them in multiple files. You need to go back to source server and find what was the other media as a part of media set.
Pinal, Perfect blog. All solutions are clear. Good job! :)
Thanks Pinal! Just had this issue myself. I always look for your posts to read first when I Google SQL issues. You are the best!
I am glad that google is showing my posts in the results.
very useful since 2015 :)
I am getting the error when connects to a CNAME on SQL 2017 version. SSMS version 17.6
it connects just fine with hostname\instancename, but errors out when connects to cname\instance name. Any idea?
TITLE: Microsoft SQL Server Management Studio
——————————
Specified cast is not valid. (ObjectExplorer)
——————————
BUTTONS:
OK
——————————