Do I really need third-party backups?

Question

I need to describe our situation in order to pose this question, so please bear with me...

We currently have Maintenance plans running via scheduled SQL jobs that create our backups. 

Previously, we'd only been doing one full backup nightly, and rarely ever ran test restores.  Now that the company is growing, and our database has more transactions, we need the backups to be a lot more current.

After changing our backup plan to run a couple Fulls twice a week, Differentials every 6 hours, and T-Log backups every 15 minutes... we discovered a problem involving some third party backup software:

Our network guy is taking a backup uf SQL Server via Veritas netbackup every night, and it is causing the chain between my Fulls/Diffs/T-Logs to be broken.  I queried the backup history tables to figure this out after unsuccessfully trying to restore to a point in time from our backups.  What's even worse, is I don't have access to the backups he takes.

My question is this: Do we really even need third party backups of SQL server to disk?  We are already getting the backups to disk via SQL jobs, plus the entire server is backed up to tape every night, so those SQL backups are stored there also.  The main database that I'm talking about is only about 30GB in size, so keeping several days worth of backups on disk is not a problem (e.g., we don't need compressions to save disk space...yet).

Thanks!

---

Clint

Answer 1

Based on the your situation, i don't see any reason to use third party backup software. As long you have right backup plan and recverable backups it should be fine.

Even if someone used the third party backups which sql server recognizes should not cuase the log sequence to break but differential backup are based on last backup dependent.

If your system guys don't want to give then you can ask them use the COPY_ONLY so that it will not break any sequence.

HTH

---

Mohammedu

Answer 2

Well, it depends. If you're not using SQL Server 2008 Enterprise Edition, some third party tools provide value such as backup compression which may help with disk space. You say it's not an issue now, but why make it a potential problem? It's harder to fix a problem once it is introduced. It does sound like you may not need something else, but I wouldn't 100% rule it out either. If the software you may purchase has value elsewhere in your environment, it may be a worthwhile investment. If not, don't bother.

---

Allan Hirt Blog: http://www.sqlha.com/blog Author: Pro SQL Server 2008 Failover Clustering (Apress - due out June, 2009)

Answer 3

sounds like you & your backup person need to have a conversation to decide what to do.  I think you should go with one or the other, your current situation is very broken, esp. if you cannot get easy access to the netbackup backups.

The netbackup plug-in for sql backups is quiet expensive too, so it sounds like the business may have squandered money on something it doesnt sound like it needs. 

I would stick with sql backups & make sure the netbackup backups happen shortly after your nightly job completes to pickup your backups on disk, you dont want to end up with a big gap between the two processes.

Answer 4

One thing I do often with my customers who have something like Veritas in - do your backups with SQL, but just have Veritas come by to that directory and grab the backups. Problem solved.

As Nick says, if you can't get easy access to the backups, in the event of a problem, it's you who will be responsible for getting SQL back up and running, not the Veritas guys. 

---

Allan Hirt Blog: http://www.sqlha.com/blog Author: Pro SQL Server 2008 Failover Clustering (Apress - due out June, 2009)

Answer 5

I concur with Alan Hirts recommendation.  The BAK is on disk and on tape, a belt and suspenders so the pants don't fall down.

Answer 6

Allan & J Langston,

How do you guys make sure backup is actually completed (AND verified!) before Veritas grab it?

We do have very tight backup window and try to run lots of stuff asynchronously.

 

Like need to backup 50+ databases:

Start database Backup DB #1..#5 [full of diff depending on a criteria]

 

On completion of any database backup:

1. run next database backup #6 and so on.

2. run verify on completed backup

 

On verify completion:

Signal to backup software (Veritas?) to grab it

 

I.e. it’s quite complex powershell script.

And although everything runs asynchronously – it sends only one email on completion with timing and errors (if any).

 

Took me a week to learn PS and almost a month to write and make it run smoothly. ;-)

What do you use in your case?

Answer 7

Thanks for all the recommendations! 

We resolved the issue shortly after I posted the topic.  We are backing up the DBs to disk via SQL Server jobs, then the entire server is being backed up to tape every night.  Currently, we are testing Litespeed as 3rd party backup utility.  However, it is only giving us around 12% compression and only saving us about 20 minutes in time - we think this is partly due to the fact that we have lots of images in our DB, and they don't compress very well.  At the moment, our DB is only around 30GB anyway...although i am interested in trying some other 3rd party stuff.

---

Clint

Answer 8

I know it has been awhile since this post, but I have a question on your response.  Is it possible for Veritas backups to breat the SQL backup chain?  I could be reading your response wrong, but it seems to imply that using anything other than COPY_ONLY will interfere with the SQL backups.  Is this true?
Thank you in advance for your time.

Answer 9

Hi,

You can run SQL server own backup and veritas SQL agent backup simultaneous that's now problem and should not break the backup chain. Problem comes when you have to do a restore because backups are located in two different media. Full backups are pretty easy but when restoring transation log backups from two different sources it becomes complicated. Transaction log backups should be restored in precise order. 

So best practise for my opinion is to use only one backup software either SQL backup or Veritas SQL agent both work well.

BR,

JoukoK