14,494 questions
Restore Backup from 2017 to Sql 2014 Sql
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(150.4, 17%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
pdsqsql
431
Reputation points
Hello,
I have Sql Server 2017 installed previously and now i uninstall it and installed Sql 2014, I need to restore Backup taken from Sql Server 2017 but as per my understanding, i can't restore into Sql server 2014 because backup taken from sql server 2017.
Could you please guide me is it any way i can restore it?
Thank You!
SQL Server Other
{count} votes
-
pdsqsql 431 Reputation points
2020-09-12T03:58:20.78+00:00 Thanks everyone for your tips and suggestions.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(160, 61%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESC%3C/text%3E%3C/svg%3E)
Stacy Clark 31 Reputation points2020-12-11T10:39:59.533+00:00 You can restore the backup to same or higher version, vice-versa is not possible,
Sign in to comment
7 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(240, 3%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EG%3C/text%3E%3C/svg%3E)
Guoxiong 8,206 Reputation points2020-09-09T20:04:10.96+00:00 -
pdsqsql 431 Reputation points
2020-09-09T20:16:34.733+00:00 Thanks Yuan.
I saw that earlier blog but i have already uninstalled the Sql 2017 so i can't generate it, only i have backup. -
Guoxiong 8,206 Reputation points
2020-09-09T20:32:01.15+00:00 I do not think that you can directly restore that database to the SQL Server 2014 using the backup files generated from the SQL Server 2017. You can re-install the SQL Server 2017 with a different named instance on the same server and then restore that database. And then you can generate the scripts, move data ....
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator2020-09-09T20:48:47.433+00:00 Without SQL 2017 (or SQL 2019) that backup is of no use to you.
-
pdsqsql 431 Reputation points
2020-09-10T00:15:04.983+00:00 Thanks Yuan.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff2020-09-10T07:26:36.877+00:00 Hi @pdsqsql ,
Did the answers above help you?
If you find any post in the thread is helpful, you could kindly accept it as answer. This would benefit the community, and encourage the community member to keep working on your issues.Best Regards,
Amelia -
pdsqsql 431 Reputation points
2020-09-10T13:25:02.67+00:00 Thanks Amelia for the follow up, post kind of helpful but looking another alternative as generating script and loading eating up all the memory and consuming all the resources.
Sign in to comment -
-
Edwin M Sarmiento 261 Reputation points2020-09-09T20:29:32.587+00:00 Re-install SQL Server 2017, restore the backup you've taken and follow the solutions provided in that article
-
pdsqsql 431 Reputation points
2020-09-09T21:49:19.443+00:00 I am agreed with you all but i was thinking if i can use any trick apart from installing that backup into Sql 2017 and regenerate the script.
If it has more data, will it work as i think script will be big and having issue to load? -
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
2020-09-09T21:57:22.623+00:00 If the database uses features not available on SQL 2014, you will obviously get problems.
-
pdsqsql 431 Reputation points
2020-09-09T22:34:44.177+00:00 Thanks Erland.
I don't think so our DB using any features which might affect.
But our DB size is Few GB so it will be a issue loading in editor, right? -
Guoxiong 8,206 Reputation points
2020-09-09T23:59:18.003+00:00 For the big sizes of the tables, you can use the SSIS package to transfer data to the destination tables.
Sign in to comment -
-
Olaf Helper 47,436 Reputation points2020-09-10T13:42:23.04+00:00 Script out the database design without data and create a new database on 2014 with that script.
Then copy over the data with Import/Export wizard or via linked server or with an SSIS package.-
pdsqsql 431 Reputation points
2020-09-10T18:31:57.517+00:00 Thanks Olaf.
Actually i have created DB with script with all the objects.
you mean to copy the data using export/Import or other methods but i don't have that original database on sql 2017, only i have backup. -
Guoxiong 8,206 Reputation points
2020-09-10T19:11:42.707+00:00 That is why you need to restore the database to the SQL 2017 or later version from your backup and then you are able to transfer data.
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,771 Reputation points2020-09-10T15:01:00.62+00:00 You can't. There is no way to restore a SQL Server backup to a lower version.
-
pdsqsql 431 Reputation points
2020-09-10T15:19:06.877+00:00 Thanks Tom. I am also aware about that and confirming with all of you guys have comments and suggestions but only thinking if any way i can make it little easy if we have any other way as currently BIG script consuming all the resources.
-
Tom Phillips 17,771 Reputation points
2020-09-10T19:35:48.443+00:00 There is no way to restore a backup to a previous version.
As others have said, you would need to export and import the data into the lower version.
However, rather than do any of that, I would highly suggest you simply upgrade your SQL 2014 to SQL 2017. SQL 2014 is already out of mainstream support.
-
Olaf Helper 47,436 Reputation points
2020-09-11T06:36:18.863+00:00 There is really no other easy way then the option I discribed.
Sign in to comment -
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
2020-09-10T21:01:31.113+00:00 Step one of your operation is to restore that backup on SQL 2017 (or SQL 2019). There is no way around that.
Then there are several alternatives to transfer schema and data to SQL 2014. Being the kind of person I am, I would build an empty database from the scripts I have in version control, since that is my master. Then I would run some SELECT queries to generate BCP commands to bulk out the data and load. Before I load, I would disable all foreign keys and re-enable them afterwards. I might also do the same with all non-clustered indexes to speed up the load.
If you don't have any version control, you can use the Generate Scripts feature in SSMS. Just beware that by default it does not script exactly everything, so click the Advanced button and make sure that you include everything you want.
That wizard also includes an option to script data, but I would only use that for a really small database, say below 50 MB.
You don't have to use BCP if you are not comfortable with that method. The Import/Export wizard or some SSIS package may fit you better.