Db refresh - Need input on strategy

Hi Folks,

The Need : Refresh a part of local database daily from remote server.

Assumption : All updates in remote are updated in local db as well.

Need inputs on the type of strategy

1) Take full backup of remote, refresh on local

( Downside for us is Network and disk space )

2) DTS ( refresh only the objects required )

Looks good to us but does it take care of my assumption ?? Your suggestions welcome .. I may be wrong

3) replication ( Dont want it implement on the already complicated sceanrio ... so I'll pass)

4) Standy databases ( ? Any help on this)

5) Any other

Thanks so much,

Warm Regards,
Ranjit.

-------------------
The best moments of my life are often things I get paid forI am not too clear on the assumption part. Do you mean all updates on the remote server as of a point in time should be reflected in the local (refreshed) server? Or that a process is updating both outside the refresh process?

If this is a pure Disaster Recovery purpose, you can look into things like Log Shipping (if you have SQL 2000 Enterprise edition), database mirroring (SQL 2005 SP1 only), or a third party hardware or software replication solution. If this is a reporting/snapshot solution, most of the third party replication schemes may not work for you, since the replicated copy will likely have to be offline.|||Thanks Matt.. Yes , it seems we need all updates on the remote server ( at a particular point of time ) to be captured and also reflected in the local (refreshed)server ..

This is actually more of a particular project requirement for cube builds ..

Also, at this point of time , we are on SQL 2000 EE for this requirement .. And logshipping ..Well that's an option I can snoop around for more details .. Any help/pointers ??

Thanks again.

Warm Regards,
Ranjit.

-------------------
The best moments of my life are often things I get paid for|||Logshipping may make the transaction log backups (and more importantly restores) of the remote system a little difficult, so you will need to have a test system to play with. One of the downsides of logshipping is you will get the entire database, and all changes, which it sounds like you do not need.

Snapshot Replication is probably out, due to the size of the database you are looking at (if you are building cubes from it, it must be big).

Transactional Replication can give you either scheduled, or (near) continuous updates on the subscriber system. You can have the distribution job halted during the cube build.

Third party replication schemes (hardware or software) would work in this scenario. You would have to cut the replication and fire up the database, before the cube build, but that can probably be scripted easily enough.

If you have no control over the remote server, the transactional replication of key tables will probably be the easiest politcally.