SQL Server Backup – Backup 2019 and Restore to 2017

backuprestoresql-server-2017sql-server-2019ssms

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?

Best Answer

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.

Related Question