Specified Cast is not Valid : How to Backup Db from MSSQL 2012 to 2008 ?

Errors can be annoying at times, specially when the error message has a little or no specifics available about the error. I have encountered once such error message recently while I was trying to restore a DB backup from a higher version of SQL to a lower version.

I am no DB expert but I did everything right, the way I knew, right click the DB , select tasks, select Backup and save the backup file. The operation was successful. The issue happened when I tried to restore the same to a lower version of MSSQL. When I tried to do that I get the following error

Specified cast is not valid(Sql Manager UI)

The error message has no specifics and I had no clue, what I was doing wrong. Apparently we can’t restore DB backup from MSSQL version 2012 to a lower version. You can escalate the error further if you want to know the details, Pinal Dave has a good blog post here about the same.

Now what if you want to restore the DB from 2012 to a lower version ? Well, you can make use of generate script option. Lets me walk you through the process:

  1. First right click on the DB and select tasks1
  2. There you will see the option to Generate Scripts, click on the same. Click next on the prompt message you
  3. You can either hand select the tables you want only or can generate the script for full database object
  4. On the next screen select advanced options
  5. From here you can either generate script for schema only, data only or both
  6. Click OK and proceed, the wizard will take few second and generate the script based on the options you selected.
  7. Come back to your older version of MSSQL, create a blank database with the desired name and run the script you generated on the command window.
  8. Ie it you are good to go!

Hope this helped, let me know any of you guys know other alternate solutions. I would love to hear about those.

Over 6 years of experience in Software Development and Front End Development. Experience in Coding, Testing, Design on Microsoft Platform Technologies, HTML/CSS, scripting etc. Area of exposure includes software development using Microsoft Asp NET, C# and SQL Server for web.

Leave a Reply

Next ArticleGet File Size in a File Upload Control Before Uploading for Validation