Copy Backup File

Question

Copy Backup File

VastSQL

SSC-Dedicated

Points: 36097
More actions
February 2, 2019 at 10:45 pm

#370398

Hi Experts,
We have backup job taken every 20 mins(trn) every 1 hour(diff) and weekly full. These backups are taken to local disk and then copied to network by a batch file which is running ROBOCOPY. We have recently faced the issue where the copy job was stuck(the batch file is run by Windows scheduled task ) . The job runs every 10 mins.
Is there a better way to copy the files ,is there a tool to achieve this (free & Paid).
Thanks in Advance.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

without going through all possible options (google can help there) why do you have the copy job on Windows Schedule instead of a SQL server job?
It would be better in my opinion to have both backup steps and copy to network on a single job, 2 steps.
Or even a SSIS package doing backup and copying the corresponding file as the second task. easy foreach database loop - backup ->copy file

And robocopy is quite robust so just from a "copy file" tool although there may be other options I'm not sure it would warrant replacement just from a copy file point of view.

and how and how is the schedule getting stuck out of curiosity?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

VastSQL - Saturday, February 2, 2019 10:45 PM
Hi Experts,
We have backup job taken every 20 mins(trn) every 1 hour(diff) and weekly full. These backups are taken to local disk and then copied to network by a batch file which is running ROBOCOPY. We have recently faced the issue where the copy job was stuck(the batch file is run by Windows scheduled task ) . The job runs every 10 mins.
Is there a better way to copy the files ,is there a tool to achieve this (free & Paid).
Thanks in Advance.

Just stumbled across a product by Synametrics (.com) that might do the job, not tried it yet.
😎

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

VastSQL - Saturday, February 2, 2019 10:45 PM
Hi Experts,
We have backup job taken every 20 mins(trn) every 1 hour(diff) and weekly full. These backups are taken to local disk and then copied to network by a batch file which is running ROBOCOPY. We have recently faced the issue where the copy job was stuck(the batch file is run by Windows scheduled task ) . The job runs every 10 mins.
Is there a better way to copy the files ,is there a tool to achieve this (free & Paid).
Thanks in Advance.

Why can't you just backup directly to the final destination?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

barsuk SSChampion Points: 12280 More actions · Answer 4

Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

Eirikur Eiriksson - Sunday, February 3, 2019 7:35 AM
VastSQL - Saturday, February 2, 2019 10:45 PM
Hi Experts,
We have backup job taken every 20 mins(trn) every 1 hour(diff) and weekly full. These backups are taken to local disk and then copied to network by a batch file which is running ROBOCOPY. We have recently faced the issue where the copy job was stuck(the batch file is run by Windows scheduled task ) . The job runs every 10 mins.
Is there a better way to copy the files ,is there a tool to achieve this (free & Paid).
Thanks in Advance.
Just stumbled across a product by Synametrics (.com) that might do the job, not tried it yet.
😎

Thanks

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

frederico_fonseca - Sunday, February 3, 2019 6:35 AM
without going through all possible options (google can help there) why do you have the copy job on Windows Schedule instead of a SQL server job?
It would be better in my opinion to have both backup steps and copy to network on a single job, 2 steps.
Or even a SSIS package doing backup and copying the corresponding file as the second task. easy foreach database loop - backup ->copy file
And robocopy is quite robust so just from a "copy file" tool although there may be other options I'm not sure it would warrant replacement just from a copy file point of view.
and how and how is the schedule getting stuck out of curiosity?

Using windows scheduler only because even if in case SQL Server goes down windows scheduler will be up and will do the job.

Currently using ROBOCOPY

when large backups are getting started the job is waiting it to complete.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

barsuk - Monday, February 4, 2019 11:21 PM
Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.

The copy job runs every 15 mins and some backup takes more than hour to complete and that was the issue

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

VastSQL - Tuesday, February 5, 2019 12:19 AM
barsuk - Monday, February 4, 2019 11:21 PM
Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.
The copy job runs every 15 mins and some backup takes more than hour to complete and that was the issue

