2

I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: http://learnsqlwithbru.com/2012/01/11/setting-database-compatibility-level-for-sql-server-2012/

However, the restore still doesn't work and I get "specified cast is invalid". Is there any way I can fix this problem?

Note: I don't want to use a Generate scripts option.

| improve this question | |
  • 5
    You CANNOT under any circumstance restore a newer backup to an older instance. Just CANNOT be done. No tool, no trick, no hack - it just isn't technically doable. No way. Period. The database compatibility level doesn't help here. Your only viable option is using database scripts - or a database diff tool like Red-Gate SQL Compare to achieve this. – marc_s May 1 '13 at 4:53
  • 1
    You can use SSIS to pump data between the two servers, assuming they can see each other – 孔夫子 May 1 '13 at 5:05
8

Compatibility level only controls what features may or may not be used on a given database and has no bearing on the restoration of DBs from different versions.

As pointed out by others, it can't be done by any means other than scripts or, if you can get it to work, the built in Copy Database tool. However that uses SSIS and I have never managed to get it to work successfully as it requires a whole heap of fiddly security apparatus, and usually consumes far more time than it's worth.

If you truly don't want to use scripting, then pretty much your only option is to upgrade your target DB server to 2012, or install a new one (using developer or Express editions is an option provided that a) It isn't a production instance if you use Developer Edition and b) the limitations of Express don't affect you).

| improve this answer | |
  • Just been reminded of this thanks to an upvote and just for the record, the import and export tool in SQL 2012 is MUCH better than in previous versions and works just like the old SQL 2000 version did. Why it took so long for MS to get this back to being the simple and powerful tool it should be I do not know, unless I was missing something obvious for the last three versions (entirely possible). – Steve Pettifer Apr 3 '14 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.