ERASEFILES Option Not Working 5.3.0.178

thomascroththomascroth Posts: 17
edited April 21, 2008 5:18PM in SQL Backup Previous Versions
I've upgraded half of my server installations from 5.1.0.2781 to 5.3.0.178. I have not changed any of the options in the SQL jobs executing the SQL backups, but after the upgrade, the ERASEFILES option no longer deletes the files that meet the criteria.

The servers still running 5.1.0.2781 delete the old files, while the servers running 5.3.0.178 don't delete the files. All of the servers regardless of the version installed back their data up to the same network location.

The following shows the SQL Agent code that is curently running on each server, and then the first part of the log from a 5.3.0.178 job:

master..sqlbackup
'-SQL "BACKUP DATABASES EXCLUDE [master, model, msdb, tempdb]
TO DISK = [\\ServerStorage\SQLBackup\ServName\UserDBs\<AUTO>.sqb]
WITH COMPRESSION = 3,
ERASEFILES = 8,
INIT,
MAILTO = [SystemsSupport@abc.com],
KEYSIZE = 256,
PASSWORD = [<ENCRYPTEDPASSWORD>XXXXX</ENCRYPTEDPASSWORD>]" -E'



SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASES EXCLUDE 'master, model, msdb, tempdb'
TO DISK = '\\ServerStorage\SQLBackup\ServName\UserDBs\<AUTO>'
WITH COMPRESSION = 3,
ERASEFILES = 8,
INIT,
MAILTO = 'SystemsSupport@abc.com',
KEYSIZE = 256,
PASSWORD = 'XXXXX' " -E


Thanks for your assistance!!!
Tom

Comments

  • peteypetey Posts: 2,358 New member
    Has the backup password been changed?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • No, the password hasn't changed. The only thing changed was the actual software upgrade from 5.1.0.2781 to 5.3.0.178. No other settings have changed.

    After the upgrade, I performed several backups and tested the ability to recover. The recovery process worked correctly. This issue has now been going on for more than 10 days, so at this point the ERASEFILES option should be deleting files that have been created with the new version.
    Tom
  • peteypetey Posts: 2,358 New member
    If you have a small backup file that should have been deleted but was not, could you please send it to me, together with the password and the SQL Server instance name that this database was attached to?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • What email address or ftp site do you want me to place the information?
    Tom
  • peteypetey Posts: 2,358 New member
    Please send the details and file (if its not too large) to peter.yeoh@red-gate.com. If the file is large, let me know and I'll try to set something up.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • We have the same issues. Because we also use COPYTO, we have tried various values for FILEOPTIONS ("5" being what we were told to use by tech support), including not using the FILEOPTIONS at all. We are still having files not deleting.
    The file in question last weekend was on the local box. It was backed up with COPYTO another box. We could not delete the file manually because it was "in use", even though it was 2 weeks old! I was going to try taking the SQL Backup Agent Offline / Online, but when I tried to delete it first this morning, I was successful. (The SQL cluster and the Backup Agent had not rolled or been restarted.)
    Our databases are over a TB, so sending them to you is not feasible.
  • peteypetey Posts: 2,358 New member
    The FILEOPTIONS value could be either 1 or 3 if you only want to erase files on the COPYTO locations.

    Using the value 1, SQL Backup will pick up all relevant files for deletion in the COPYTO folders. What is meant by relevant is that the files originated from the same server and database, are backups of the same type as that being performed now, and if encrypted, uses the same password as that being used or the current backup. If any of the attributes are different, the file will not be deleted.

    You use FILEOPTIONS = 3 when you only want to delete files that do not have the archive attribute.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.