?
Solved

Are Differential / Transaction Log Backups Necessary

Posted on 2009-04-22
6
Medium Priority
?
341 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:ZekeLA
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 24207347
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
 
LVL 1

Author Comment

by:ZekeLA
ID: 24233654
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 2000 total points
ID: 24241896
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:ZekeLA
ID: 24242644
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 2000 total points
ID: 24244821
If you need point in time restore capabilities, then FULL recovery mode with Full and Transaction log backups is your solution.
0
 
LVL 1

Author Closing Comment

by:ZekeLA
ID: 31575138
Thanks. That's what I'll do.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question