Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-07-22
2
Medium Priority
?
2,587 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 1000 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Dropbox has a relatively new feature called Smart Sync.  This feature allows Dropbox Professional (not plus) and Dropbox Business (if enabled) users to store information in Dropbox WITHOUT storing any files on their computer.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

572 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