November 4, 2007 at 7:14 pm
Hi:
I'm pretty new to SQL and have some questions re: SQL backups.
If you're using Backup Exec to backup the SQL server, is there a reason to use the SQL server's built-in backup? How do people normally do this if they have a third party backup software.
Thank you.
November 4, 2007 at 7:23 pm
Which 3rd party utility do you have? Some take the place of the native backup, Some don't.
The native backup utility works great and is nearly flawless. Some of the third party solutions add value with compression or encryption.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 4, 2007 at 7:26 pm
Hi,
Most of them prefer SQL server native backup, since you dont need to pay additional cost for taking backup.
If you are moving for third party backup tool you need to spend additional money and they provide compression, encrypt on backup so the time taken and the space used will be reduced. I have mentioned some of the backup tool
SQL Backup -- Backup tool from Red-gate
Litespeed [/url]-- Backup tool from Quest
Regards..Vidhya Sagar
SQL-Articles
November 4, 2007 at 9:55 pm
Thanks for your replies.
We use Veritas Backup Exec.
So this would replace the native SQL backup then? Is this what the others would normally do if they have a third party backup software, they'd just use this instead using it in addition to the native SQL backup?
November 4, 2007 at 10:41 pm
If you are having third party software, then you can rely on this since it has some features compared to native backup.
Regards..Vidhya Sagar
SQL-Articles
November 5, 2007 at 6:55 am
Veritas centralizes many of your backups, but are you going to tape or disk with Veritas? I've had problems before with Veritas and Seagate's drivers in restoring databases, so be sure that you practice this and you're sure it works 95% of the time.
The third parties I've been comfortable with are Quest's Litespeed and Red Gate's SQL Backup (I work for Red Gate).
You might not have a choice, but even with Veritas, I might use Native backups. If you're going to tape directly, I'd run a disk backup as well every night and keep it handy. Never know what being able to pull back data from disk would help.
Be sure you know how to restore a backup to a different database. Handy for recovering partial databases.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 5, 2007 at 7:00 am
We use backup exec, and I can't believe I am going to say this....
Oh I can't say it(how many issues we have had). I recommend it. Sorry if you have been a DBA long enought fate is just one of those things that you don't fool with.
I recover my DB's almost every other day to one system or another. So they are tested quite well. We use it to give us easy access to the storage system, with easy restore to other systems. We also use it for all of our backups (file, exchange,etc).
November 5, 2007 at 8:12 am
I have been using Veritas for about the last 7 years. We always use the 'native' SQL BACKUP commands to get the files to disk. Then Veritas can pick them up at almost any time. Veritas is a strong enterprise solution with lots of options. There is one option I 'strongly' suggest staying away from their SQL backup agent - in my past with it I have experienced almost a 50% failure rate.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 5, 2007 at 8:35 am
We use Veritas Backup Exec as well and have also had problems with the SQL Server Agent. The best solution is to do SQL Server native backups to disk and have those backed up to tape using Veritas Backup Exec.
November 5, 2007 at 9:41 pm
Thank you for all your repiles.
Backing up using native SQL to a disk then picking them up using Backup Exec sounds like a good option.
Backup Exec has this thing called Continuous Protection for Microsoft SQL Server Databases.
Have any of you used this option?
November 5, 2007 at 11:43 pm
Giraffe (don't think I've ever called anyone by that name but here goes) -
One thing not addressed so far is that backup exec, etc. are often used to take daily/weekly backups of databases straight to tape (sometime near line but usually tape) - which is great for files that don't change every time a new transaction hits the database or where losing a day or week of work isn't too big a deal.
That said, the single most important thing to determine is just how much data your company/superiors are willing to lose should your database server go down hard - if your databases are fairly static and you can lose a days worth of data, backup exec, etc. may be just fine. However, it's much more likely that the loss of hours/days of transaction is completely unacceptable you're going to need to look at a different strategy - probably a dump & sweep (e.g. backup to disk, sweep to tape) that includes full & transactional backups (maybe even differentials)... in which case backup exec or other straight to tape solutions are probably not the answer.
Joe
November 6, 2007 at 6:28 am
To parrot that which has been noted already, the SQL Agent for Veritas, in addition to being a substantial expense if you have several servers, is not reliable. Also, intermingling backups to disk via native or third party tools and agent mediated backups to tape will lead to difficulties when a restore requires a tape full/diff in addition to disk backups.
Spend the SQL Agent money on disk, use native or third party backups to disk and backup those disks using Veritas. Be certain to retain the disk files for several (3 - 5) days for ‘accident recovery’ and the ‘I need a copy on the QA box’ requests. The tapes can be safely moved off-site for disaster recovery and seldom, if ever, needed for actual day-to-day recovery operations. Using the on-site daily rotational tapes has always provided the files in the event the disk version had been deleted.
NEVER believe that three days data loss is acceptable. Unless it is something that can be rebuilt from data sources from which ‘no loss’ is acceptable (e.g. a data warehouse’s operational data store) and only then if it can be rebuilt in approximately the same time as a restore from disk operation could be completed should you consider less than daily backups. The moment you fall for the ‘n days data loss is acceptable’ fallacy will be the day the data is mission critical.
HTH,
Art
November 6, 2007 at 7:07 am
Completely agree with the notes about spending the $$ on disk above. Let your manager know that the sales guy is pushing a product that's not reliable enough for databases. Get your money back.
It's unlikely you'll go back more than a day or two anyway. usually just too much data has changed, but it is important that you get back up immediately and tape usually doesn't work well. Have your manager sit by and tap his foot while you try to restore from tape so he/she gets an idea of how long tape can be. It's not fun.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 6, 2007 at 7:28 am
I also tend to prefer to make disk-based native backups first then backup those to tape. It is always less hassle to restore from disk than tape. In the past I have opted for raid0 for the disk volume for maximum performance and considered redundancy unnecessary since it is backed up to tape later anyway. However, having just had a failure of a raid0 backup volume and the hassle of having no backups while the disk volume is rebuilt, I have now had to re-think this. I would now recommend redundancy even for the disk-based backup volume.
November 6, 2007 at 8:27 am
i use Veritas Netbackup Enterprise with a 7 year old tape library. we have around 30 SQL servers so using native SQL backup is too much time to manage.
i use the SQL Agent and only recently have had trouble restoring data on older servers because of a timeout issue between creating the 70GB database files and the job waiting for it to finish
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply