SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Secure Transaction Logs


Secure Transaction Logs

Author
Message
Curtis Smith
Curtis Smith
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 239

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 ...
David A. Long
David A. Long
SSC Eights!
SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)

Group: General Forum Members
Points: 943 Visits: 237

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





Philip Kelley
Philip Kelley
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 232

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





Curtis Smith
Curtis Smith
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 239
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 ...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search