3

I have a database that is set to the 'Full' recovery model, and I'm creating a maintenance plan to backup the Transaction Log every 15 minutes. A full backup is then taken once every 24 hours.

I was wondering: will the maintenance plan for the transaction logs create a lot of different files every 15 minutes, or will it by default append to the same file for 24 hours?

Also: what is preferred? I couldn't find anything on this subject (or maybe I was Googling the wrong words).

Thanks for any pointers anyone can give me.

EDIT: I also see that the checkmark "Verify backup integrity" is OFF by default. Is there a risk with turning it on? It seems to be that it should be on by default, so is there a reason for not using this?

  • 4
    I'd recommend against maintenance plans entirely. There are totally free scripts that do a much better job of things: ola.hallengren.com – Erik Darling Jun 12 '17 at 13:29
5

You might want to read up on the concept of Transaction Log Backups in the following Microsoft Technet Article: SQL Server - Understanding SQL Server Backups

If you set up a maintenance plan that creates a transaction log backup of all your user databases, then the backup job will create a *.TRN (default extension) file for each database and for each scheduled run.

Depending on the retention period you set in your maintenance plan, you will/can have literally thousands of transaction log backup files (*.trn) in your backup location.

E.g. Backing up a 10 user databases every 15 minutes and leaving the retention period for clean-up to 30 days, will leave you with 28'800 transaction log backup files lying around.

10 db * 1 trn/db/15min * 1440 min/d * 30d = 28'800 trn files

db = database
trn = trn backup file
min = minutes
d = day

So depending on the size of the databases and the amount of changing data, you could end up needing quite a lot of disk space for your transcation log backups. Then again, having a short retention period in your maintenance plan could result in lower disk space requirements.

Pssst, after reading the Microsoft article, have a look at some alternative backup solutions, like Ola's Maintenance script or other alternative solutions.

2

You will have 1 Tlog backup every 15 minutes per database you have set in the maintenance plan. In regards to Verify Backup integrity, I would reference this answer for Verify Backup Integrity.

  • So, one file per database? Or one file per database every 15 minutes? (in other words, after a few hours, will it flood my folder with different log backups per database, or will it remain one file per database) – Steven Lemmens Jun 12 '17 at 12:42
  • Yes - one file per database every 15 minutes. – user120572 Jun 12 '17 at 12:47
  • It depends on how you set up your folder structure. I have my TLogs set up to go into their own database folder for each. I also have the clean up set to 120 hours so I do not blow the system out. – user120572 Jun 12 '17 at 12:48
1

Regarding your follow-up question:

I also see that the checkmark "Verify backup integrity" is OFF by default. Is there a risk with turning it on? It seems to be that it should be on by default, so is there a reason for not using this?

I'd recommend leaving this off.

See this related question for a more complete discussion, but all this does is do a RESTORE VERIFYONLY on each backup file immediately after the backup completes, which doubles your backup job time for very little benefit.

All a RESTORE VERIFYONLY does it ensure that the backup file can be read off the disk, which is something that used to be a big deal in the "olden-days" when we backed up straight to flaky tape drives. Your environment might be different than mine, but I haven't backed up to tapes in a decade or more.

It doesn't do anything to validate that the backup is restorable, something you can only tell by doing a test restore (usually in a test environment).

So leave it off on backups, but make sure that you are occasionally doing full test restores of random backups to make sure they work.

0

Yes, you will get every 15 minutes a new file with transaction log backup. But such backup strategy can fully your local drive. I'd recommend you to use a third party tool like SQLBackupAndFTP of SqlBak wich can backup your SQL Server databases and delete old backups according to your schedule automatically.

Disclaimer: I am the Marketing Manager for Pranas.NET.

  • Alex, please disclose your affiliation to SqlBak when providing links to your company's product in answers, as per the behavior guidelines. – LowlyDBA Jun 12 '17 at 14:51

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.