sql server 2008 restore with transaction log?

Question

I am developer so need your advice on how to plan for it

I am having sql server 2008.

I am going to through what they have in maintance wizard

And found that they have full, differential and transaction log.

So if i take one full back once a week then differential backup every day. Not sure how transaction log fit into this.

I assume sql server is saving transaction log some where so in case of failure I can restore from last differential backup coupled with full backup.

What i need to use transaction log on top of it? Where is transaction log saved?

I need this for application data loss issue, if in case some action made it delete some data so i need ability to go back point in time.

Answer 1

A Zero loss strategy require the involvement of replication in some form. Then you'll also need a failover server to account for replication time before the "slave" server is fully up to date.

Even with transaction log backups your still at risk for losing X data, where X is your transaction log backup interval.

Maybe edit your question?

Answer 2

You must backup your log too, explicitly. Schedule a job to backup the log at short intervals (15 minutes to an hour usually). When you do a recovery, you apply the full backup, then the newest differential and then all the log backup after the differential.

Only with log backup can you restore the database at a specific moment, using the 'WITH STOP AT'. See: How to: Restore to a Point in Time.

Also to recover from a crash, you backup the log tail then apply the recovery (full->differential->logs->tail) and hopefully occur no data loss at all.