Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mchohan
Starting Member
39 Posts |
 Posted - 2009-04-03 : 05:50:01
|
| Hi,I will be doing a snapshot replication from a LiveDB to a CopyDB everynight.I then need to import the new customers from the Copy to a bespoke database each night too. However i need to flag those customers imported so they they are not imported again the following night, only the new ones are.How can I flag this on the Copy database as 'imported' without having it overwritten completely by the live snapshot. Writing the flag back to the live DB is not an option.thanks. |
|
|
zzzbla
Starting Member
13 Posts |
Posted - 2009-04-03 : 06:36:48
|
| Hi,Snapshot replication simply copies complete tables (schema + data) from the publisher to the subscribers. If you wish to transfer only changes, you should consider using transactional replication. You can change the Customers table schema on the subscriber and add a new column to use for flagging data you copy to that other database (remember to set a default value for the new column). You could also use a rowversion/timestamp or identity column on the subcriber table and keep a control table in which you keep record of which rows were copied (if you use rowversion, you would also catch updates to existing rows.Hope that helps,S. Neumann |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-03 : 10:15:16
|
| Then why you need to replicate for LiveDB. Just backup/restore to COPYDB everynight and use SSIS (Lookup Transformation) to find only new ones to other database? |
|
|
|
mchohan
Starting Member
39 Posts |
Posted - 2009-04-03 : 11:23:21
|
| Yea, i was thinking about simply doing a backup/restore. However, I'll need to bakup on Server A and then Restore on server B each night. Can this be done via script? |
|
|
|
zzzbla
Starting Member
13 Posts |
Posted - 2009-04-03 : 12:23:45
|
| Backup/restore or snapshot replication can be a good idea if your database is small, otherwise you should consider incremental copying of data using transactional replication or log shipping. Backup/restore can be scripted and scheduled with SQL Agent.Hope that helps,S. Neumann |
|
|
|
|
|
|
Snapshot Replication