Copying one or two tables from a different server
-
Could somebody help me out, and apologies if I've posted this in the wrong forum.
I've been using SQL Server 2000 on a daily basis from when it was launched and SQL server 7 before that, I've now currently started on 2005. So far it's been ok but I'm rather upset by the lack of little options like not being able to create a view by right clicking on a table name, I don't really want to save a view I just want to invoke the query designer as I'm no good at remembering the correct syntax for things. Also the management studio is painfully slow compared to something like
At the moment all I want to do is occasionally copy one or more tables between instances of SQL Server, it's not always the same tables, in SQL Server 2000 this was no problem, I just invoked the Copy Data wizard and set my options. However in SQL Server 2005 I can't seem to find any options for retaining the table permissions, when you have lots of users connected to the table this is a problem if the permissions are not copies over.
I realize I can probably delete the contents of the remote table then copy over the contents of the source table but that’s less than ideal and more time consuming. What I really want to do is delete the remote table and replace it with the new table, indexes, keys and permissions, the lot.
Does anyone know of a suitable resource or give me a step by step run though of how to do this as I'm lost after weeks of searching.
Thanks
Ed
-
There's a jumble of info in your post, perhaps by going through each issue you can get to a simple solution.
Your problem is that you want a simple procedure for one-off tasks. By nature it is easier to set something up once and make it repeatable.
The Copy Database wizard will handle the whole database and associated objects. In 2005 the new schema is pretty essential to the security model so you may want to copy this as well as the user accounts.
Using a linked server will give you a permanent connect to the source server, then you can get access to any tables you might want to duplicate on your destination server and the issue becomes inter database rather than inter server.
There are other functions that could be useful here, snapshots, replication to name a few.
To get clarity can you say why and when you want to carry out this operation?
-
Hi Joseph,
Thanks for the reply and here goes a little more info.
Every once in a while (could be every few days, every day or once a year) I would like to copy a table from my production server to my development server. This could be any table in any of my databases. Traditionally I'd just use the 'Import Data' task in Enterprise manager and select the table I want to copy as an object and then tick the 'copy-object level permissions' item in the 'Advanced Copy Options' window.
As is very much an ad hoc scenario it isn't worth the disk space or time creating a package to execute in the future as I may end up with hundreds of packages in a month.
So, I simply complete the wizard and execute it immediately.
In SQL Server 2005 there is a copy data wizard which does much the same but it's lacking that one option to copy the object level permissions, what I'm after is a solution to this rather than having to use a hammer to crack a nut like creating a package in SSIS and then have it stored for months to come when I know it would only be used once.
Any ideas?
Thanks
Ed
-
I think following will work for you.
Run this script on your Server
USE [Master]
GO
EXEC sp_addlinkedserver @server='YourRemoteServerName'
EXEC sp_addlinkedsrvlogin 'YourRemoteServerName', 'false', 'YourServerUser', 'RemoteServerUser', 'RemoteServerPassword'
/*
For Example
EXEC
sp_addlinkedsrvlogin 'RemoteServer', 'false', 'sa', 'sa', 'dba'
*/
select * from [RemoteServer].DatabaseName.dbo.TableName
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply