Possible to take a backup of SQL Server 2012 DB and restore on 2008?

Question

I want to take a backup of SQL 2012 and restore on 2008, is that possible ?

I have this options which I can't change on DB. Thank you

Answer 1

No. Not possible. Compatibility level talks of how the SQL behaves - not the database internal format.

I suggest you start standardizing your servers. THe requirement points to some highly disputable decisions - either to run outdated reserver machines, or to upgrade a production machine without planning.

Answer 2

SQL Server does not allow downgrading to an older version

You could use the Tasks ->Export wizard in 2012 instance, create an empty database and use Import wizard in 2008 instance, however this may hot be an ideal solution since the Export/Import wizard will not restore all objects (it will copy tables but not indexes, keys etc.)

For more possible options, and an explanation you can refer to this article: Why Can't I Restore a Database to an Older Version of SQL Server?

Options suggested in the article are:

-Upgrade the older version of SQL Server to be at the same level as the newer SQL Server.

-Script out the objects from the newer database and then usp a bcp process to extract the data from the newer database and import it into the older database.

-Use the SQL Server Import and Export Wizard to build an SSIS package to move the data (it will move the data only).

-Build a custom SSIS package to do the data move.

-Use replication to move the data from the newer database to the older one.

-Use some other form of scripting, such as with PowerShell, to keep the databases in sync.

Another option is to use third party tools, ApexSQL Diff and ApexSQL Data Diff

You can read detailed explanation of the process in this article:Restoring SQL Server database backup to an older version of SQL Server

Other third party tools may help as well

Hope this helps

Disclaimer: I work for ApexSQL as a Support Engineer