• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

database backups of audit related DB

¿Can anyone provide a layman's interpretation and view on the following issue (ideally keep your responses basic to a level of someone who has never managed backups at DB level). We have an audit related SQL express database (but the RDBMS platform is irrelevant its the concepts I am interested in) that basically logs entries about actions taken in one of our ERP apps. The data can be useful for investigations when trying to determine what activity a user took in the application.

It has recently come to our attention the audit database isn't being regularly backed up. The DBA surprisingly doesn't seem to concerned, he stated the audit logs go back 12 months, and they don't overwrite themselves, so will continue to log to the database as long as required, so he could provide logs going back to October 2012.For audit log type databases, what is the risk in not doing backups of the DB? For what scenarios does a backup of this kind of data save you? I am struggling to challenge their verdict of this being a low risk issue, it doesn't sound good practice to me.
3 Solutions
Simple, just think of these scenarios:

1) The disk subsystem that holds the logs just failed and there is no way to recover it.  What is the impact because you have NO audit logs at all and no way to recover them?

2) The disk subsystem that holds the databases just failed.  What is the impact because you don't have a backup and therefore no way to recover the database.

3) The disk subsystems that holds both the logs and the databases just failed?  How do you recover?

Normally, at least in my experience, the "most recent" audit logs should be backed up as well as the databases.
DavidSenior Oracle Database AdministratorCommented:
What makes a good practice can vary widely.  High level, an analyst uncovers what is the business requirement.   Then, does the solution mitigate the risk of a failure, to an acceptable level.

Right off the bat, your scenario suggests confusion as to what's the issue.  Whether I use the audit features built into the Oracle product, or I record all inserts or updates against a table (into another table) -- auditing does not generally store the records into a log file that is external to the database.  

Let's say there's a need to examine all data changes on a given table, by a given user, back on June 20, 2013.  A query against an audit record table is straightforward, and may take a few seconds.  Can your DBA retrieve the same information, using his or her OS files?  Certainly not as quickly.

Another problem is if the user has been updating personally identifiable information (PII).  There is absolutely no way I would accept a log of those changes to exist in clear text outside of the database.

The DBA seems to be saying that he/she has a year of data.  Great.  Tomorrow there's a fire on the floor above you.  Your computer closet has major water damage, and the building is quarantined.  What good are the log files then?

Databases, more than operating systems, are meant to be redundant for that and similar reasons.  Back to your point, without database backups THAT ARE RECOVERABLE, log files just aren't a viable alternative.
Jim P.Commented:
I'm always paranoid about having backups. I don't want to have to recreate a year(s) worth of data for no reason, when I can just do a restore?

So a few questions to ask:

Is there a legal, company, professional, ethical, or other standard that this data needs to be retained?

If there were a loss of the DB for several days or weeks would anybody but the DBA face sanctions?

I look at it as that if the DBA wants to be stupid, he should be allowed to be. A good DBA  should be able to get everything setup so that his daily job is to check a few e-mails and then spend the rest of the day surfing the internet, playing solitaire and posting on EE to get more t-shirts. ;-)

That includes making sure every DB instance is backed up and ready to start a restore in about 20 minutes. The 20 minutes is to give his management time to figure what they want to do.

That it is a SQL Express instance means that there is no SQL Service Agent service running. There are two solutions to that. Have the SQL Service Agent from a full service connect and do the backups, or download the agent replacement from Lazy Coding.  I've done both over the years.

So if the DBA wants to put his butt on the line, fine for him. Just put it out to the management that is what he is, or isn't, doing and let them make the decision. If you are the management, then explain that you want to have consistent backups (preferably daily, or at least weekly) and that you expect to him to do it.

If he doesn't know a good way, tell him to ask on EE and we'll gladly assist him to get to the point.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now