Copy backup to network auto deletion

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

Copy backup to network auto deletion

Postby morne3107 » Mon Jun 23, 2008 2:56 pm

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?
morne3107
 
Posts: 4
Joined: Tue Jun 10, 2008 1:18 pm
Location: JHB

Postby petey » Mon Jun 23, 2008 3:32 pm

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.
Code: Select all
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.

Code: Select all
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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Using the Interface

Postby morne3107 » Tue Jun 24, 2008 10:05 am

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?
morne3107
 
Posts: 4
Joined: Tue Jun 10, 2008 1:18 pm
Location: JHB

Postby petey » Tue Jun 24, 2008 10:17 am

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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby swirl80 » Tue Jul 14, 2009 4:57 pm

can anyone please tell me why this does not work:
Code: Select all
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
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby swirl80 » Tue Jul 14, 2009 5:02 pm

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??
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby Eliza30 » Thu Jul 07, 2011 6:06 am

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
Eliza30
 
Posts: 1
Joined: Thu Jul 07, 2011 6:04 am


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests