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

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
JustinGSEIWIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony303Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.