Append backup to existing transaction LOG backup file

pjonk

We are converting all our SQL Jobs to the Redgate EXECUTE master..sqlbackup command.
Currently we use the following strategy for LOG backups:
- The first transaction LOG of the day is made using the WITH INIT command to a <db>_LOG.bak file
- The next transaction LOG's of that day are appended to the existing <db>_LOG.bak file. This way we store all transaction LOG backup's of one day in one file instead of a lot of LOG backup files. We take a LOG backup every hour.

I can't get this scenario working for the REDGATE backup's.
The first LOG backup using WITH INIT is working correctly
The next LOG backup's without using WITH INIT fail with the error below:
Backup file exists. Will not overwrite. File name: (f:\backup\ASPState_LOG.sqb)

Is it possible to append LOG backup's to an existing SQB file?

Brian Donahue

Hi Peter,

SQL Backup supports one backup per file. It does not implement backup device files in the same way as SQL Server does. Luckily, this becomes less of an issue because SQL Backup can restore the logs using wildcards, eliminating the need for backup device files if you only use them to keep your transaction log backups in LSN order.

pjonk

Okay that explains a lot. Thanks.
So I guess a datetime stamp in the backup file is needed to use this RESTORE wildcard feature right?

Brian Donahue

The LSN information is in the file header, yes. When a wildcard restore is done, SQL Backup will run a RESTORE SQBHEADERONLY on all files meeting the wildcard specification and restore them in proper LSN order.