Deleting files from Network Folder/copy location

Giggles220 · July 15, 2009 8:00AM

I was really excited about this new feature but now I'm finding that it isn't working as advertised. I have my job set up to delete files older than 7 days in the original backup location and after 4 days in the network copy location. When I looked at my network copy location today I see it is still saving 7 days worth of backups. In the past I used other jobs to do the deletes and it looks like I'll have to go back to doing so as I don't have enough space sometimes to keep 7 days worth. is this a known bug or am I doing something wrong?

HJoyce · July 15, 2009 10:03AM

Hi Giggles,

Can I just ask when you last ran a backup job? The purging of backups is a task which happens after the backup itself, so the files older than 4 days on your network location will only get deleted the next time it's run. Also it will only delete files for the databases which are being backed up in the job, so files for different databases will remain in the location.

If it is running a backup job for the databases which have backups in the remote location and they are not being deleted, could you send me the command you are using?

thanks,
Helen

Giggles220 · July 15, 2009 10:07AM

Helen
The job runs every night so I'd expect it to do deletes every night.

Here is the code in the job. I created the job using the GUI and did not modify it outside the GUI at all.

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES EXCLUDE &#91;master&#93; TO DISK = ''e:\backups\BIPRODUCTION\&lt;database&gt;\&lt;type&gt;\&lt;AUTO&gt;.sqb'' WITH ERASEFILES = 7, ERASEFILES_REMOTE = 4, MAILTO_ONERROR = ''_sqlpsqi@dfa.state.ny.us'', PASSWORD = ''&lt;ENCRYPTEDPASSWORD&gt;xxxxxx&lt;/ENCRYPTEDPASSWORD&gt;'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 3, COPYTO = ''\\mscotda0a1bg\sqlback\mscotda0a1azbiproduction\&lt;type&gt;\&lt;database&gt;'', KEYSIZE = 256, THREADCOUNT = 3, DIFFERENTIAL"', @exitcode OUT, @sqlerrorcode OUT
IF &#40;@exitcode &gt;= 500&#41; OR &#40;@sqlerrorcode &lt;&gt; 0&#41;
BEGIN
RAISERROR &#40;'SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode&#41;
END

petey · July 20, 2009 2:52AM

Is the password the same for the current file as it was for the 4-day old files? Also, only old backups of the same type will be deleted i.e. in this case, only differential backups of the same database that are 4 days old will be deleted.

Thanks.

Giggles220 · July 20, 2009 7:54AM

our password has not changed so all the files should have the same password. This folder only contains differential backups per the script. I just looked at the folders and now it appears that files are being deleted. Is it possible that SQL Backup 6 will not remove sql backup 5 files?

petey · July 21, 2009 4:37AM

Yes, it is indeed a bug, where SQL Backup 6 fails to identify encrypted files created by previous versions of SQL Backup for deletion.

This will be fixed in the next release of SQL Backup. Thanks for bringing this to our attention.

tyanni · July 21, 2009 10:54AM

petey wrote:

Yes, it is indeed a bug, where SQL Backup 6 fails to identify encrypted files created by previous versions of SQL Backup for deletion.

This will be fixed in the next release of SQL Backup. Thanks for bringing this to our attention.

By next release, do you mean it was fixed in 6.1, will be fixed in the next release of 6.x, or that I will have to manually purge my files since it won't be fixed until version 7.x?

petey · July 21, 2009 11:22AM

For the moment, the fix is planned to be included in version 6.3.

Until then, you will have to manually purge encrypted version 3, 4, 5 files, until only version 6 files remain in the backup folders.

Thanks.

tyanni · July 21, 2009 11:27AM

Thanks - 6.3 isn't bad at all.

Tim

Brian Donahue · December 7, 2009 6:07AM

Hello,

SQL Backup version 6.3 has been released and should fix the problem.
Thanks for your patience.

vshih · July 29, 2010 1:50AM

What is the current state of this issue? I am running SQL Backup 6.4.0.56, I've tried specifying FILEOPTIONS = 1 manually, and it still does not remove old backups from the remote copy location.

petey · July 29, 2010 4:14AM

Could you please post the entire BACKUP command you are using?

Thanks.

vshih · July 29, 2010 4:29AM

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [...] TO DISK = ''D:\Backups\SQLServer\<database>\<AUTO>.sqb'' WITH ERASEFILES = 4b, ERASEFILES_REMOTE = 3b, FILEOPTIONS = 1, MAILTO = ''...'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 2, COPYTO = ''G:\Backups\SQLServer'', THREADCOUNT = 7"', @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

Note that I added the FILEOPTIONS directive manually. Previously it was not there, but that configuration did not work either.

petey · July 29, 2010 4:38AM

Is the G:\ path a local or remote disk?

Thanks.

vshih · July 29, 2010 4:45AM

It's a USB drive connected to the server. The copies to that drive location work fine, but not the deletes.

