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:
- First right click on the DB and select tasks
- There you will see the option to Generate Scripts, click on the same. Click next on the prompt message you
- You can either hand select the tables you want only or can generate the script for full database object
- On the next screen select advanced options
- From here you can either generate script for schema only, data only or both
- Click OK and proceed, the wizard will take few second and generate the script based on the options you selected.
- 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.
- 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.