Splitting Up Backup and Copy Functions

JoshDBGuy · August 4, 2015 2:41PM

Is there a way to split up the backup and copy functions using Redgate? I can do backups myself and then use SQL/Powershell to do copies to network locations but I'd rather use redgate. Is this possible?

petey · August 4, 2015 8:25PM

What is the reason for wanting to split the backup and copy functions?

rayherring · January 27, 2016 12:19PM

I would also like to have the backup and copy in two steps.
I have a number of production jobs that I have to disable prior to starting the Full Backup. I have to do this because the disk I/O contention on the data drives was significantly slowing both production processing and backup throughput. The Full Backup was taking > 24 hours. With Jobs disabled it now takes 3 - 4 hours.
As soon as the Full Backup is completed the Production Jobs can be restarted.
The file copy to off-storage can then run without impact.

Currently I have separate Backup job steps to

Disable Selected Production Jobs Run Backup Enable Selected Production Jobs Copy Backups to File Server (Power Shell)

petey · January 27, 2016 9:09PM

There is an option to place the backup file(s) to be copied into a separate process, so that the backup job completes as soon as the database backup has completed. However, the queued copy process will be picked up a minute or two later, thus you have no control over the exact time the copying is performed.

To enable this option, use the USEQUEUEDCOPY keyword e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO ... WITH COPYTO = ..., USEQUEUEDCOPY"'

Splitting Up Backup and Copy Functions

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk