Feature Request: Delete Backups on Network Drives?

CenturaCentura Posts: 14 Bronze 2
Hello,

in the Backup Dialog is an option to delete Backups in the Backup Folder prior to the backup.

Is it possible to add an option to delete files before copying the backups to a network drive?

Now we have to delete the files there with an extra tool.

Thanks.

Regards,
Volker

Comments

  • Eddie DEddie D Posts: 1,643 Rose Gold 5
    Thank you for your forum posting.

    If I have understood your post correctly, the feature you are seeking already exists in SQL Backup.

    Have I assumed correctly that you backup the database to a local disk and then use the COPYTO argument to make a copy of the backup file on a network share?

    If the answer is yes, you can delete the backup files located at the network share using the FILEOPTIONS = 1 argument.

    To achieve this, you must manually add the FILEOPTIONS argument to the backup script as this argument cannot be set using the SQL Backup GUI.

    For example the backup script may look similar to the following:


    EXEC master.dbo.sqlbackup '-SQL "BACKUP DATABASE [AdventureWorks] TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.7\MSSQL\Backup\<DATABASE>_<TYPE>_<DATETIME YYYYmmdd_hhnnss>.sqb'' WITH COPYTO = ''\\MyServer\NetworkShare'', ERASEFILES_ATSTART = 5, COMPRESSION = 2, VERIFY " '

    Now add the FILEOPTIONS argument:

    EXEC master.dbo.sqlbackup '-SQL "BACKUP DATABASE [AdventureWorks] TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.7\MSSQL\Backup\<DATABASE>_<TYPE>_<DATETIME YYYYmmdd_hhnnss>.sqb'' WITH COPYTO = ''\\MyServer\NetworkShare'', ERASEFILES_ATSTART = 5, FILEOPTIONS = 1, COMPRESSION = 2, VERIFY " '

    I hope the above helps.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Also, if you wish to have the backup files deleted on different schedules in the primary and network share locations, you can use the ERASEFILES_REMOTE argument.

    For example, you wish to delete backup files in the primary location older than 5 days and in the network share older than 7 days. Your script maybe similar to the following:

    EXEC master.dbo.sqlbackup '-SQL "BACKUP DATABASE [AdventureWorks] TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.7\MSSQL\Backup\<DATABASE>_<TYPE>_<DATETIME YYYYmmdd_hhnnss>.sqb'' WITH COPYTO = ''\\MyServer\NetworkShare'', ERASEFILES_ATSTART = 5, ERASEFILES_REMOTE = 7, FILEOPTIONS = 1, COMPRESSION = 2, VERIFY " '

    As per the FILEOPTIONS argument, you cannot configure this argument in the SQL Backup GUI.

    Finally the ERASEFILES_REMOTE is only available currently in the SQL Backup 5.3.0.178.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • CenturaCentura Posts: 14 Bronze 2
    It works - thanks very much.

    But after editing this nothing can by changed with the GUI - so if I want to add another Database to backup with this Job first the parameter has to be deleted and afterwords added again.

    Please change this behavior.

    Regards,
    Volker Bachmann
  • Eddie DEddie D Posts: 1,643 Rose Gold 5
    Thank you for your reply.

    We already have a feature request submitted to include the configuration of the FILEOPTIONS and ERASEFILES_REMOTE arguments via the GUI.

    I will add details of this forum post to the request.

    Unfortunately I cannot provide a timescale as to when the above arguments will be configurable from the GUI.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • CenturaCentura Posts: 14 Bronze 2
    Hello,

    as far as I read the release notes of Version 5.4 the parameter FILEOPTIONS and ERASEFILES_REMOTE didn't make it into this version of the GUI. Am I right?

    When will they go into?

    Regards,
    Volker
  • Hi,

    We are adding support for ERASEFILES_REMOTE in GUI in the next version. I can assure you that this has already been implemented internally.

    FILEOPTIONS would most probably be in the version after next release.

    Thanks,
    Priya Sinha
    Project Manager SQL Backup
    Priya Sinha
    Project Manager
    Red Gate Software
  • CenturaCentura Posts: 14 Bronze 2
    Thanks,

    why does the implementation of such options last so long?

    Anyway:
    I tried to use the option erasefiles_remote this night for the first time with the version 5.3.0.178.

    But the backup fails when I add
    erasefiles_remote = '20h'

    EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [< multiple datbases> ] TO DISK = ''E:\SQLBackup\<AUTO>.sqb'' WITH COMPRESSION = 3, COPYTO = ''\\<server>\SQLBackup'', ERASEFILES = 1, FILEOPTIONS = 1, ERASEFILES_REMOTE = ''20h'', MAILTO = ''<email>'', THREADCOUNT = 15, VERIFY"', @exitcode OUT, @sqlerrorcode OUT

    with this error:
    Syntax error: '20h' after '='

    Raiseerror:
    SQL Backup failed with exit code: 850 SQL error code: 0

    What's wrong?

    The "<>" values are filled correctly as the backup with erasefiles_remote=1 runs normally.

    May I use erasefiles_remote= 0 to delete every old backup on the remote server? That would fix my problem here.

    Thanks.

    Regards,
    Volker
  • CenturaCentura Posts: 14 Bronze 2
    I got it.

    The option must be included without quotation marks

    Wrong
    ERASEFILES_REMOTE = '24h'

    Right
    ERASEFILES_REMOTE = 24h

    There is a typo in the help at this point.

    Thanks.

    Regards,
    Volker
  • Hi,

    Yes you are right.

    For deleting all the files on your remote server except the one which you have created just now, you should use ERASEFILES_REMOTE=1. We consider '0' as invalid value. Though you would be able to enter '0' in 5.3 server components but we are introducing this validation in next release and you will get syntax error if you enter '0'.

    Thanks,
    Priya Sinha
    Project Manager
    Red Gate Software
  • priyasinha wrote:
    We are adding support for ERASEFILES_REMOTE in GUI in the next version. I can assure you that this has already been implemented internally.
    Any idea when the next version of SQL Backup with this feature will be available? If there's one feature I'm dying for right now, it's this one. :D

    Keep up the great work guys, I love your toolset.
    Peter Kristolaitis
    Database Administrator
    VON Canada
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Peter,

    This thread is quite old... ERASEFILES_REMOTE went into the 5.4 release, I believe.
  • Hi Brian;

    I'm aware that the feature exists in 5.4, but only if you manually write/modify the script. What I was wondering about is the ability to manage that functionality through the GUI -- it's cumbersome (and error-prone) to edit scripts every time we need to modify the jobs.

    Priya's reply seems to indicate that there were plans to add the functionality to the GUI, and I was wondering about the timeline for that.

    Thanks!
    Peter Kristolaitis
    Database Administrator
    VON Canada
  • Hi Peter,

    Yes, ERASEFILES_REMOTE will be in UI in next version. We have already implemented this internally. We are aiming to release in Q2 but I should warn you that this plan may change.

    Regards,
    Priya
    Priya Sinha
    Project Manager
    Red Gate Software
  • Peter and Vollker,

    Until Redgate can impliment everything, have you considered just changing the Job steps via a SQl script? Jobs are just text records in msdb, and you should be able to easily manipulate these to add the commands you need for each job.

    Just a thought.....

    Rick in Phx
    Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
    Sacramento, CA Cell: 602.540.6750
    "Those are my principles, and if you don't like them... well, I have others." - Groucho Marx
  • Just wanted to jump onto this and say that a GUI interface for these options would be amazing. :) Thanks for the great work guys.
    Dave.
    - Network Administrator, Philadelphia Biblical University (www.pbu.edu).
Sign In or Register to comment.