November 5, 2009 at 12:50 pm
I have a requirement for SQL Server audit trails and security to store a years worth of audit data through Error logs being copied/archived for later reading if necessary.
How best to automate this process. I know that the ERRORLOG files are rolled over (recycled) after so many have been written.
Is there a way to automate this process? I did see the "Archiving SQL Server Error Logs" article on SQLServerCentral.com site, but that is a copy of one ERRORLOG file and is not automatic as far as knowing when to copy the file before it is lost or moved down in the ERRORLOG.# order.
Thanks,
Zee - Atlanta
November 5, 2009 at 1:33 pm
Run a backup on the directory the log files are stored in. That's the easiest solution. If it's a diff backup, you'll only get files that have changed since they were last backed up. By this, I mean a file backup, not an SQL backup.
- 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
November 5, 2009 at 7:24 pm
GSquared,
Do I do this through the Backup Wizard [dialog] or from Query pane (SQL)? I will test this tommorrow at work.
Thanks,
ZeeAtl
November 6, 2009 at 6:45 am
No. You would do this through either Windows built-in backup, or through whatever backup software you're using for everything except databases. Regular file backup.
- 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
November 6, 2009 at 7:02 am
I would suggest that you determine the size/frequency needed to archive the errorlogs. If you are running C2 auditing, the logs grow very large very fast so you may need to do this daily. We do it weekly. We then copy the file to a location where we also change the name to include the date. We maintain these files for a period of time as dictated by our guidelines. These files are also located in an area which is backed up to tape daily, so that we have 2 copies available. To start the process we have a job step which issues a DBCC ERRORLOG command which closes the current log and starts a new one.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply