Solved

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

Posted on 2014-07-22
2
2,086 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

If you ever consider purchasing any Daossoft Software Products, DON'T expect any meaningful support - This article should convince you why!
When speed and performance are vital to revenue, companies must have complete confidence in their cloud environment.
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 configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

724 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