petey · July 30, 2010 3:34AM

I guess that there are always 4 backup sets in the 'G:\Backups\SQLServer' folder, and not 3 as specified by your ERASEFILES_REMOTE = 3b option. ERASEFILES_REMOTE will only apply its retention policy to files located on network shares.

ERASEFILES = 4b alone would have retained 4 backup sets in the primary backup folder (D:\Backups\SQLServer\<database>). By adding FILEOPTIONS = 1, SQL Backup will then also apply that retention policy to your COPYTO folder (G:\Backups\SQLServer).

Unfortunately, there isn't a way to do what you require now i.e. retain 4 backup sets in the primary backup folder, and 3 backup sets in the COPYTO folder.

The next release of SQL Backup will make all this much more straightforward, by adding a ERASEFILES_PRIMARY and ERASEFILES_SECONDARY options. So to do what you need, you can use

BACKUP ... WITH ERASEFILES_PRIMARY = 4b, ERASEFILES_SECONDARY = 3b ...

vshih · July 30, 2010 3:42AM

I await this functionality with bated breath.

petey · July 30, 2010 3:48AM

I guess in our use case scenarios, we expected users to copy backup files to remote shares, not to another local disk, from a disaster recovery point of view. Then again, everybody has different needs, which was why we added the 2 new options in the next release.

Paula · November 24, 2010 11:27AM

Here is the code, but it doesn't remove the files.

set @strsql =
'EXECUTE master..sqlbackup ''-SQL "BACKUP DATABASE test TO DISK = ''''\\share\temp\test.sqb'''' WITH ERASEFILES = 7, ERASEFILES_REMOTE = 7, FILEOPTIONS = 1, COPYTO = ''''\\machinename\d$\temp\'''', INIT, THREADCOUNT = 2"'', @exitcode OUT, @sqlerrorcode OUT'

EXECUTE sp_executesql @strsql, @ParmDefinition, @exitcode=@ecode OUTPUT, @sqlerrorcode=@secode OUTPUT ;

petey · November 25, 2010 12:36AM

Does the SQL Backup Agent service startup account have rights to delete files on the '\\share\temp\' and '\machinename\d$\temp\' directories?

If this is a test machine, could you try running test backups over 8 days by manipulating the system time (you will need to use different file names, and not just 'test.sqb'), and send me the SQL Backup log for the backup that was suppose to delete the old files but did not do so? The default directory where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.

Thanks.

Paula · November 29, 2010 9:30AM

It has permissions as the backups are copied there, just not removed. The files names are actually a combo of dbname and date.

Here is last log of DelayCheck. It has been running over 2 weeks.

SQL Backup log file 6.4.0.56

-SQL "BACKUP DATABASE DelayCheck TO DISK = '\\<share>\DelayCheck\DelayCheck_backup_20101129021500.sqb' WITH ERASEFILES_REMOTE = 7, ERASEFILES = 7, FILEOPTIONS = 1, COPYTO = '\\<remotecomputername>\d$\SQLServer\Backups\DelayCheck\', INIT, THREADCOUNT = 7 "

PROCESSES COMPLETED SUCCESSFULLY

11/29/2010 2:15:00 AM: Backing up DelayCheck (full database) to:
11/29/2010 2:15:00 AM: \\<share>\DelayCheck\DelayCheck_backup_20101129021500.sqb

11/29/2010 2:15:01 AM: BACKUP DATABASE [DelayCheck] TO VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F', VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F01', VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F02', VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F03', VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F04', VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F05', VIRTUAL_DEVICE = 'SQLBACKUP_237CB09B-13F8-4D00-BAC7-9E861464D01F06' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (DelayCheck), 11/29/2010 2:15:00 AM', DESCRIPTION = N'Backup on 11/29/2010 2:15:00 AM Server: <localcomputername> Database: DelayCheck', FORMAT

11/29/2010 2:15:31 AM: Database size : 8.849 GB
11/29/2010 2:15:31 AM: Compressed data size: 535.220 MB
11/29/2010 2:15:31 AM: Compression rate : 94.09%

11/29/2010 2:15:31 AM: Processed 241992 pages for database 'DelayCheck', file 'DelayCheck_Data' on file 1.
11/29/2010 2:15:31 AM: Processed 3 pages for database 'DelayCheck', file 'DelayCheck_Log' on file 1.
11/29/2010 2:15:31 AM: BACKUP DATABASE successfully processed 241995 pages in 30.114 seconds (65.830 MB/sec).
11/29/2010 2:16:04 AM: Copied \\<share>\DelayCheck\DelayCheck_backup_20101129021500.sqb to \\<remotecomputername>\d$\SQLServer\Backups\DelayCheck\DelayCheck_backup_20101129021500.sqb.
11/29/2010 2:16:04 AM: SQL Backup process ended.

Deleting files from Network Folder/copy location

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk