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. 6 27919
"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.
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
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 <esq...@sommarskog.sewrote:
javelin (google.1.jvm...@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, esq...@sommarskog.se
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
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
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
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" <Pla...@SQLStudio.comwrote:
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Thomas Bartkus |
last post by:
Is it safe to say that restoring the directory from a
backup would that restore all database data and structure? All usr/pwd and
GRANT info?
Thomas Bartkus
|
by: Vikrant |
last post by:
Friends,
I have read DB2/UDB 8.x 'RESTORE DATABASE Command', with my 'limited'
knowledge & skill. I think it should address my concern, but I want
advice/ opinion / experience and any care I...
|
by: Hardy |
last post by:
hi gurus, now I have to backup and restore a 8 T size db2 database. from two
s85 to two 670. the partitions,tablespaces of the db should be redesigned
then I plan to use redirected restore.
but...
|
by: Brice Avila |
last post by:
Hello,
IHAC running DB2 7.1.1.93 on Solaris 8. When he tried restoring this
instance from a different server (alternate client restore) he
encountered:
db2 => restore database dumbunny load...
|
by: Paul Aspinall |
last post by:
Does anyone have any sample code, or references to help when calling SQL DMO
to backup / restore DB via C#
Thanks
|
by: G. Dean Blake |
last post by:
How can I backup and restore a SQLServer DB from a .net VB program?
Thanks,
G
|
by: aka |
last post by:
Hi,
I'm actually working on the migration of a DB2 unicode db (V8.2 FP 15) from
a Sunfire machine with SunOS (64 bit, Big-endian) to an AIX 5.3 (64-bit,
Big-endian) server located in US. The...
|
by: mgpsivan |
last post by:
Hi,
How can we backup or restore a database with all its tables,procedures,views,triggers etc., when i tried using mysqldump command i'm able to backup the tables and procedures alone.But...
|
by: muddasirmunir |
last post by:
Can you please name me the best utility/software/aplication that can Backup/Restore SQL Server 2000 Databas in a proficient manner and easy to use for the end user.
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |