7

A recent question got me looking at MS Documents and wondering.

Is there a limit to how many backups can be appended to a single file?

By default SQL Server uses NOINIT to append new backups to the old backup file.

{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT). Source

The documents clearly say that lack of free disk space will cause an appended backup to fail.

If a disk file fills while a backup operation is appending a backup to the media set, the backup operation fails. The maximum size of a backup file is determined by the free disk space available on the disk device; therefore, the appropriate size for a backup disk device depends on the size of your backups. Source

The answer at SQL Recover from .bak file with NOINIT indicates that the Position from RESTORE HEADERONLY indicates the individual backup in the file. Which is a "smallint" field, which should max out at 32,767

For the most part when googling around, you find people who are accidently appending their backups, and can't understand why the backups are so big.

I don't find any clear references on how many backups can be appended, assuming sufficient disk space. Is the limit 32,767 or something else entirely?

  • 5
    Seems an easy thing to check, but better advice in most cases is to not use append at all... – Aaron Bertrand Aug 7 at 17:25
  • @AaronBertrand Easy maybe, quick no. If you did one backup a minute it would take more than 3 weeks to take 32,767 backups. – James Jenkins Aug 7 at 17:38
  • 2
    Why do you have to wait a minute? – Aaron Bertrand Aug 7 at 17:39
  • @AaronBertrand as it turns out, at some point it slows so much that even one per minute is not feasible. – James Jenkins Aug 12 at 12:03
  • 1
    Right, but you could still keep going, even if it takes incrementally longer per file. Your question was "how many can there be?" not "how long does it take to get there?" I gave up because it is purely academic. – Aaron Bertrand Aug 12 at 12:07
7
+500

TL:DR; It is possibly to put 32,000+ backups on a single file. If this is good thing or if you can recover from a backup on this file is not addressed here.


I started taking tlog backups last night, on an existing database (231682) with no activity. I used a while loop and a counter so I could get a running total.

DECLARE @counter int
SET @counter = 1
While 1=1
Begin 
BACKUP LOG [231682] TO  
DISK = N'G:\SQLBackups\Test_Tlog.trn' WITH NOFORMAT, NOINIT,  
NAME = N'231682-Log Database Backup', SKIP, NOREWIND, NOUNLOAD
SET @counter = @counter + 1
print @counter
End
  • 16 hours later the count is at 5,967 with a file size of 356MB. This might take a while. At the start was completing 24 log backups per second.
  • 1 day 16 hours: count is 8,401 with a file size of 700MB, sp_whoisactive shows wait info (2029ms)BACKUPTHREAD
  • 4 days 18 hours: count is 12,834 with a file size of 1.6GB sp_whoisactive shows wait info (52113ms)BACKUPTHREAD time between backups has slowed to about 70 seconds per log backup.
  • 5 days and some hours; an external event (server reboot) caused the tlog backups to halt. count of completed backups is 13,717 file size is 1.8GB, time between backups is about 80 seconds. Validated with restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn' Did not attempt a database restore, as it would be beyond painful. Set counter to start SET @counter = 13717 and restart adding backups to the same file with the same code. Backups resume and are taking about 80 seconds
  • 1 week; second restart due to external issues. Count is 15,186 file size 2.3GB each backup is taking about 90 seconds.
  • Week and half; Count is 17,919 file size is 3.2GB each backups is taking about 2 minutes 25 seconds.
  • 2 Weeks; Count is 19,645 file size is 3.8GB each backup is taking about 2 minutes 45 seconds.
  • 3 Weeks; count is 22,919 file size is 5.2GB each backup is taking about 3 minutes 30 seconds (17 per hour) as I am running the endless t-log backups in a job now, I have added this to the code RAISERROR(N'Count equals :%d', 16, 1, @counter ) WITH LOG; so the running count displays in the SQL error log Thank you @Erik Darling
  • 4 weeks; count is 25,587 file size is 6.6GB each backup is taking about 3 minutes 45 seconds (16 per hour)
  • 5 Weeks; count is 28,242 file size is 8GB each backup is taking about 4 minutes 35 seconds, (13.5 per hour)
  • 6 weeks; count is 30,037 file size is 9.1GB each backup is taking about 5 minutes (12 per hour)
  • ~7 weeks: Running out of space on the dedicated backup disk. Backups start failing when insufficient space, but the job keeps running and trying. Stop everything else, made a bit more space by deleting some files. The count in SQL logs is wrong, as it counts both failed and successful attempts.
    • Stop job
    • File size is 10.4GB each backup is taking about 5 minutes (12 per hour), time is the same on success or failure.
    • Check backup with restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn' Count is 32,021
    • Header implies all the successful backups are fine.
    • Most backups show a value of 75,776 for BackupSize and a value of ~4,000 for CompressedBackupSixe, Compressed size varies on each backup.
    • I did not attempt a Restore with the t-log file. I suspect there will be issues, and I would want to try it before I made everything as ugly as it is now.
    • Delete the t-log file, restart normal backups.

