backup/restore of Tables ONLY (please)

[javelin]

I'm having difficulty searching for an answer to this challenge. Can
someone give me a clue on the right keywords to use to find a
discussion on this subject? All of the ones I saw appear to touch on
older versions. I'm working with SQL Server 2000, and need to backup
and restore only tables, because the full backup/restore appears to
mess up users/security, or something or another.

Thanks for the assist.

[Greg D. Moore \(Strider\)]

"javelin" <go*************@spamgourmet.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

I'm having difficulty searching for an answer to this challenge. Can
someone give me a clue on the right keywords to use to find a
discussion on this subject? All of the ones I saw appear to touch on
older versions. I'm working with SQL Server 2000, and need to backup
and restore only tables, because the full backup/restore appears to
mess up users/security, or something or another.

There really isn't. You backup and restore an entire database. Unless
you're using filegroups, etc.

However, most likely what you need is sp_change_users_login to reconcile the
users in the database with the logins on the server.

Check that out. It will most likely solve your problems.

>
Thanks for the assist.

[Erland Sommarskog]

javelin (go*************@spamgourmet.com) writes:

I'm having difficulty searching for an answer to this challenge. Can
someone give me a clue on the right keywords to use to find a
discussion on this subject? All of the ones I saw appear to touch on
older versions. I'm working with SQL Server 2000, and need to backup
and restore only tables, because the full backup/restore appears to
mess up users/security, or something or another.

You cannot backup and restore individual tables. SQL 6.5 had such a
feature, but thankfully this folly was dropped.

You can however backup and restore single filegroups.

But it is not likely to be the solution to your problem. BACKUP/RESTORE
as such does not mess up users, but if you restore a backup on a different
server, you lose the mapping between database users and server logins.
Obviously - the logins in two servers are likely to be different.

As Greg said, user sp_change_users_login to sort out the situation.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[javelin]

There are two requirements here, the first being the need to restore
tables to a different server than the backup originated from without
messing up mapping of server logins. The second is the need for a user
to work remotely on one single table without implementing fancy
database features, such as replication. Why not? Because the DBA is a
real "P.I.T.A.", and there's no way to convince him he's wrong! I did
discover one script to "generate INSERT statements from the existing
data" (found here: http://vyaskn.tripod.com/code.htm#inserts).
However, this one doesn't work well with tables with many columns, and
my table is definitely a "many-columned" table. If someone has
experience with fixing this script to be more flexible and suit my
needs, I could use it to have the remote user modify records in the
table and have the insert scripts generated from this code.

Thanks again for further advice.

On Jan 28, 4:34 am, Erland Sommarskog <[e-mail]:

javelin ([e-mail]) writes:

I'm having difficulty searching for an answer to this challenge. Can
someone give me a clue on the right keywords to use to find a
discussion on this subject? All of the ones I saw appear to touch on
older versions. I'm working with SQL Server 2000, and need to backup
and restore only tables, because the full backup/restore appears to
mess up users/security, or something or another.You cannot backup and restore individual tables. SQL 6.5 had such a

feature, but thankfully this folly was dropped.

You can however backup and restore single filegroups.

But it is not likely to be the solution to your problem. BACKUP/RESTORE
as such does not mess up users, but if you restore a backup on a different
server, you lose the mapping between database users and server logins.
Obviously - the logins in two servers are likely to be different.

As Greg said, user sp_change_users_login to sort out the situation.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

javelin (go*************@spamgourmet.com) writes:

There are two requirements here, the first being the need to restore
tables to a different server than the backup originated from without
messing up mapping of server logins.

As I said, this can be handled with sp_change_users_login, please
see Books Online for details.

The second is the need for a user to work remotely on one single table
without implementing fancy database features, such as replication.

Have you looked at bulk copy? There is also some Import/Export GUI
stuff in Enterprise Manager that I have never used myself.

Why not? Because the DBA is a real "P.I.T.A.", and there's no way to
convince him he's wrong!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

Since you rule out the traditional methods for BACKUP/RESTORE, then maybe
you can take a look at linked servers (that is if you have direct
connectivity between the two servers, which I assume you do because you
mention replication). You can set up a remote linked server and then
directly query and transfer the tables that you need. It can be easily
automated via a stored procedure that can be scheduled to run as a job.
Simple enough. Not the fastest approach but seems you are already looking at
generating INSERT statements to dump the data out...

Another alternative is to use a DTS package to transfer the tables. It can
be also fully automated, but assumes as above that you have connectivity
between the servers.

If you do not have direct connectivity then you can use a similar approach
to dumping with INSERT statements, but via DTS. In essence in the source
server you can create a DTS task to dump the table data to a text file, then
on the destination server import the data to a table using the reverse
process. The same scenario is doable using the BCP utility.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[javelin]

Plamen:

Thanks for the interesting advice. I am going to try and get direct
connectivity to the target server. If I can, the copy tables DTS
function is nice and straightforward. I don't know if the BCP utility
has been tried, but I'll give that a shot as well.

Thanks also to Erland Sommarskog for all your great advice. I'll be
experimenting with options for a few days.

Thanks again.

J
On Jan 29, 11:06 pm, "Plamen Ratchev" <[e-mail]:

Since you rule out the traditional methods for BACKUP/RESTORE, then maybe
you can take a look at linked servers (that is if you have direct
connectivity between the two servers, which I assume you do because you
mention replication). You can set up a remote linked server and then
directly query and transfer the tables that you need. It can be easily
automated via a stored procedure that can be scheduled to run as a job.
Simple enough. Not the fastest approach but seems you are already looking at
generating INSERT statements to dump the data out...

Another alternative is to use a DTS package to transfer the tables. It can
be also fully automated, but assumes as above that you have connectivity
between the servers.

If you do not have direct connectivity then you can use a similar approach
to dumping with INSERT statements, but via DTS. In essence in the source
server you can create a DTS task to dump the table data to a text file, then
on the destination server import the data to a table using the reverse
process. The same scenario is doable using the BCP utility.

HTH,

Plamen Ratchevhttp://www.SQLStudio.com