Maintenance plan for creating transaction log backups

Question

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?

Answer 1

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.

Answer 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.

Answer 3

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.

Answer 4

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.