SQLTeam.com | Weblogs | Forums

Dumb question, but do I need a backup to get to a date if I have all transaction logs?


#1

I am almost embarrassed to ask but in all my years of MsSql anytime I needed to get to a point in time I'd restore backups and then all transactions up to my date I needed. However if 10 days ago I have a full backup, then have trans backups the next 4 days, then full backup, then trans backups up to current.

Could I take my backup from 10 days ago and apply all transactions and get to current, or do I need the backup prior to the transactions to be restored before transaction restores?

The reason for this question is I need to transmit data from about 50 locations to a central place and recreate the 50 databases at the central place, but I&: rather just ship the transactions backups over weekly?

Any better methods besides implementing full transaction shipping which is a juggernaut of a task have 50 locations dumping into 1 simultaneously.


#2

Yes - as long as you have an unbroken chain of log backups from any full backup you can restore that full backup and all log backups up to a point in time.

Transaction log backups are not like differentials - where they are tied to a specific full backup.


#3

If the data needs to be refreshed on a weekly basis, have you considered using Replication? The initial snapshot(s) would create and populate the tables at the target. You would need to decide if you wanted Snapshot Replication, and have a new snapshot scheduled weekly, or if you wanted Transactional Replication, where you could keep real-time or near real-time data at the central repository.