1

I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it.

We have couple databases where sometimes there may not be any activity for 20 to 30 minutes. We have Transaction Log Backup setup every 15 minutes.

My questions are:

Would there by any TR Log Backups for that time period?

What would happen if I happen to restore those transaction log ?

I'm sorry, I tried looking for answer on different places and books, but could not find it. If someone can please help me out.

Thank you in advance.

  • Can you please explain 2nd part of your question. Then I would give complete answer to you. – Shanky Sep 8 '14 at 15:43
  • @Shanky : considering first question as a base: If I do not have any Transaction and as per schedule if Transaction Log Backup job run will it create any Transaction Log Backup files and can I skip those files, if there are not any transactions – Im88 Sep 8 '14 at 15:50
7

would there by any TR Log Backups for that time period?

If there is no activity at all, there would be not transactions. But there is no harm in taking that backup as a part of your backup schedule (which is every 15 mins).

It will look like -

/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

what would happen if I happen to restore those transaction log ?

You will be able to restore them in sequence or you can possibly skip it as well- if there is NO gap in the Log Sequence Numbers of the log backups..

1

considering first question as a base: If I do not have any Transaction and as per schedule if Transaction Log Backup job run will it create any Transaction Log Backup files and can I skip those files, if there are not any transactions

Yes of course it will create a transaction log backup file. backup job has nothing to do with whether any activity is going inside database or not. Even if DB is idle with almost nothing going inside it still if you have schedules log backup it will execute.

You should not skip any files because log backups are connected with each other using LSN( its really difficult to know when actually server is idle like pointed by Kin that you might be able to skip log backup) and if you miss a log backup file restore sequence will break. So you need to make sure you have all trn log backup files intact for restore as per RPO and RTO. If you miss log file backup there could be limited restore and hence data loss.

For second part of question. You can anytime follow the restore sequence like first restore full backup then all subsequent log backups. If transaction is committed before backup finished it would be there in backup files.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.