If that's right (which I am not doubting you, just talking out loud), then there is no way to naturally delete the local files that you create...so the FILEOPTIONS flag is meaningless without doing something manually...
I agree. I don't think that should be the proper behavior for the ERASEFILES Option. It should be left up to the User on how to keep/erase/backup etc the backup files according to their needs and/or requirements.
I just use a separate scheduled batch file job to delete the local files. Not the best option, but it works! Hopefully, RedGate will look into changing this functionality in future versions of the SQL Backup product.
FILEOPTIONS = 1 should delete both the local copy and the remote copy of the backup files according to the retention period providing the files are for the same Server/Instance/Database/Backup type and the password has not been changed.
Having no FILEOPTIONS or FILEOPTIONS = 1 will delete the local copy of the backups files regardless of the Archive flag being set.
FILEOPTIONS = 2 is used to make sure no backup files are deleted by SQL Backup until they have been archived to a tape drive and the flag removed.
When FILEOPTIONS = 1 is set, are there any error messages in the SQL Backup log files regarding files not being deleted?
Is there a support phone number I can call and talk to someone about this? This is getting frustrating.
I thought I figured out the issue and it was my fault. I had the backup creating a new folder every backup with the date as the name of the backup. So I thought maybe SQL Backup wasn't smart enough to know that those folders were created by the app and that it didn't look to delete them.
So then I had all the backup files dumped into one folder and each file is named as: <date>_<name>.sqb
and here is the job:
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [<LIST OF DBS TO BACKUP>] TO DISK = ''E:\Backup\FULL\<AUTO>.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\homebackup\FULL'', ERASEFILES = 3, FILEOPTIONS = 1, INIT, MAILTO = ''mailaddress'', THREADCOUNT = 4, VERIFY"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
Where do I find the SQL Backup logs to see if there is failure and the type of failure that's going on?
Here is the last log entry that I have (the last relevant part) that was created on the 15th of this month:
1/15/2009 10:08:42 PM: Copied E:\Backup\FULL\01_15_09_ZachsTempDB.sqb to \\homebackup\crdl\FULL\01_15_09_ZachsTempDB.sqb.
1/15/2009 10:08:42 PM: SQL Backup process ended.
[b]1/15/2009 10:08:43 PM: Deleted msdb entries older than 1/10/2009 10:08:42 PM[/b]
1/15/2009 10:08:43 PM: Deleted local history entries older than 1/10/2009 10:08:42 PM
1/15/2009 10:08:43 PM: Mail sent successfully to: DELETEDEMAIL
the part that is bolded, is that telling that it deleted all backups older than that date? If so, why is it keeping entries for 5 days when I explicitly having it delete backups older than 3 days? I only have enough space to keep around 3 days worth of backups...
If it's defaulting to 5 days, then it would cause the issue I am having of running out of space and appearing that it's not deleting the backups...
Ok I checked this morning and it deleted the older backup files on both the local and the network share.
Since now I only have last night's backup, it will be a couple of days before I can check to make sure it ran properly, but hopefully this is a good sign.
Comments
I agree. I don't think that should be the proper behavior for the ERASEFILES Option. It should be left up to the User on how to keep/erase/backup etc the backup files according to their needs and/or requirements.
I just use a separate scheduled batch file job to delete the local files. Not the best option, but it works! Hopefully, RedGate will look into changing this functionality in future versions of the SQL Backup product.
Mike
Hopefully these guys will figure out the issue.
FILEOPTIONS = 1 should delete both the local copy and the remote copy of the backup files according to the retention period providing the files are for the same Server/Instance/Database/Backup type and the password has not been changed.
Having no FILEOPTIONS or FILEOPTIONS = 1 will delete the local copy of the backups files regardless of the Archive flag being set.
FILEOPTIONS = 2 is used to make sure no backup files are deleted by SQL Backup until they have been archived to a tape drive and the flag removed.
When FILEOPTIONS = 1 is set, are there any error messages in the SQL Backup log files regarding files not being deleted?
Redgate Foundry
Is there a support phone number I can call and talk to someone about this? This is getting frustrating.
I thought I figured out the issue and it was my fault. I had the backup creating a new folder every backup with the date as the name of the backup. So I thought maybe SQL Backup wasn't smart enough to know that those folders were created by the app and that it didn't look to delete them.
So then I had all the backup files dumped into one folder and each file is named as: <date>_<name>.sqb
and here is the job:
Where do I find the SQL Backup logs to see if there is failure and the type of failure that's going on?
Please help me on this.
Where are the files not being deleted from, the local drive or the network share?
The SQL Backup log files are located by default in :
%allusersprofile%\Application Data\Red Gate\SQL Backup\Log\<instance name>
You can also open Tools>Options in the SQL Backup GUI to check the location.
Redgate Foundry
Here is the last log entry that I have (the last relevant part) that was created on the 15th of this month:
the part that is bolded, is that telling that it deleted all backups older than that date? If so, why is it keeping entries for 5 days when I explicitly having it delete backups older than 3 days? I only have enough space to keep around 3 days worth of backups...
If it's defaulting to 5 days, then it would cause the issue I am having of running out of space and appearing that it's not deleting the backups...
Any thoughts?
Since now I only have last night's backup, it will be a couple of days before I can check to make sure it ran properly, but hopefully this is a good sign.