Question on 'load/replace" in SQL Server

I have to periodically overwrite selected tables in one environment
(Prod) with the contents of the same named tables from another
environment (Staging).
There are about 200 tables involved.
In SQL Server what would be the best way to do this and minimize
logging?
Thanks in advance.
Gerry"DataPro" <datapro01@.yahoo.com> wrote in message
news:1166795421.720680.18940@.42g2000cwt.googlegroups.com...
>I have to periodically overwrite selected tables in one environment
> (Prod) with the contents of the same named tables from another
> environment (Staging).
> There are about 200 tables involved.
> In SQL Server what would be the best way to do this and minimize
> logging?
"Best" is relative.
I'd suggest creating a DTS package and on the receiving server either put it
into simple mode (but break up your backup chain) or bulk-logged during the
load.
Having said that, I do a quarterly update with 3 very large tables and still
tweak it, but I use BCP on one and and bulk insert on the other and a batch
file. And also do log-backups between the 3 tables.
> Thanks in advance.
> Gerry
>|||Thanks much
Greg D. Moore (Strider) wrote:
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1166795421.720680.18940@.42g2000cwt.googlegroups.com...
> >I have to periodically overwrite selected tables in one environment
> > (Prod) with the contents of the same named tables from another
> > environment (Staging).
> >
> > There are about 200 tables involved.
> >
> > In SQL Server what would be the best way to do this and minimize
> > logging?
> "Best" is relative.
> I'd suggest creating a DTS package and on the receiving server either put it
> into simple mode (but break up your backup chain) or bulk-logged during the
> load.
> Having said that, I do a quarterly update with 3 very large tables and still
> tweak it, but I use BCP on one and and bulk insert on the other and a batch
> file. And also do log-backups between the 3 tables.
>
> >
> > Thanks in advance.
> > Gerry
> >

Question on load/replace" in SQL Server

I have to periodically overwrite selected tables in one environment (Prod) with the contents of the same named tables from another environment (Staging).

There are about 200 tables involved.

In SQL Server what would be the best way to do this and minimize logging?

Thanks in advance.
GerryI usually do this:

0. bcp out all data from source
1. disable FKs as appropriate in dest
2. truncate/delete from tables in dest
3. bcp in all data to dest
4. re-enable FKs in dest

You could probably also use DTS/SSIS for this, but I prefer bcp because I like the cmd line.

EDIT: one thing I forgot: step 2.1 should be to drop all indexes in dest, then at 4.1 recreate indexes. with indexes dropped the data will import much faster.

Question on Data out of sync

Greetings,
Here is the scenario: Server A is Prod, Server B is reporting or
history
Server A does Transactional replication to Server B. Server A gets
records purged at the given point of retention. So as you can tell
Server B will have much more data than Server A. Today, I get the
dreaded call of that data in server A does not seem to be at Server B.
We may have had some issues with replication a couple of weeks ago but
it appeared that they were corrected and replication has been working
fine since.
So , to my questions
Is there any way of pushing over all data on Server A to B to make
sure they are in sync without losing the older data as well?
Any good way of comparing the data in Server A to Server B without
having to go row by row through each table?
I am afraid that we may be out of synch and would like to recify it
without having to drop all replication, bcp out and then bcp back in
to get them back in sync. As you can imagine that is a major headache
and would be my last resort. Any and all ideas are welcome.
Thanks,
MM
You could also have a look at Innovartis DB Ghost at
http://www.innovartis.co.uk/ which can compare & synchronize your
database schema AND data (plus build, verify & version control). I have
found you can use SQL Enterprise Manager to generate scripts to quickly
drop & re-create replication and run DB Ghost to ship your missing rows.
John McGrath MCSE
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!