January 7, 2013 at 7:27 am
I'm looking after a whole bunch of SQL Servers, both 2005 and 2008, some full and some simple recovery databases with the largest database at 126Gb and some others over 30Gb.
Current backup strategy which I've set up is nightly full backups (using sql agent and native SQL Server backups) to disk with transaction log backups at intervals during the day (frequency depends on the application). After each full or log backup the backup file is copied to a fileshare on another physical server and both the SQL server and the backup fileshare are copied to tape nightly using BackupExec but only copying the backup files not letting it do its own agent backup.
There is a proposal afoot to have BackupExec do all the backups with its agent (which they've already bought) straight to tape and not do any SQL Server native backups. This is to reduce the disk space used. I'm not at all keen on this but am trying to find up-to-date arguments to justify my position. I've read the previous discussion on this forum but that was nearly four years ago and things change so what does the forum think?
One downer I've read is that BackupExec would not support restoring to drive letters that were different from the original backup location - as I regularly restore live to test or to copy databases elsewhere this is important to us. Is it still true with current versions?
Also if a database is added to the SQL Server does BackupExec automatically pick it up and back it up or does it have to be added to the job by someone? Since I use Ola Hallengren's backup routine in many cases, the native backup routine picks up most new databases:-)
I'm very happy with the status quo but need to argue to keep it. Please could you help me collect the justification?
January 7, 2013 at 9:04 am
The fact that their own FAQ on using their product to back up SQL Server databases has incorrect information in it isn't promising to me:
If you use SQL's own backup utility, a full backup will backup the database, then truncate the log files.
(http://www.backupexecfaq.com/backing-up-microsoft-sql-server/)
That's wrong. It will do no such thing.
Most likely, if you purchase their add-on for SQL backups, it will be fine and you'll get what you need from it. Not sure it will actually give you any real advantage, but it should work. I'd do a try-before-you-buy on a test server. Run some backups, restore them, see how it goes. If it works okay, then go for it.
I currently have backups to disk via SQL Agent using a custom script I wrote. Then the backups are copied to tape. Full backups weekly, diff nightly, log hourly (on appropriate databases only, of course). Some variations, like the model system database only gets a full backup after I change something in it. Managed by DDL triggers and the normal backup script. It changes so infrequently that there's no real point to backing it up every night. I also have a few read-only databases where I have a full backup after any changes (usually annual), and then don't bother the rest of the time. But most of the databases it's weekly full, nightly diff, hourly log.
Since the backups are swept to tape every day, there's no "you end up with double the files". And SQL 2008 and beyond can compress the backups, so it's a less critical issue even if they were doubled up. But they aren't, so it's moot.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 10:02 am
I am not a fan of direct-to-tape database backups using the Veritas, ArcServer or other SQL Server backup agents for several reasons:
1. The tape drive is usually a shared resource, so it may not be able to backup a database at the point in time that I want it backed up. Also, a tape drive can only backup one database at a time, and I may need to backup multiple databases at the same time.
2. Direct to tape backups are especially unsuitable for transaction log backups, since they should run very often, like every 15 minutes, and it is unlikely that the tape drive will be available that often.
3. Restore requires the use of the tape backup software, and the last thing I want is an NT backup admin restoring a database.
4. I like to have the current backup on disk, because it is the one that I am most likely to need to restore from, and I usually need it in a hurry. Tape has a higher media failure rate than disk, so there is a higher safety factor in a restore from disk. Also, if the tape drive is in use, I may have to wait until its current operation is complete to start a restore.
I prefer to backup the database and transaction logs to disk with a regular SQL Server backup, and then have the backup files saved off to tape on a daily basis. It requires more disk, but I think that the safety of the database has to be the first consideration.
January 8, 2013 at 4:15 am
It's been a while since I used the SQL addon for Backup Exec (think it was back in version 12 or thereabouts), but I never found it to work very well. My preferred method is always to back up using the built-in tools to disk, then back up that disk location using Backup Exec.
April 16, 2013 at 2:08 am
I wonder if anyone else has any more comments about this?
My manager has just mentioned that we need to decide if the SA should do the backups using BackupExec or if we'll continue to do them using SQL Agent jobs. I might be a bit of a control freak, which means I want to keep doing them... but that isn't really a business case.
I'd appreciate any input that might either make me more comfortable with BackupExec doing the backups, or suggest some reasons to indulge my controlling nature make my case to my manager.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply