Solved

What is the difference between a log and differential backup for SQL backups with Symantec Backup Exec?

Posted on 2014-07-22
2
1,904 Views
Last Modified: 2014-07-25
I am currently running daily full backups of my SQL database with Backup Exec. I am trying to run log backups at noon but I get an error saying that the last backup job was not done by backup exec, which is not true. I found the tech article from Symantec that showed me how to do that and each time, backup exec is listed. So I thought of doing a differential backup.

Can someone tell me the difference between the log and differential backups?

With either, can I do backups every hour to keep a current backup?

Thanks,

Justin
0
Comment
Question by:JustinGSEIWI
2 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 250 total points
ID: 40213777
Hi Justin,

The trans log and diff backups are different.

Transaction log backups give you point-in-time recovery. Differentials do not. A differential is only the full backup plus whatever changes have happened since the full backup and up to the differential. They do not include any changes after the differential. The transaction log backup does.

Both are reliant on a Full Backup.
In the disaster recovery situation here is how everything hangs together.

So.
Lets say on a Sunday at 3 PM you do a full backup.
Lets say each weekday you do a Differential Backup at 3 am.
Lets say each hour on a weekday you do a log backup between 6 am and 9pm (working hours).

If you have a database failure at 12:30 pm on Monday you will need to in order...

1. Restore Sunday 3PM full,
2. Restore Monday 3AM differential.
3 Every log backup from 6 am till midday Monday.

The amount of loss of data is midday to 12:30 Monday

Personally, I use Full backups nightly and hourly log backups during office hours.

Hope that helps

Thanks
T
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 40215294
Differential backups are not the way to go (in my opinion).

Your plan of nightly Full backups plus hourly Log backups would be the best option...

Transaction log backups are important for the health of your transaction log, as well as providing "point in time" recovery.

You still need to run Transaction log backups under Full Recovery database model (which is the  default recovery model) even if you run differential.

I would be inclined to set up a scheduled job within SQL Server and use SQL Server to manage the transaction log backup.

Use backup exec to then take the backup files and make a secure (disk) copy of those files where they can be readily and easily accessed if needed.

Have a read of differential backups and note the need to still run transaction log backups : http://technet.microsoft.com/en-us/library/ms181092(v=sql.105).aspx  (it also has links to transaction log backups and point in time recovery)

Also, I have written an Article about managing the Transaction log : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html and applies to any DB from 2005 onwards.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

830 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