DB Restore and Trans Log vs.Differential Backup question 
Author Message
 DB Restore and Trans Log vs.Differential Backup question
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



Thu, 03 Oct 2002 03:00:00 GMT
 DB Restore and Trans Log vs.Differential Backup question

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



Thu, 03 Oct 2002 03:00:00 GMT
 DB Restore and Trans Log vs.Differential Backup question

Quote:
> 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?

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.



Thu, 03 Oct 2002 03:00:00 GMT
 DB Restore and Trans Log vs.Differential Backup question
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.


Quote:
> 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



Fri, 04 Oct 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Restoring differential backup to standby DB?

2. SQL 7.0 - Differential vs. Log backups

3. Please help...Backup vs Transaction log vs Restore is so important

4. Please help...Backup vs Transaction log vs Restore is so important

5. SQL Trainee: DB and Trans Log Backups

6. Master DB trans log backup error

7. Differential Backup and Log Backup

8. Removal Trans.logs backup files fails - (Backup Maintenance Plan)

9. Log Shipping/Trans Log Backups

10. SQL 6.5 DB Restoring vs SQL 7.0 DB Restoring

11. differential backup and restore

12. How to take Differential Backup and Restore it...?


 
Powered by phpBB® Forum Software