Can I do the network copy job after all DB's are backed up?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Can I do the network copy job after all DB's are backed up?

Postby Chrish » Tue Feb 07, 2012 1:26 pm

I am currently using SQL Backup Pro 6 to back up multiple production databases with the COPYTO command to perform a network copy. This process backs up, then copies each database individually.

This is causing me an issue as it takes hours to copy across a couple of our databases across the network. This means that some of the backups are still running during working hours which in turn is causing system issues.

Is it possible to get SQL Backup Pro to perform ALL database backups before the network copy job is envoked? This would ensure that all bakups are completed well before the working day starts, leaving just the network copy job to complete.

Any help much appreciated. Please let me know if you need any further information.

Regards,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Postby petey » Tue Feb 07, 2012 2:13 pm

It isn't possible to do what you want with SQL Backup. What you could do is use the CopyTool utility available here, to copy the files, perhaps setting it up as a job step after the backup job step.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2229
Joined: Sun Apr 24, 2005 12:34 pm

Postby Chrish » Tue Feb 07, 2012 3:21 pm

Thanks Pete,

I'll try the suggested utility, sounds like it will do exactly what I want :)

Regards,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Anyway to only keep the last 2 backup files

Postby Chrish » Tue Feb 07, 2012 3:34 pm

Hi Peter,

I've just had a look a the CopyTool readme and it doesn't look like there a way to remove files older than a specified date from the destination or only keep the last X number of backups (something that can be set in SQL Backup Pro).

Is there a way of doing this using the CopyTool utility? We curently append the datetime stamp onto the end our backup file names so no 2 backups are named the same so we cannot utilise the /OVERWRITE option.

If it's not possible using the CopyTool then I'll look at setting up a seperate process on the destination server to clear down the old backup files.

Many thanks,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Postby petey » Tue Feb 07, 2012 3:40 pm

No, CopyTool doesn't have the option to delete unwanted backup files.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2229
Joined: Sun Apr 24, 2005 12:34 pm

Postby Chrish » Tue Feb 07, 2012 4:22 pm

Hi Peter,

Thanks for confirming this for me.

Regards,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Postby ChrisAVWood » Tue Feb 07, 2012 4:30 pm

Chris,

SQLBackup gives you the ability to delete before or after the backup is taken. I am assuming that your cleanup on the attached disk is at least a few hours after you have taken the backup that you copy to a network location.

You should still be able to take your backup to disk with a delete and then run the CopyTool. I haven't used the CopyTool or seen how it works but I am sure you can come up with a combination that can work for you?

HTH

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby Chrish » Tue Feb 07, 2012 5:24 pm

Hi Chris,

Thanks for the suggestion. I think that SQL Backup will only allow me to effect the local copy of the backup in terms of deleting and not the network copy as I can't use the network copy functionality in SQL Backup (as this copies the file after each backup - my original problem).

Since I have the datetime appended to the file name then I can't simply overwrite what's in the network location as they will be different. So my problem is how to only keep 2 days worth of backups on the network location.

I'm sure there will be a way to do this, just need to think on it a little more.

Thanks again,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Postby ChrisAVWood » Tue Feb 07, 2012 5:45 pm

Chris,

have you seen the ERASEFILES_REMOTE option to delete the backups on a network share? I haven't used it but I am sure it will work. You just need to planout a strategy.

Good luck

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby Chrish » Tue Feb 07, 2012 5:50 pm

Hi Chris,

Thanks for the quick reply. Just had a look at ERASEFILES_REMOTE option and it certainly looks promising. Not got any time now to try it out but I will try it out later.

Looks as though this may well be the answer I was looking for. I'll let you know how I get on.

Thanks alot,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Postby ChrisAVWood » Tue Feb 07, 2012 6:06 pm

Lastly Chris.

We are using 6.5.2.9 and V 7 is due out shortly. I would at least get to 6.5.2.9 as it has some fixes to problems in 6.5.1.9. Petey should be able to help you get that version.

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby petey » Wed Feb 08, 2012 1:35 pm

ERASEFILES_REMOTE specifies the retention interval for files located on remote shares, be it in the primary backup folder, COPYTO folder and MOVETO folder. If your backup does not use any remote shares in any of the above 3 parameters, it has no effect.

As you are leaving out the COPYTO option to copy your backup files to the remote share as they take too long, the ERASEFILES_REMOTE option would not be of any help to you.

As for the patches, I would suggest contacting Red Gate support indicating the issue you are facing, and they would then be able to determine if the latest patch version addresses your issue.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2229
Joined: Sun Apr 24, 2005 12:34 pm

Postby Chrish » Wed Feb 08, 2012 6:16 pm

Hi Peter,

Thanks for the info. That will save me some time trying it out if it's not going to work.

I'll continue to investigate other options.

Thanks again,

Chris.
Chrish
 
Posts: 9
Joined: Thu Jun 30, 2011 4:45 pm
Location: Cambridgeshire

Postby jhboricua » Fri Feb 17, 2012 11:11 pm

If you're not squeamish about scripting, you could wrap everything into a powershell script that does:

1. Backup using Redgate SQL Backup.
2. Copy files after backup is complete.
3. Do the pruning of older files from the network share.

I use a powershell script for #3 as I found that having the SQLBackup utility doing this was unreliable.
jhboricua
 
Posts: 29
Joined: Tue Mar 15, 2011 3:54 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests