Deleting files from Network Folder/copy location

Giggles220Giggles220 Posts: 154
edited November 29, 2010 9:30AM in SQL Backup Previous Versions
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?

Comments

  • 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
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • 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 [master] TO DISK = ''e:\backups\BIPRODUCTION\<database>\<type>\<AUTO>.sqb'' WITH ERASEFILES = 7, ERASEFILES_REMOTE = 4, MAILTO_ONERROR = ''_sqlpsqi@dfa.state.ny.us'', PASSWORD = ''<ENCRYPTEDPASSWORD>xxxxxx</ENCRYPTEDPASSWORD>'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 3, COPYTO = ''\\mscotda0a1bg\sqlback\mscotda0a1azbiproduction\<type>\<database>'', KEYSIZE = 256, THREADCOUNT = 3, DIFFERENTIAL"', @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
    
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    edited July 21, 2009 11:29AM
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks - 6.3 isn't bad at all.

    Tim
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    SQL Backup version 6.3 has been released and should fix the problem.
    Thanks for your patience.
  • 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.
  • peteypetey Posts: 2,358 New member
    Could you please post the entire BACKUP command you are using?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • peteypetey Posts: 2,358 New member
    Is the G:\ path a local or remote disk?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • It's a USB drive connected to the server. The copies to that drive location work fine, but not the deletes.
  • peteypetey Posts: 2,358 New member
    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 ...
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I await this functionality with bated breath.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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 ;
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
Sign In or Register to comment.