Backup SQL 2017 to SQL 2012 using SQL 2016 a middle man

Question

Is it possible to take a SQL 2017 database backup and restore to SQL 2012 if I first restore the SQL 2017 backup file to a SQL 2016 server first. Once restored on the SQL 2016 server, can I perform a backup of SQL 2016 and restore the database on SQL 2012 server? I realize that I can't perform a backup/restore from SQL 2017 to SQL 2012 because it is more than 2 versions old, but by using SQL 2016 as a go between, can this work?

I realize that this may seem odd to go backwards, but it would only be used in a Disaster Relief situation if a new rollout fails which would force us to go back to the original application that is currently running in SQL 2012.

Any insight around this is very helpful.

Thanks,
Doug

Answer 1

Hi @Doug DeVoe ,

The most commonly used method of SQL Server database migration is backup&restore. An error that the version of the source database is higher than the version of the target database will be reported, so the backup&restore method cannot be used.

In order to avoid the problem of database version, you can follow the steps below:

1. Generate a script to create an object in the source database, and then apply this script in the target database.
   On the source database side: right-click the source database="Tasks="Generate Script=". According to the wizard to produce the corresponding script, the script contains SQL statements to create database, table, view, sp, etc.;
   Note: The version of the SQL statement produced must not be higher than the version of the target database (the version of the SQL statement is set in the "Advanced" on the "Set Scripting Options" page).
   Then execute the produced SQL statement on the target database side.
2. Use import/export to import the data in the source database to the target database.

For more details, please refer to this blog: https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I realize that I can't perform a backup/restore from SQL 2017 to SQL 2012 because it is more than 2 versions old,

You can not restore a backup from a newer to an older version of SQL Server and it doesn't matter how many versions are between; it's simply not possible.