I am using SQL Server 2014 Management Studio installed in my system. And using linked server I have connected to a linked server.

Now here I need to copy whole database from linked server to my local SQL Server using linked server itself.

I am using a linked server instead of backup/restore because using linked server I can copy remote database.

Tool used: Microsoft SQL Server 2014 Management Studio.

  • "using linked server I can copy remote database.". There are many methods of copying a remote database and linked server is about the worst one – Nick.McDermaid Jan 6 '19 at 12:22

Backup/restore, the Copy Database Wizard, and Log Shipping are all potentially better choices than linked server copies.

See the following documentation:

| improve this answer | |

This is not a good solution to copy database using linked server at all! but if you are really really forced to do it (and you have try all the other ways) and you have a simple database with not a lot of data,
First You can generate script of your database:

Right-click on your database > Tasks > Generate Scrip > Choose Entire Database > Next

Advanced options: Type of data to script: Schema and data
Table\Views Options: Set all to true (except Data Compressions and Full-Text Indexes)
...(and set other options)

Change queries for your linked server using replace "[DatabaseName]." with "[linkedserver].[databaseName]."

I'm not really fan of this but this might be useful sometimes.

| improve this answer | |

You can use 'select into' construct to load data from tables on a linked server to your local server. I have just tested it and i works perfect ecpecially if you have few tables to get. Otherwise you will have to create some sort of a cursor to get the list of tables and data from them.

| improve this answer | |
  • 4
    Using this option might allow them to copy the database on a table-by-table basis but would not include any indexes, foreign keys, triggers, views, sps, functions, CLR modules etc, and would also introduce potential strain on the network, generate unnecessary transaction log on the target and create an unregulated, unmaintained copy of the database no-one knows about. For those reasons, it's a downvote from me, hope you understand : ) – wBob Mar 20 '17 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.