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,831 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

831 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