ERASEFILES_ATSTART fails to delete all backup files

stratburst · June 2, 2005 9:26AM

I am performing transaction log file backups via a scheduled stored procedure job utilizing ERASEFILES_ATSTART. The job successfully backs up all logs but fails to erase all of the transaction log backup files as configured. All databases configured with full recovery model. No error seem to be logged. The following is a snippit of the SP that sets the log backup parameters. Please advise.

set @backupstring = '-SQL "BACKUP LOG TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ')'', VERIFY, ERASEFILES_ATSTART = 1, MAILTO_ONERROR = ''xxx@xxxx.com'', COMPRESSION = 1" -E'

Daniel Handley · June 2, 2005 12:31PM

Hi

The ERASEFILES_ATSTART and ERASEFILES options value is number of days. This is calculated as 24 hour blocks, so the only files deleted will be greater than X*24 hours old.

In your script all the files that remained should be less than 24 hours old.

Regards
Dan

stratburst · June 2, 2005 12:55PM

The server has multiple databases, the SP backs up TRN log files every 2 hours and deletes log backup files older than one day on all databases as configured, except for a few. The backup files that are not being deleted go back to the date that the SQLBackup job was created.

Any known issues with long file names? This might be entirely irrelevant but it seems that the backup files that are not being deleted are 20+ char long. Doesn't seem to be a 15+1 char issue as several of the databases are longer that 16 char but less than 20.

Daniel Handley · June 3, 2005 11:37AM

Hi

We have had no reports of long file name problems. Can you check that the SQLAgent user has adequate rights to delete files from the directory, and that you are running version 3.2 .

Regards
Dan

stratburst · June 17, 2005 4:07PM

I have run some tests and have verified that this appears as a bug in SQLBackup. Erasefiles will not remove files with database names that are longer than 22 charactors. Resolution: export database with long name to database with name less than 22 charactors. Erasefiles will then work as expected.

petey · June 18, 2005 11:58AM

Thx for reporting the bug.

petey · June 19, 2005 12:32PM

The problem is that database names are truncated to 22 characters in the SQL Backup file header block. For ERASEFILES to work, it needs to find an exact match for the database name.

This will be fixed in a future release. If you need this to work now on English operating systems, you can do the following, but note that this is an unofficial, unsupported workaround and has not been fully tested:

Using a hex editor, look for the following sequence in sqlbackup.exe and sqlbackupc.exe:

B916000000BA01000000

Change the value 16 (hex for 22) to 2C (hex for 44). You should find two occurences of this sequence. The first is for the instance name, and the second for the database name. Making both changes will then store the first 44 characters of the instance/database name in the SQL Backup file header block.

Mel · July 26, 2005 11:43AM

Has this been fixed in the latest version of SQLBackup?

Brian Donahue · July 26, 2005 12:53PM

Hi Mel,

Sorry, but we haven't released a new version since the last post on 19 June. I'm sure that the fix will be included in the next release.

mkleppe · November 28, 2005 5:40PM

Has this been fixed in the latest version of SQLBackup yet...?

petey · November 28, 2005 8:41PM

This will not be changed in version 3 as on non-English systems, the 44 bytes need to cater for multi-byte character sets.

In version 4, the full name of the database (max 128 characters) will be stored as a unicode string.

ERASEFILES_ATSTART fails to delete all backup files

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk