sql transcation log backups

[kumarsam986]

Hi folks,

my question is related sql transcation log backups.

1)while takeing of full backup of SQL DATABASE it takes transacation logs also for backup but it can not truncate why?

2)it is possible take only database for backup instead of  logs. after that  we can resotre the database without logs

3)while taking differential backup of sql database logs will get truncate or not? if it will clear the logs which type of differential backup need to be configure like cumulative or incremental

4)I have a 3 full backup of SQL databases in such a case 3rd full backup is currpeted then we can restore the 1st and 2nd full backup along  T-logs related to all 3 databases

[Mustan]

1)while takeing of full backup of SQL DATABASE it takes transacation logs also for backup but it can not truncate why?

log trunction is done by sql, when logs are backed up they are only marked for truncation, netbackup doesnot truncate the logs its SQL application which needs to do it, if that is not happening please check the event viewer for more details, if you have SQL clsuter or so where it is waiting for logs to be replicated in that scenario aswell it will not truncate the logs immediately.

2)it is possible take only database for backup instead of  logs. after that  we can resotre the database without logs

SQL logs are written to .lDF file which is part of database, hence when you backup SQL it will backup .mdf and .LDF, there is an option to backup only logs however not the database.

3)while taking differential backup of sql database logs will get truncate or not? if it will clear the logs which type of differential backup need to be configure like cumulative or incremental

no they will not, you can only perform full, differential backup i.e cumulative incremental backup and transaction log backup  of SQL database, in netbackup

please refer to the article http://www.veritas.com/docs/000029573       for more details.

4)I have a 3 full backup of SQL databases in such a case 3rd full backup is currpeted then we can restore the 1st and 2nd full backup along  T-logs related to all 3 databases

as mentioned above SQL logs are stored in .ldf file if third full backup is corrupt, max you can go back to 2nd full and than restore differential if you have taken any in between 2nd and 3rd backup.

[NBU35]

Adding to above reply : 1)while takeing of full backup of SQL DATABASE it takes transacation logs also for backup but it can not truncate why? it depends your SQL DB is in which mode, if DB is in simple mode then FULL backup will truncate the logs and trx log backup is not possible. if DB is in full mode then full backup will not truncate the logs, you to run tlog backups to with trucation option enabled.

2)it is possible take only database for backup instead of logs. after that we can resotre the database without logs. Separately you can only backup tlogs, full and differential backup will backup both db and logs due to SQL architecture.

3)while taking differential backup of sql database logs will get truncate or not? if it will clear the logs which type of differential backup need to be configure like cumulative or incremental in Netbackup

only three type of SQL Backups.. full , Full-differential i.e cummilative, trx log backups and out of them which can run successfully, it depends on your DB mode. Full-differential does not truncate logs.

4)I have a 3 full backup of SQL databases in such a case 3rd full backup is currpeted then we can restore the 1st and 2nd full backup along T-logs related to all 3 databases ? In Netbackup SQL module, when you choose to recover Full DB using tlogs, it automatically selects preceding Full and Full differential backup. if your 3rd Full is corrupted, your recovery point will be 2nd Full and logs just before 3rd full backup (if any tlog/ful differential backup exists.)