Secure Transaction Logs
-
Due to SOX, i am being asked to do the following:
We purchased LogExplorer to view the transaction logs for auditing. I am being asked to copy the transaction logs to a secure location, so they can be reviewed.
My Problem: I backup the log every 11 minutes and name the file based on the date and hour. During the same hour I append the transaction log backup to the file. What is the best approach for the back, and copy to the secure location. Keeping in mind that the file being copied may be in use. Also, if successfully copied I need to remove the file from by original location.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
You could create another Job scheduled a few minutes after the hour to perform a CmdExec MOVE ... operation. The few minutes should be greater than the worst case log backup duration, to eliminate the file in use issue.
Andy
-
Adding another step to your backup job to perform the move might work as well. If I have it right:
- You're creating t-log backups every 11 minutes [11? 10, 12, and 15 are all even factors of 60 minutes, why 11?]
- All backups that occur within a given hour are loaded into the same physical file
- When you get to the "next" hour, a new file is created
If so, having a step 2 that moves (or copies and then deletes) all files "prior" to the current file to your destination should work. Your current backup file (set) isn't touched, and your old files are deleted after their copied [but be darn sure the copy is succesful before deleting it!] If the file's in use, the copy or the delete will fail... only to be processed again with the next run.
Philip
-
Thanks Philip
, that is what I was thinking, just wanted another opinion. I just chose 11 minute to stay away from human nature, people tend to think in even numbers.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply