Sql-server – Copy table from one server to another including keys and indexes

copyscriptingsql serversql-server-2017table

I have been working on a small project for about two weeks.

We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live databases….kind of like a DIY mini-data warehouse.

We have three SQL Server 2008 remote servers and I am compiling the data onto one local server running three separate SQL Server Express 2017 instances.

The SQL Server Express databases are all set up, and I have successfully tested copying the tables required using several different methods including:

  • SSMS Import/Export Wizard

  • as well as scripting using Linked Servers from the new database:

    INSERT INTO dbo.table
  SELECT *
  FROM remoteserver.remotedatabase.dbo.remotetable

Both of these successfully copied tables and data BUT not keys, indexes, and other constraints.

I am only recently working in databases, so I am at a loss myself, and Google yielded the above suggestions for copying, but I cannot find anything that includes keys, indexes and other constraints when transferring tables BETWEEN servers.

Best Answer

Since you want to selectively script out certain tables, you can use dbatools to programatically script out tables, triggers, etc using Export-DbaScript.

Alternatively, you can also try Export-DbaDacPackage with -table parameter.

Since you are using linked server, my answer on Which one is more efficient: select from linked server or insert into linked server? will help as well.

Related Question