Database synchronization question

10 Nov 2006 4:42 PM

segis bata

Hello everyone,

I'm going to copy all the tables from one database in one server to other
database in another server. A couple of days later I will want to copy the
new (and updated) records (from those couple of days) instead of copying
everything again.

Plus, those tables don't have date fields for last update/insert date.

Is there a way to that w/o using replication/subscription?!? and if so,
please try to be as specific as possible or send me a link where I can go
through all the steps in the process

Thanks in advance,
SB-R

10 Nov 2006 4:54 PM

Razvan Socol

Hello,

You might use a column with the rowversion data type.

Razvan
PS. I know, I'm not being "as specific as possible"...

segis bata wrote:
Show quote

> Hello everyone,
>
> I'm going to copy all the tables from one database in one server to other
> database in another server. A couple of days later I will want to copy the
> new (and updated) records (from those couple of days) instead of copying
> everything again.
>
> Plus, those tables don't have date fields for last update/insert date.
>
> Is there a way to that w/o using replication/subscription?!? and if so,
> please try to be as specific as possible or send me a link where I can go
> through all the steps in the process
>
> Thanks in advance,
> SB-R

10 Nov 2006 5:20 PM

Arnie Rowland

Look in Books Online for "Log Shipping"

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

Show quote

"segis bata" <segisb***@hotmail.com> wrote in message
news:e2goucOBHHA.1220@TK2MSFTNGP04.phx.gbl...
> Hello everyone,
>
> I'm going to copy all the tables from one database in one server to other
> database in another server. A couple of days later I will want to copy the
> new (and updated) records (from those couple of days) instead of copying
> everything again.
>
> Plus, those tables don't have date fields for last update/insert date.
>
> Is there a way to that w/o using replication/subscription?!? and if so,
> please try to be as specific as possible or send me a link where I can go
> through all the steps in the process
>
> Thanks in advance,
> SB-R
>

11 Nov 2006 1:21 AM

Hilary Cotter

You will need to use a product like RedGate Data Compare, or create audit
tables which will write pk info, the type of DML (Insert, Update, Delete),
and the date the DML occurred. Then use triggers to write to these audit
tables when there is any modification occurring on the base tables.

Then you merely need to query these audit tables to get a list of rows which
have changed between syncs. Note that triggers on each table will increase
the latency of every transaction.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"segis bata" <segisb***@hotmail.com> wrote in message
news:e2goucOBHHA.1220@TK2MSFTNGP04.phx.gbl...
> Hello everyone,
>
> I'm going to copy all the tables from one database in one server to other
> database in another server. A couple of days later I will want to copy the
> new (and updated) records (from those couple of days) instead of copying
> everything again.
>
> Plus, those tables don't have date fields for last update/insert date.
>
> Is there a way to that w/o using replication/subscription?!? and if so,
> please try to be as specific as possible or send me a link where I can go
> through all the steps in the process
>
> Thanks in advance,
> SB-R
>