Copy backup to network auto deletion

morne3107

How do you setup SQL Backup to delete the copies made to an offsite location after several days. The normal removal of Backups older than "X" days works fine, but the copy to an offsite location does not provide you with that option?

Is this possible?

petey

Use FILEOPTIONS = 1 when you want the deletion to also be applied to folders specified in the COPYTO option. Use the ERASEFILES_REMOTE option if you want different retention period for remote folders.

E.g.

EXEC master..sqlbackup '-sql "BACKUP ... TO DISK = [e:\backups\<AUTO>] WITH COPYTO = [\\fileserver\backups], ERASEFILES = 5, FILEOPTIONS = 1" '

will delete all relevant files older than 5 days in the e:\backups and \\fileserver\backups folders.

EXEC master..sqlbackup '-sql "BACKUP ... TO DISK = [e:\backups\<AUTO>] WITH COPYTO = [\\fileserver\backups], ERASEFILES = 5, ERASEFILES_REMOTE = 7, FILEOPTIONS = 1" '

will delete all relevant files older than 5 days in the e:\backups folder, and files older than 7 days in the \\fileserver\backups folder.

morne3107

The code you gave me is great, but how do I add this to the Interface. When I get to step 3 out of 5, I have an option to enter the folder location, and below that I have the option to copy backup to network with the path location. What do I add where? Please can you split this into 2 parts for me, one for the local location, and then another part for the network location.

I need to delete the network backup after "X" days, however, it should be the same value as the local backups. so the deletion days would not differ between local and network.

Please advise?

petey

The GUI does not allow you to set the FILEOPTIONS nor ERASEFILES_REMOTE options.

You will need to create the backup job first using the available options. Once the job has been created, use Query Analyzer or Management Studio, open that SQL Server Agent job, and edit the syntax to include the FILEOPTIONS and ERASEFILES_REMOTE options as required.

swirl80

can anyone please tell me why this does not work:

EXEC master..sqlbackup N'-SQL "BACKUP DATABASE [DBName] TO DISK = ''d:\backup\<AUTO>.sqb'' 
				WITH COPYTO= ''\\backupsrv\Servername'', 
				NAME = ''Database (DBName) Full'',
                        ERASEFILES = 3,                         
                        FILEOPTIONS = 1,
                        ERASEFILES_REMOTE = 3
                        " -E '

I get the error:

SQL Backup v5.2.0.2825

---

Syntax error: 'ERASEFILES_REMOTE' after ','

If i remove ERASEFILES_REMOTE then the script will run BUT it doesn't remove the relevant files from either the local folder OR the network.

Also, does anyone have a list of what the options of FILEOPTIONS actually are? I know they're from 1-7 but can't find anything that tells me what each value represents....

Running v5.2 on sql server 2005 (Win 2k3 server)

many thanks

swirl80

just found the info that say's ERASEFILES_REMOTE was new in v5.3 :shock: so now installed v5.4 but i'm still getting the same error...

This still doesn't explain my issue with the erasefiles = 3 not removing any backups for that DB older than 3 days. I've even tried it at 3h but still no luck....

any ideas??

Eliza30

Network Backup enterprise network is one of the basic components of conducting a successful business. Small offices and businesses at home and are hungry for the best data backup and disaster recovery capability as soon as possible. Not to mention big business, where downtime is a disaster in itself and can result in a catastrophic reduction in revenue and customer dissatisfaction. We see a variety of backup solutions for home and small office, but not so easy to find good software for server and network backup. Before making a decision, you should review the existing solutions and the facilities they offer. Then choose the features that meet their needs and, finally, estimate the amount of money you are willing to spend for the backup solution.


network tools