October 15, 2014 at 4:54 am
Hello all
We currently take a log backup once an hour, a differential once a day and a full backup once a week. Previously, we did this locally on each SQL Server but we have since migrated to using Netbackup as that stores the backups off the SAN.
However, we encountered a problem whereby retrieving the numerous log backups would simply fail via the Netbackup Agent GUI and Symantec have been less than helpful in this regard. A colleague managed to create a PowerShell script as a workaround which works fine.
Moving forward, it has been proposed that we reduce the number of log backups to once a day on Netbackup and take copy_only log backups on the SQL Server. Enough storage will be provided to deal with log growth.
As the DBA here this concerns me greatly. Copy_only backups should only be used on an adhoc basis and in the event of corruption and/or backup failures I would feel reassured to have multiple (and recent!) log backups. The SAN team countered this by saying that in the event of a site failure, all those local log backups would be lost anyway (apart from what was swept off there by the nightly copy job) and that it would be better to have the backups off site.
They also said that since we had synchronous database mirroring, there would be no data loss (providing of course that the state of the mirrors were synchronised). I dont feel all that comfortable with this solution as your backup and DR strategy should be separate.
Can anyone help argue help provide convincing arguments from past experiences that I could use to push my case? I just dont feel like we should be compromising our backup/recovery strategy just because the silly GUI cant cope with retrieving thousands of log files. :crazy:
October 15, 2014 at 7:06 am
Copy only won't work, you'll end up with holes in your log chain.
See http://spaghettidba.com/2014/01/22/copy_only-backups-and-log-shipping/. It's log shipping, but the idea is the same.
As far as arguments to convince them:
Mirroring is not a sufficient data protection: if a user deletes all rows from a table, the mirror will synchronize and eventually contain no data to recover from.
Data loss does not only come from hw or sw failures, but also from user actions.
-- Gianluca Sartori
October 15, 2014 at 7:47 am
Agreed but we will still be taking log backups once a day to netbackup. If any rows were deleted from the primary database, we could take another log backup and then restore that to a previous point in time.
Thanks for the link - I'll take a look now.
October 23, 2014 at 5:27 am
One other option which I had used was -
1. Perform Backups (Weekly Full, Daily Differential, 4-hourly Log)
2. Write a script that runs every 4 hours (30 minutes after Log Backups) that zips\compresses .trn or .bak files using Winzip or 7-zip utility.
3. Let this get archived on SAN.
This should minimize the Size of the Backups taken to as much as 1\8th of their actual size. Additionally, it would safeguard the files within.
Hope this works for you.
October 24, 2014 at 10:05 am
Interesting post on corrupted remote backups on LinkedIn recently.
Jeff Moden's response is extremely interesting.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply