Replication vs Backup/Restore

[BillDHS]

We are looking at copying about 30 tables monthly between two SS 2000 instances.

As a rule of thumb which would be most efficient, a snapshot backup/restore of the source tables or a snapshot replication?

What would be some primary considerations in selecting a method?

 

[mrdenny]

Backup and restore would be the most effecent, and most flexable.

Here are some reasons not to choose replication (and I'm a big fan of replication).
If you add a table to the database you have to change the replication setup to add the table to the publication.
If you need to add or drop a column from a table in the publication you need to do it through the replication config part of EM, or via scripts.
You can't change column definations.
When the snapshot runs on the publisher it locks the tables it's using.

Here are some reasons to choose replication.
You can select only the tables you want.
You can schedule it to run when ever you want.
It has better recovery options if there is a failure for some reason.

Here are some reasons not to choose the backup/restore method.
You can only transfer the entire database. (FileGroup backups might help with this, but I'm not sure).
Any code to recover from a backup or restore failure must be written by hand. (Not all that hard, just a pain to have to do by hand).

Here are some reasonsto choose the backup/restore method.
You can schedule it to run when ever you want.
It does not lock the tables when running, so the backup can be done during the day if needed.
Any new tables, or schema schanges made to the source system are moved over when the new backup is restored to the destination system.
You don't have to use another method to change the table definations. ALTER TABLE still works just fine.

My vote in this case would be for the backup and restore method. I think it will give you less headaches in the long run.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLSister]

Is there some reason why you can't create a DTS package or job to simpley copy the new or revised data? Do you really need to completely change the db every time?

Replication is somewhat complicated, so for a once a month update, I probably wouldn't use it. I'd just DTS the information instead. I find replication to be more useful when you need to keep information in synch on almost a real time basis.

If you need the whole database copied, restoring backups is not a bad choice. At least you get quite practiced in restoration which is always good when a database actually needs restoration right now.

But if the users will be entering data in the second database, restoring a database from elsewhere will wipe out thes changes.

Whatever you choose, there is no substitute for doing backups. Replication can't substitute for a backup.



Questions about posting. See faq183-874