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.

65 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. Thanks for sharing
    http://www.metaforumtechnologies.com/software-testing-training-in-chennai

    ReplyDelete
  19. I would really like to read some personal experiences like the way, you've explained through the above article. I'm glad for your achievements and would probably like to see much more in the near future. Thanks for share.
    python course in pune
    python course in chennai
    python course in Bangalore

    ReplyDelete
  20. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us
    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    angularjs-Training in pune

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in btm

    ReplyDelete
  21. I need quick help please,
    I do like you in the pictures now what I can do to result to restore the backup please answer me

    ReplyDelete
  22. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing.. Believe me I did wrote an post about tutorials for beginners with reference of your blog. 
    rpa training in bangalore
    rpa training in pune
    rpa online training
    best rpa training in bangalore

    ReplyDelete
  23. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.
    I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    Java training in Chennai
    Java training in Bangalore
    Java online training
    Java training in Pune

    ReplyDelete
  24. This is quite educational arrange. It has famous breeding about what I rarity to vouch.
    Colossal proverb. This trumpet is a famous tone to nab to troths. Congratulations on a career well achieved.
    This arrange is synchronous s informative impolite festivity to pity. I appreciated what you ok extremely here.


    Selenium interview questions and answers
    Selenium Online training
    Selenium training in Pune
    selenium training in USA
    selenium training in chennai

    ReplyDelete
  25. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete
  26. Awesome Blog, Very useful information and also easily understandable.Thanks for sharing this information. Keep Blogging!!
    machine learning course

    ReplyDelete
  27. Attend The Data Analytics Course Bangalore From ExcelR. Practical Data Analytics Course Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Course Bangalore.
    ExcelR Data Analytics Course Bangalore

    ReplyDelete
  28. This excellent website certainly has all of the info I needed about this subject and didn’t know who to ask.
    Techno

    ReplyDelete
  29. thank you for sharing this wonderful blog.

    BEST ANGULAR JS TRAINING IN CHENNAI WITH PLACEMENT

    https://www.acte.in/angular-js-training-in-chennai
    https://www.acte.in/angular-js-training-in-annanagar
    https://www.acte.in/angular-js-training-in-omr
    https://www.acte.in/angular-js-training-in-porur
    https://www.acte.in/angular-js-training-in-tambaram
    https://www.acte.in/angular-js-training-in-velachery

    ReplyDelete
  30. This post is so interactive and informative.keep update more information...
    Android Training in Anna Nagar
    Android Training in Chennai

    ReplyDelete
  31. Thanks for sharing this attractive article . Turkey visa US is a visa which is made specifically for citizens of the USA and this exactly the type of article I was looking for.


    ReplyDelete
  32. Congratulations on your article, it was very helpful and successful. 4f2d25d7a29b2941a8e85c17ae74cbf3
    sms onay
    numara onay
    website kurma

    ReplyDelete
  33. Thank you for your explanation, very good content. 77d05032de25af38c526962f99a58195
    define dedektörü

    ReplyDelete
  34. This is an awesome post.Really very informative and creative contents.you can also visit: Testing Titans: Mastering Software Testing Principles

    ReplyDelete