Sunday, April 7, 2013

SQL Server restore error: Specified cast is not valid. (SqlManagerUI)

 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.

25 comments:

  1. Yeah, It helped me a Lot. Thanx.
    I also got stuck at the same error and was not able to make out what was going on.

    ReplyDelete
  2. My thoughts were this was happening, thanks for confirming the suspicions.
    What was your end solution?

    ReplyDelete
    Replies
    1. Hi Joel, I ended up finding a SQL Server 2012 instance and restoring the backup there.

      Delete
  3. yes, you are right I have verified this.

    ReplyDelete
  4. Yes, this is SQL version issue.
    Thanks for updating here...God Bless you.

    ReplyDelete
  5. Thanks, I also had the same problem! One possible solution: use extract data. Drawback: only gets data out of the table and not stored procedures/views etc.

    ReplyDelete
  6. Sweet, helped me to solve my issue.

    ReplyDelete
  7. Helped me too, thanks!

    ReplyDelete
  8. Is there any possible way to restore the said backup (taken from SQL 2012) to SQL2008 only??

    ReplyDelete
    Replies
    1. There is no easy way to do this as you can't go backward in version. You can however, script the database objects(tables, views, stored procedures, data etc) in SQL Server 2012 instance and run the scripts on the instance with SQL Server 2008. Again, not a very clean solution but will get you started. Hope this helps.

      Delete
  9. Thanks! Kind regard from Austria!

    ReplyDelete
  10. thanks that was great help..

    ReplyDelete
  11. Nice blog , helped me to find solution.

    ReplyDelete
  12. I get the same error trying to restore a SQL 2000 .BAK to a SQL 2005 server? Shouldn't I be able to restore a SQL 2000 DB to SQL 2005 and have it auto-upgrade?

    ReplyDelete
    Replies
    1. Yes, you should be able to restore a SQL Server 2000 database to SQL Server 2005 instance. Did you try the other options mentioned in the post?

      Delete
  13. Yes, I also encountered the same issue. I thought, Microsoft should have mentioned this somewhere about this 2012 to 2008 R2 (Or I dont know am I missing this point :) ). Any how, after creating a DB and restoring it 2008 R2, it is really time wasting one to try with SQL 2012.

    ReplyDelete
  14. Excellent...helped me tons. Keep it up!

    ReplyDelete
  15. Thank You from Hungary, too - all my kudos are Yours :)

    ReplyDelete
  16. Great help thanks for showing with detailed explanation.Good work

    ReplyDelete
  17. how i get virsion of sql server 2012

    ReplyDelete
    Replies
    1. Are you trying to restore this to SQL Server 2008 or 2008 R2? If so, you will get the same error.

      Delete
  18. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    Pega Training In Chennai

    ReplyDelete