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.