Are Differential / Transaction Log Backups Necessary

This is a follow-up to an earlier post (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24336568.html).

My 5.5 Gb SQL Server 2000 database grows at about 10 Mb per day. I want to implement a better backup strategy that reduces the amount of data loss at risk in the event of a failure. I'm not completely sure of the need for transactional backups. Our daily complete backup currently creates a file about 75 Mb in size.

Am I correct that transactional backups just reduce the size of the current transaction log? If I didn't perform any transactional backups, can't I still restore the database from the last complete backup and the current transaction log? Or is the transaction log trashed if there is a failure? Are there other benefits to backing up the transaction log?

Given our small transaction activity, having frequent differential backups may be sufficient, if we can live with a small loss of data. Recommendations?
LVL 1
ZekeLAAsked:
Who is Participating?
 
BrandonGalderisiCommented:
If you have a 5.5GB database, then your daily FULL backup would be close to that size unless it's all empty space in the database.

Transaction log backups backup the information in the transaction log and mark it as OK to be purged.  It does not actually shrink the log.  Transaction log backups, which are restored on top of FULL backup restores, allow point in time recovery.

Read this article where chapmandew (aka Tim Chapman) discusses the topic of transaction logs in detail.

http://blogs.techrepublic.com.com/datacenter/?p=448
0
 
ZekeLAAuthor Commented:
Sorry I didn't respond sooner. I've been sick and didn't see your reply.

We must have a lot of empty space because the full backup is consistently around 75 Mb. The backup was 5.5 Gb only when the full daily backups were temporarly suspended for a week and a half.

I read the article but my understanding of the transaction log file and its backups is still incomplete. I assume that transaction log file backups can be used to restore the database to a point in time since the last full backup. However, I don't know the following:

1) Can the transaction log itself be used to restore the database to a point in time or only transaction log backups? If the latter, then why wouldn't it be just as good to use differential backups instead of transactional ones?

2) How do transaction log backups compare in size to differential backups?

Thank you again for your response. I hope you can answer my follow-up questions.
0
 
BrandonGalderisiCommented:
1) Can the transaction log itself be used to restore the database to a point in time or only transaction log backups? If the latter, then why wouldn't it be just as good to use differential backups instead of transactional ones?

The transaction log backup can be used to restore to a point-in-time.


2) How do transaction log backups compare in size to differential backups?

Differential backups are differences from a full to the differential point.  Transaction log backups are a record of each and every transaction that occurs since the last time the transaction log was cleared.  There is no answer as to which is larger or not.  But most likely the T-Log ones will be larger.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ZekeLAAuthor Commented:
Now I understand. I thought differential backups were since the last complete or differential backup. I didn't realize it was only since the last complete backup. So by the end of the day, it would begin to be as expensive as a complete backup.  

That's why the transaction log backups are important. They won't be redundant like succesive differential backup's would.

Would you still recommend using differential backups or should I just rely on a mix of complete and transaction log backups?
0
 
BrandonGalderisiCommented:
If you need point in time restore capabilities, then FULL recovery mode with Full and Transaction log backups is your solution.
0
 
ZekeLAAuthor Commented:
Thanks. That's what I'll do.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.