Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Secure Transaction Logs Expand / Collapse
Author
Message
Posted Wednesday, June 22, 2005 10:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 20, 2014 10:05 AM
Points: 287, Visits: 208

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 ...
Post #193066
Posted Thursday, June 23, 2005 1:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:36 PM
Points: 631, Visits: 236

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




Post #193331
Posted Thursday, June 23, 2005 9:33 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, October 6, 2014 1:56 PM
Points: 649, Visits: 209

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

 




Post #193783
Posted Friday, June 24, 2005 9:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 20, 2014 10:05 AM
Points: 287, Visits: 208
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 ...
Post #193943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse