Hello Folks,

OK. I can defend myself on PL/SQL but I am not much of a SQL Server
aficionado and therefore it just seems odd to me on how to move data
from one DB to another using DTS.

Here is the situation -

I have multiple DB through out the country on SQL Server 2000 + SP4
running on Windows 2003 Server SP1.

The application performs a replication between databases but due to
network issues, server problems, human errors some of the databases
have fallen behind. Our Databases are almost upto 11GB of data alone
(others are at 6GB), not including any attached files (which have
reached upwards of 390GB).

Anyway, I am trying to figure out a way to append data from one SQL
database to another. My plan was to copy the most upto date Database
place it on a laptop and fly to the sites with the databases which have
fallen sorely behind (6GB of data). Also perform the same with other
Databases until, hopefully, the difference between the databases is
minimal and replication solve that within eight hours of transmission.

The problem is that I cannot just replace the database at the other
location because it houses data from that location which might not have
been replicated to other sites (yes I know - Nightmare). Anway, we
have several sites like this and I was trying to use DTS to create a
package that would do this. Hmm, well I obviously don't understand DTS
well enough or it just simply cannot perform the task I tried because
it has failed on the owner of the DB.

Side Note: Each Database was build by someone different and hence use
an Administrator to run SQL while others use the System User. Some
have only NT Authentication while other have both NT Authentication and
sa authentication. To add insult to injury no one has documented what
the sa passwords are throughout the country (you don't even want to
know). In addition, all the SQL Servers reside outside the Windows
Domain and in their own individual Workgroups.

In addition, there are several copies of the DB which are used on
laptops which go out to customer sites and that data is normally easily
enough extracted using a Tool from the application. The problem is
that someone has not kept up with their duties and we do not know how
freaquently this process had been done. Using the application tool
would take upto 24 to transfer and it requires a human to be present in
the event of any errors or conflicts.

Any ideas would be welcomed. I'm already doing so much other stuff
that I have very little time to research on how to do this on SQL
(although I'm still trying to do some research). I'm considering some
comparison tools as well as migration tools or something else because
if I had to write the SQL it would just be a nightmare (300 Tables on
the Schema).

Is there anything native in SQL that would simplfy this process or am I
looking for some mythological creature?

Thank you,
Ed

Hello Folks,

Not that I expected anyone to reply this quickly. However, this
morning I've had a breakthrough with DTS.

Instead of using the Export I used the Import. Although technically
the same I suppose I was looking at it with fresh eyes this morning and
I saw something (or read something) that I had not seen (or ignored)
previously.

I came up to the window on the DTS Wizard which has the option to
create objects, copy objects, and copy data. Previously I
(errouneously) thought that it would need to create, however playing
around I un-selected the Create Objects and the Copy Objects options
which are set by Default.

I only left the Copy Data and selected the append data. The reason is
because the Schema on all the Databases is the same and hence all the
objects and tables already exists. Those which might be unique to one
database (like a view) are irrelevant at other locations since they are
only used for reports and the like at local sites.

Anyway, it will take a while for all the data to transfer over and I
still have to ensure the data transfered correctly. However, it is
still in the process of copying the data from one SQL Server to the
other. The reason I was having an error was due to the option to copy
all objects which included the ASPNET user which is obviously only in
existance on one location but will fail against the other Server
because the name is techincally different (hostname\ASPNET).

I'll keep you all posted and let you know if it actually worked. I'll
have to go back on my head now.

Thanks,
Ed


medina4me (AT) netscape (DOT) net wrote:

Me Again,

OK, it did work but I have questions.

I performed a test and copied a full Database to one which had nothing.
I didn't copy all the objects over just the Data and the Data seems to
be all there. However, there is a difference between the two
Databases. The Full DB is approximately 11GB while the one I
transfered the Data is approximately 8GB.

While there are four more stored procedures in the larger one and about
16 more Views I don't believe those things could take upto 3GB of
space. Is there a way that I can compare the objects between the two
native to SQL Server that would let me know what could be causing the
discrepancy in the *.mdf file size?

In my understanding of Views unless you are actively using a view it
normally doesn't occupy any space except the size of the script. When
you are using a view it then creates a temporary table which later
drops when it is done. Is my understanding incorrect? I could see how
different views could take upto 3GB.

Thanks,
Ed