If you have compressed backups available, you should use those. They really do make a performance difference. You might also want to check out some of the lesser known settings for BACKUP, like the number of buffers used, etc. They can also make a pretty decent difference.

I would also consider avoiding the method of doing a backup to one spot and then using RoboCopy or anything else to copy from there to their final resting place. Consider backing up directly to the final place, especially if the drives being used for the current backups is on the same SAN/drives as the databases themselves.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

Jeff Moden - Tuesday, February 5, 2019 7:02 AM
VastSQL - Tuesday, February 5, 2019 12:19 AM
barsuk - Monday, February 4, 2019 11:21 PM
Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.
The copy job runs every 15 mins and some backup takes more than hour to complete and that was the issue
If you have compressed backups available, you should use those. They really do make a performance difference. You might also want to check out some of the lesser known settings for BACKUP, like the number of buffers used, etc. They can also make a pretty decent difference.
I would also consider avoiding the method of doing a backup to one spot and then using RoboCopy or anything else to copy from there to their final resting place. Consider backing up directly to the final place, especially if the drives being used for the current backups is on the same SAN/drives as the databases themselves.

Further on Jeff's good points, consider striping the backups if you have multiple IO paths.
😎

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 10

Jeff Moden - Tuesday, February 5, 2019 7:02 AM
VastSQL - Tuesday, February 5, 2019 12:19 AM
barsuk - Monday, February 4, 2019 11:21 PM
Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.
The copy job runs every 15 mins and some backup takes more than hour to complete and that was the issue
If you have compressed backups available, you should use those. They really do make a performance difference. You might also want to check out some of the lesser known settings for BACKUP, like the number of buffers used, etc. They can also make a pretty decent difference.
I would also consider avoiding the method of doing a backup to one spot and then using RoboCopy or anything else to copy from there to their final resting place. Consider backing up directly to the final place, especially if the drives being used for the current backups is on the same SAN/drives as the databases themselves.

Thanks Jeff for your valuable points.. Will check those

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 11

Eirikur Eiriksson - Tuesday, February 5, 2019 7:25 AM
Jeff Moden - Tuesday, February 5, 2019 7:02 AM
VastSQL - Tuesday, February 5, 2019 12:19 AM
barsuk - Monday, February 4, 2019 11:21 PM
Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.
The copy job runs every 15 mins and some backup takes more than hour to complete and that was the issue
If you have compressed backups available, you should use those. They really do make a performance difference. You might also want to check out some of the lesser known settings for BACKUP, like the number of buffers used, etc. They can also make a pretty decent difference.
I would also consider avoiding the method of doing a backup to one spot and then using RoboCopy or anything else to copy from there to their final resting place. Consider backing up directly to the final place, especially if the drives being used for the current backups is on the same SAN/drives as the databases themselves.
Further on Jeff's good points, consider striping the backups if you have multiple IO paths.
😎

Thanks Erikku, Now all backup to one back drive

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

VastSQL - Monday, February 11, 2019 5:17 AM
Eirikur Eiriksson - Tuesday, February 5, 2019 7:25 AM
Jeff Moden - Tuesday, February 5, 2019 7:02 AM
VastSQL - Tuesday, February 5, 2019 12:19 AM
barsuk - Monday, February 4, 2019 11:21 PM
Was backup completed? you need to ensure that backup is completed before moving to the Robocopy step.
The copy job runs every 15 mins and some backup takes more than hour to complete and that was the issue
If you have compressed backups available, you should use those. They really do make a performance difference. You might also want to check out some of the lesser known settings for BACKUP, like the number of buffers used, etc. They can also make a pretty decent difference.
I would also consider avoiding the method of doing a backup to one spot and then using RoboCopy or anything else to copy from there to their final resting place. Consider backing up directly to the final place, especially if the drives being used for the current backups is on the same SAN/drives as the databases themselves.
Further on Jeff's good points, consider striping the backups if you have multiple IO paths.
😎
Thanks Erikku, Now all backup to one back drive

Hopefully that location is on a different physical box than the database files themselves.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)