copying SQL server DB table from one server to another

Question

I am trying to copy a table from a live remote server to my local development machine. I tried:

select * 
into mmm 
from [sqlb8.webcontrolcenter.com].[1photo].[kelraie].[pix]

But got this error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'sqlb8.webcontrolcenter.com' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

How do i link the servers? Or is there an easier way to do this? Thanks Khaled

Chris Gessler · Accepted Answer · 2012-02-25 00:21:57Z

1

Have you tried Backup - Restore? You can also use Export, but Export won't script any Primary or Foreign keys.

And don't forget about BCP http://msdn.microsoft.com/en-us/library/ms162802.aspx

To link servers, use the stored proc sp_addlinkedserver http://msdn.microsoft.com/en-us/library/ms190479.aspx

answered Feb 25, 2012 at 0:21

Chris Gessler

23.2k8 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Bertrand Over a year ago

If the OP just wants one table it might be because it's 10MB but the backup might be 100GB. So backup/restore might not be practical.

Vinnie · Accepted Answer · 2012-02-25 01:11:40Z

1

The linked server can use other provider drivers on your system to allow you to query directly from within SQL Server from that source. In your case, it may look like you're trying to connect to a web based version of SQL, which might not be as simple as setting up a linked server. Depending on how you're accessing the data, you may have to create a backup or use another method to pass the credentials along to be able to access the source.

answered Feb 25, 2012 at 1:11

Vinnie

3,9471 gold badge28 silver badges30 bronze badges

Comments

djangofan · Accepted Answer · 2012-02-25 01:16:14Z

0

If you have SQL Server 2008 Express or a later version, it comes with a "Import/Export" wizard that will do it.

answered Feb 25, 2012 at 1:16

djangofan

29.9k62 gold badges211 silver badges308 bronze badges

Collectives™ on Stack Overflow

copying SQL server DB table from one server to another

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related