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

Chrish

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.

petey

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.

Chrish

Thanks Pete,

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

Regards,

Chris.

Chrish

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.

petey

No, CopyTool doesn't have the option to delete unwanted backup files.

Chrish

Hi Peter,

Thanks for confirming this for me.

Regards,

Chris.

ChrisAVWood

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

Chrish

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.

ChrisAVWood

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

Chrish

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.

ChrisAVWood

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

petey

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.

Chrish

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.

jhboricua

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.