Transactional log back up taken monthly once

• Irish

  Hall of Fame

  Points: 3797

  More actions

  January 18, 2011 at 11:31 am

  #1274905

  Dj463 (1/11/2011)

  ---

  Hello all,

  I am currently taken full backups of a database daily. For some auditing purpose i need all the transactions that happen in that database. So if i copy the physical transactional log initially and taking transactional log backup monthly. Does this serve my purpose.

  For example:

  If i am taking full backup of database X daily at 2 AM, then

  3 AM JAN 1- transactional log backup

  3 AM FEB 1- transactional log backup

  3 AM MAR 1- transactional log backup

  .

  .

  . so on.

  So does transactional log backup taken on 3 AM FEB 1 contain all the transactions from 3AM JAN 1 transactional log backup (or) only the transactions from 2AM FEB 1 Full backup.

  Gilamonster has it quite right on this. If you need to maintain audits on certain changes made, the best way to do that is by setting Triggers to gather that information for you. That is what we use. Each table in the database has an associated audit table. When there is an update, insert, or delete we write out who made the change, what the value was before (Inserts appears as NULL), what the new value is, and when it was changed.

  Yes, you can get the same with C2 auditing, but that keeps every change made in the database ever and is not selective based on a particular application. Likewise, you can get this information from a Transaction Log, but the Log file has to be mined to get that data.

  Lastly, I am not sure why this thread is in Backups. This is a matter of business logic and change retention than a backup/recovery scenario. :hehe:

  Regards, Irish