24

A database that was originally from SQL Server 2008, was restored into SQL Server 2012. A backup from SQL Server 2012 was made and I am trying to restore it on my local SQL Server 2008 Express. However I get an error 'Specified cast is not valid' (SQLManagerUI).

I have generated an SQL Script from 2012 and set it so that it will generate with compatibility to SQL Server 2008. However it is a large sql file, around 700mb.

I recall before that I had tried to run a script of that size before on my local SQLExpress and also got an error.

Is there a way I can get a "large" database from SQL Server 2012 into SQL Server 2008 Express?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stormwild
  • 2,855
  • 2
  • 31
  • 38
  • 5
    You can **never** go "back" in terms of versions in SQL Server. If you backed up your database in a 2012 version, you cannot restore that backup to a 2008 version. Just won't work - no tricks or ways around it, either :-( – marc_s May 25 '12 at 11:09
  • Oh, no. Thanks Marc. I'll try to see if I can run the generated sql script to get a copy of the database into my local sql express. – stormwild May 25 '12 at 11:25
  • 1
    See [Why SQL Server cannot restore backup to earlier version](http://www.mytechmantra.com/LearnSQLServer/Unable_to_Restore_Database_From_Backup.html) – marc_s May 25 '12 at 11:47
  • 2
    Also see my answer here: http://stackoverflow.com/questions/10303791/create-database-in-sql-server-2012-script-and-use-in-2008 – Aaron Bertrand May 25 '12 at 12:39
  • Even if the compatibility mode is set to sql 2005? – Juan Dec 22 '13 at 22:39
  • I had the exact same issue and ended up using **Redgate Sql Packager**, building an .exe file that I could deploy and run on the server. It installed the entire database as expected. – VilladsR Jan 08 '14 at 09:36

3 Answers3

22

Thanks to Marc and Aaron for providing the answers.

The quick answer is no, it's not possible to restore a backup file from a higher version to a lower version of SQL Server.

A work around would be to generate the scripts to create the database.

You can target the script generation to a lower version.

Please see comments above for more information.

Links:

Why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

Create Database in SQL Server 2012, Script and Use in 2008?

Community
  • 1
  • 1
stormwild
  • 2,855
  • 2
  • 31
  • 38
3

Couple things to add that might be helpful to folks

When scripting large databases using scripting wizard in SSMS it’s really important to check the execution order and be willing to re arrange it manually. Older versions of SSMS had this problem because they (probably) relied on sp_depends that has a bug.

What I’ve found really useful in such cases are tools like ApexSQL Diff that you can use to read database backups and generate scripts that are in correct execution order.

SQL Server database backup restore on lower version

One thing that none of the methods will catch is the thing Aaron mentioned about using functions specific to higher version.

Community
  • 1
  • 1
Jaycob Read
  • 336
  • 2
  • 3
1

A better option than using the SSMS scripting wizard is to use a similar tool available on Codeplex called SQL Database Migration Wizard - http://sqlazuremw.codeplex.com/releases/view/32334. You want the latest version v4.x to work with SQL Server 2012.

The tool is originally intended to be used for migrating databases between SQL Server and Azure. However the tool works just as well as between SQL Server and SQL Server. The trick is to set SQL Server rather than Azure as the target in the advanced options.

The reason this is a better option than the SSMS scripting wizard is that it uses BCP for the data transfer rather than TSQL and so is much more efficient.

Brian Towers
  • 340
  • 12
  • 15