Feature Request: Delete Backups on Network Drives?

Centura · September 17, 2008 4:00AM

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

Eddie D · September 17, 2008 9:22AM

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 D · September 17, 2008 10:06AM

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

Centura · September 17, 2008 10:13AM

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 D · September 18, 2008 7:29AM

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

Centura · December 11, 2008 7:25AM

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

priyasinha · December 11, 2008 8:17AM

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

Centura · December 12, 2008 2:44AM

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

Centura · December 12, 2008 5:22AM

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

priyasinha · December 12, 2008 5:54AM

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,

pkristolaitis · March 30, 2009 10:35AM

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.

Keep up the great work guys, I love your toolset.

Brian Donahue · March 30, 2009 1:17PM

Hi Peter,

This thread is quite old... ERASEFILES_REMOTE went into the 5.4 release, I believe.

pkristolaitis · March 30, 2009 1:22PM

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!

priyasinha · March 31, 2009 5:00AM

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

rick.sheeley · April 3, 2009 12:48PM

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

davidshq · April 20, 2009 2:48PM

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.

Feature Request: Delete Backups on Network Drives?

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk