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?

Steven Lemmens
  • 135
  • 1
  • 6
  • 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

4 Answers4

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.

John K. N.
  • 17,649
  • 12
  • 51
  • 110
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.

Community
  • 1
jstexasdba
  • 175
  • 7
  • 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. – jstexasdba 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. – jstexasdba 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.

BradC
  • 9,964
  • 8
  • 48
  • 88
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.

Alexandr Omelchenko
  • 621
  • 4
  • 13