I am compressing backups by default on this instance.

Week 3 Note: File size and backup time is growing out of proportion to number of backups. Looking at tlog headers, we see the backup in position 2 has a size of 75766 bytes and a start to finish time of one second or less. The backup in position 22919 also has a size of 75766 bytes and a start to finish time of one second or less. The overhead of appending the backups to the same file seems be causing the slow down. The abnormal growth is probably related to weekly maintenance tasks I have running on the instance.

Off site backups, It looks like my offsite backup solution (IBM Spectrum) is not backing up the trn file. I suspect this is because the file is constantly being edited.


Edit, some time later. I was considering doing another experiment to test recovery at around 30,000 backups. To avoid the issues of trying to restore multiple t-logs I looked at using differential backups. I created an empty database, took a full backup and then took 10 differential backups. Then I took 10 t-logs backups and using RESTORE HEADERONLY FROM DISK I compared the size, the differential backups are significantly larger then the t-logs, I don't have a enough space to perform a good test.

Differential backups 2-10 (first is always a bit bigger)

  • BackupSize = 1126400
  • CompressedBackupSize = 62271

T-Logs backups 2-10 (first is always a bit bigger)

  • BackupSize = 75776
  • CompressedBackupSize = 3800 (average, it varies)

Differential backups are about 16 times larger, best case I could only get about 2,000 of them, I am not doing further testing at this time.

3

There should be no other limit than maximum possible file size. The backup file is written in Microsoft Tape Format and new headers are simply appended to the file.

MTF Volume Description block

  • What happens when it attempts to write the 32,768th record and it can't because it has reached the max value for the small int field that counts the number of backups? – James Jenkins Aug 12 at 13:12
  • The MTF is a linear format with blocks set in the file itself so there are no limits set there. The Restore headerony can only show a smallint value there so then something will probably break when you try to read the headers past that past that. (docs.microsoft.com/en-us/sql/t-sql/statements/…). I think my backups might take some time – Spörri Aug 12 at 14:44
2

Is there a limit to how many backups can be appended to a single file?

3285.

Actually, that just as far as I got. The backup file got to 10GB, and each backup was taking 10 seconds, so I wasn't willing to wait longer.

Using:

use master
go
create database bt
go
backup database bt to disk='c:\temp\bt.bak' with noinit
go 10000

restore headeronly from disk='c:\temp\bt.bak' 

restore database bt from disk='c:\temp\bt.bak' with file = 3285, replace
  • I got further than that (still going), but I’m doing log backups, so they’re definitely smaller, and I wonder if using compression, enabling TF 3226, probably the size of database (empty but based on size of model), etc. impacts the upper bound. – Aaron Bertrand Aug 7 at 22:42
  • @AaronBertrand is yours still running? How far did you get? – James Jenkins Aug 8 at 12:49
  • 2
    @JamesJenkins I got to about 6,400 before it really started slowing down and I canceled it. Basically, it supports more appended files than you could ever possibly need. – Aaron Bertrand Aug 8 at 12:54
  • 1
    ...or put timestamps in the filename, like sane people do... – Aaron Bertrand Aug 8 at 17:23
  • 3
    The only time appending backups is useful is where your backup device is a tape. In other words, the feature stopped being useful a long time ago. – David Browne - Microsoft Aug 8 at 17:38

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.