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.
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.
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.