Sql-server – Copy whole database from SQL Server to SQL Server using linked server

A few quick ideas:

The local server initiates a connection to the remote server, runs a query and fetches appropriate rows from the remote server. This all takes time. Worse, if you trace such activity with SQL Profiler, you may find that rows are fetched from the remote server to the local server one at a time, or in very small batches. This is very inefficient, and the more rows it fetches the worse and worse it gets.

If all data is local, none of that happens and the data can be retrieved much more quickly, especially if it is sitting in the data cache.

Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

For very large remote result sets (in terms of data size), you may run into issues where SQL needs to grow data or log files to accommodate the data. 7 seconds probably doesn't qualify, since it probably isn't bringing back much data.

Lastly, if the remote query is to a production system and the local query is to a dev or test system, load on the production server (which does not exist on the dev server) may cause blocking of the remote query. This would also slow down the observed performance of the remote query.

Copying ONLY data does not copy the keys and indices, since these are metadata describing how to treat the data.

The following article suggests that you create a SQL Server 2008 instance, backup the SQL Server 2000 database and restore to the SQL Server 2008 database. Then backup the 2008 database and restore it to SQL Server 2014. (Be sure to check Service Pack requirements).

• Upgrade path: SQL Server 2000 SP4 to SQL Server 2008
• Upgrade path: SQL Server 2008 SP3 or later to SQL Server 2014

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

Then you should have the SQL Server 2000 database upgraded to SQL Server 2014.

Since you want Keys and Indices as well, you need the metadata. Another approach is to script out all the objects (tables, views, procedures, etc) from your SQL Server 2000 database. Then execute the create script on your 2014 server. (Fixing any problems that you find.) After that transfer the data via linked server (per Mark Sinkinson), SSIS packages, BCP, etc.