I have only 1 table in SqlServer 2019 that I want to backup and restore into SqlServer 2017 in different server.
To SqlServer 2019 I have access to server and with SSMS but to SqlServer2017 I have access only with SSMS
How can I do that?
Add a comment
|
3 Answers
A backup is overkill for only one table and I'm not sure what compatibility issues you can run into restoring to a lower version of SQL Server (since backups occur at the database level).
Instead you should look to just copy the data between servers. There are a multitude of ways and tools out there to help you do this, but I've always found the SQL Examiner Suite simple enough for such a task. They offer a fully functional 30 day free trial, so you can utilize it as you need.
Alternatively you can setup a Linked Server between the two instances and then can easily pull the data across the linked server to fill the table on your other instance, as needed.
Finally, you can also export the data to a flat file from your SQL Server 2019 instance, and import that flat file using the same tool in SSMS on your SQL Server 2017 instance.
Outside of those options, you can also use BCP as recommended by Bob Klimes, or you can generate scripts with the data as this article walks you through.
Here's the key steps you need, to include the data with the schema scripts when you use the Generate Scripts feature:
(On the Set Scripting Options page) Click on the 'Advanced' button.
Scroll down and select 'Schema and Data' from the 'Types of data to script' dropdown.
-
1additional info learn.microsoft.com/en-us/sql/relational-databases/… Commented Mar 1, 2021 at 17:36
-
@BobKlimes Yup, thanks for linking BCP as an option as well, that's also valid. There's probably another half-dozen or so ways you can do this for just a single table as well OP, hopefully any of the ones we suggested so far work out.– J.D.Commented Mar 1, 2021 at 17:39
-
@J.D. I guess that
export the data to a flat fileis the most simple and easy way to do that Commented Mar 1, 2021 at 19:20 -
@yfr24493AzzrggAcom Probably, they each have their pros and cons, so it's subjective. But exporting and importing is relatively simple. The first solution using SQL Examiner Suite is very easy too if you only need to do this one time– J.D.Commented Mar 1, 2021 at 19:22
-
@yfr24493AzzrggAcom I also updated my answer with additional options after reading your comments on the other answer. It is possible to script both the schema and the data together with the Generate Scripts feature in SSMS, as described in the walkthrough I linked.– J.D.Commented Mar 1, 2021 at 19:29
You generally do not do it. It is not possible.
SQL Server is not forward compatible. 2017 does not know how to handle a 2019 backup. PERIOD - no discussion, it will tell you so.
Which is why it is an utterly stupid idea no to use the same versions over the whole application development team cycle - you make copy operations complex, you run into compatibility issues.
If you really have to do bit, export a generation script and run that - backup is NOT possibly with a restore on an older sql server version.
-
I have to do it... generation script will only build that scheme, not copy the content of table Commented Mar 1, 2021 at 17:19
-
Well, it does not matter what you "have to do" - it is not supported. PERIOD. Pigs can not fly, cars are not submarines and SQL Server will not restore a backup made with a later version.– TomTomCommented Mar 1, 2021 at 17:23
-
@TomTom Agree with the Sql Server bit but this will challenge "cars are not submarines", even back in 1983 abc.net.au/news/2023-07-22/… youtu.be/rNLL0fo9apc?si=CqjkzqkLD_XFnco0– Jason SCommented Sep 14, 2023 at 2:05
You can generate the scripts from SSMS and include the data. See this. This is what I would do if I needed a one time move of the schema and the data.
