cancel
Showing results for 
Search instead for 
Did you mean: 

Log Backup

Ajith_Sankar
Level 6
Certified

Hi 

What is the exact requirement of a log backup(log backup policy) in Sql and Oracle ?

Is that only required for truncate the logs or any role in the database restore ?

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Ajith,

 

In the field of databases in computer science, a transaction log (also transaction journal, database log, binary log or audit trail) is a history of actions executed by a database management system to guarantee consistency over crashes or hardware failures.

 

Physically, a log is a file of updates done to the database, stored in stable storage.  If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted transactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure consistency of transactions.

 

So you'll see that the log is a journal of all the transactions that should be contained in the database. By backing up these logs along with the database it is possible to restore the database to previous states.

 

Your full backup might be run at 8pm every night, but if you have transaction log backup running every hours in between it possible to restore your database to any hourly increment. You could restore to

  1. 11 am this morning by restoring last night's full + all transaction logs for the next 15 hours.
  2. Similarly you can go back to before last night's full by using the previous night's full + transaction logs.

And yes, it is used to truncate logs if configured.

I hope that helps.

View solution in original post

4 REPLIES 4

Nicolai
Moderator
Moderator
Partner    VIP   

log backup are used for ensuring consistency of a database when using online backups.

Depending on the database, a worst case scenario is a stop of operation, if the archive log grow to large. It therefor important to archive the log files periodically. The is no recommended frequency for the archive to run, it a estimate that need to be done based on business requirement and service level agreement.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Ajith,

 

In the field of databases in computer science, a transaction log (also transaction journal, database log, binary log or audit trail) is a history of actions executed by a database management system to guarantee consistency over crashes or hardware failures.

 

Physically, a log is a file of updates done to the database, stored in stable storage.  If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted transactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure consistency of transactions.

 

So you'll see that the log is a journal of all the transactions that should be contained in the database. By backing up these logs along with the database it is possible to restore the database to previous states.

 

Your full backup might be run at 8pm every night, but if you have transaction log backup running every hours in between it possible to restore your database to any hourly increment. You could restore to

  1. 11 am this morning by restoring last night's full + all transaction logs for the next 15 hours.
  2. Similarly you can go back to before last night's full by using the previous night's full + transaction logs.

And yes, it is used to truncate logs if configured.

I hope that helps.

Ajith_Sankar
Level 6
Certified

Thanks Rian. Your post is very much informative.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi Ajith, please mark the solution, not your own post.

 

Thanks