In our small shop we do a complete database backup every day at midnight.
The transaction logs are backed up every 2 hours.

Suppose, that instead of backing up trans logs we did a differential backup
every 2 hours.
Won't  we be able to restore the database "identically" with either
approach?  Is there any reason to prefer one over the other?

TIA,

Bill

Transaction Log backups allow point-in-time restores, but Differentail
backups
do not. So, if you think you may need restore to an exact point of failure
you
should stick with all Transaction Log backups. For your scenario you might
want
to add a differential backup during the middle of the day (i.e. Noon) in
order to
minimize restore time.

I discuss this strategy in detail in a sample chapter I have listed on this
site:
www.SQLBook.com. The chapter is in .pdf format so you'll need to
download Acrobat Reader if you don't already have it.

Garth

In addition to point-in-time recover capability, doing transaction log
backups helps you manage your transaction log size by truncating the
inactive portion of the log after  a log backup.

With the full + diff method, the transaction log doesn't get truncated
unless you do it manually.

Carl Appellof
Legato Systems, Inc.

Bill,

Yet another advantage with tlog backup is that you can produce a tlog backup
from a crashed database if the mdf and logfiles are intact (if you loose an
ndf file).

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.