Posted by Erland Sommarskog on 01/28/07 10:34

javelin (google.1.jvmail@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, esquel@sommarskog.se

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

[Back to original message]