Restore to earlier version

pduonms

Can a backup be restored to an earlier SQL version, 2016 to 2008R2?

Eddie D

Hi, thank you for your forum.

The simple answer here is no.  SQL server does not allow a user to restore a backup created on a later version of SQL to be restored on an earlier version.

However, please take a look at SQL Compare and SQL Data Compare which are Redgate's comparison tools.  You will be able to compare the 2016 database to the 2008 R2  database and view the differences.
 
Also you will be able to deploy the differences from the 2016 database to the 2008 R2, providing you are not deploying features that were added in SQL 2012 and 2016 that the SQL 2008 R2 will not support.

SQL Compare will compare the database object and as the name suggests, SQL Data Compare will compare the rows of data held in the database tables.

Many Thanks
Eddie 

pduonms

We got SQL Compare and its fantastic.

So by using SQL Data Compare we can actually compare data and deploy diffrences as eazy as diffrences in the database structure in SQL Compare?

Alex B

Hi @pduonms,

Yes indeed, with SQL Data Compare you set the comparison key (which defaults to a primary or unique key if it exists, otherwise you need to set a combination of columns that define a unique row) and it will show you the rows that contain differences, those that exist in the source and those that exist only in the target just like in SQL Compare.

Kind regards,
Alex