July 25, 2011 at 2:42 pm
I am trying to get some information to help in planning a backup strategy for an application that my employer is working on. This application is going to be SQL based (obviously:-)) but we will not have physical control of the servers it will be installed on. The application is going to be sold to our clients, who will have their own servers, with SQL installed.
Anyways, the question.
I am currently working on a backup strategy we can suggest / assist in implementing for our clients. My current plan is to have a full DB backup taken once a day, either in the early AM or late PM, followed by hourly Transaction Log backups (possibly more often.) These backups will be run to an external hard drive attached to the SQL server. The clients will be responsible for switching out the backup drives daily. What I would like to know is, if a client switches the media while a Transaction Log backup is in progress, what happens to the log chain? Would the log chain be "unbroken" with the next hourly log backup, or would it be broken until after the next full DB backup?
Basically, what happens to the log chain if a transaction log backup fails?
Thank you,
Jason A.
July 25, 2011 at 3:06 pm
A failed backup will not break the log chain.
But why are you trying to force on your clients where they backup to? Let them decide for themselves, which would normally be to a local drive, and copy off the server later.
they won't thank you for imposing on them the task of 'switching out the backup drives' (I am not actually quite sure what you mean by that)
---------------------------------------------------------------------
July 26, 2011 at 1:09 pm
Thanks, that answered my question and took a load off my mind!
:w00t:
As for dictating to the clients where to back up to, we don't tell them where or how to backup, but we do make suggestions. Generally, our clients want us to tell them what we would suggest, as often they do not have their own IT people. If they do have techs, we'll tell the techs what needs to be backed up as far as our application goes, and leave it to them.
As for switching backup media (external USB hard drives, USB flash drives,) again the clients are used to this.
All of our clients are relatively small Doctors' offices, so the expense of a decent tape drive or "real" backup system simply won't fly.
Thank you again for answering my question!
Jason
July 26, 2011 at 1:14 pm
The bigger problem here is that unplugging a USB drive while it's still writing data can result in the drive being corrupted, losing all data on it.
Other than that, it's not an issue.
- 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
July 27, 2011 at 1:52 am
Why not take all the backups to the internal hard drive of the server and then have a scheduled task to copy them to the external USB drive in the small hours of the morning, when nobody is likely to be trying to unplug it?
July 27, 2011 at 7:45 am
I'd thought of doing something like that, but the biggest problem with it, is the possibility of data loss.
The application in question is an Electronic Health / Medical Records app, and the problem with waiting until the end of the day to copy the backup files, is the amount of possible data loss.
While we recommend a server with at least a RAID-1 array, customers can be notoriously stingy, and may get only 1 drive to save a couple hundred bucks. If the drive fails near the end of the day, having to tell a client that all the patient info entered throughout the day is lost, will result in a PO'ed client (this despite clients being repeatedly told that *THEY* are responsible for their data being backed up.)
I've come up with a method in a batch file (for the stingy clients who convince the higher ups here) for use with SQL Express, which will check for a file on the external media. If it doesn't find it, it waits a short bit, and tries again. It will do this 10 times (about 5 minutes,) then it writes to a log file on the local drive indicating it couldn't find the media.
I'm really trying to accomplish two things with this, and the things are almost mutually exclusive:
1. Be as Id10T proof as possible on the client side
2. Be as simple as possible so that non-SQLers can still set up the backup on a client system, with little to no assistance from me, and still have it work correctly
You can see the problem...
Jason A.
July 28, 2011 at 6:21 am
I understand your problem, but your clients really should do the following:
1) Back up SQL Server locally
2) Back up the SQL Backup location to another drive (network drive, tape drive, etc.)
If they have offsite backup services, such as Iron Mountain offers, then they don't need to worry about moving the backups because the process takes care of it automatically several times a day.
Please note: I use "don't need to worry about it" loosely. I'm paranoid DBA. I think everyone should worry about their backups every minute of every day and should regularly check with their vendors and verify their backups.
July 28, 2011 at 10:22 am
Brandie,
Your comment got me thinking, and caused me to (further) modify the backup batch file I'm creating. It now uses SQLCMD to backup the DB or Transaction log (2 batch files, one for the DB and one for TLogs) to a folder on the local drive, then it attempts to copy the backup to the external / removable media.
Thanks!
Jason A.
July 29, 2011 at 5:01 am
Glad I could help.
August 4, 2011 at 1:21 pm
jasona.work (7/26/2011)
Thanks, that answered my question and took a load off my mind!:w00t:
As for dictating to the clients where to back up to, we don't tell them where or how to backup, but we do make suggestions. Generally, our clients want us to tell them what we would suggest, as often they do not have their own IT people. If they do have techs, we'll tell the techs what needs to be backed up as far as our application goes, and leave it to them.
As for switching backup media (external USB hard drives, USB flash drives,) again the clients are used to this.
All of our clients are relatively small Doctors' offices, so the expense of a decent tape drive or "real" backup system simply won't fly.
Thank you again for answering my question!
Jason
Careful: if you recommend using USB drives as a backup medium and they fail (and they will, sooner or later) or -- worse -- walk out the door in a disgruntled employee's pocket, you may have HIPAA headaches, legal action, etc. The "recommendations" of an expert -- that's you -- carry great weight in a civil trial and may leave you liable for damages. I'm not a lawyer, but I've had 2 careers in technical fields requiring stringent loss prevention training and policies. Ironically, the less sophisticated your client is, the easier it may be for their lawyer to paint you as the expert in a legal action.
If your clients who maintain medical records don't want to pay for proper backups and they choose to put sensitive patient data at risk, don't inadvertently assume that responsibility with a good-natured but misguided recommendation for an inadequate backup plan.
Rich
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply