Ola Hallengreen Backup Retry
Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.
backup ola-hallengren
asked Jan 23 at 14:23
JessicaJessica
211
add a comment |
Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.
backup ola-hallengren
asked Jan 23 at 14:23
JessicaJessica
211
2
You could raise a feature request on the repo github.com/olahallengren/sql-server-maintenance-solution/issues
– James Anderson
Jan 23 at 14:44
add a comment |
Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.
backup ola-hallengren
asked Jan 23 at 14:23
JessicaJessica
211
Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.
backup ola-hallengren
backup ola-hallengren
asked Jan 23 at 14:23
JessicaJessica
211
asked Jan 23 at 14:23
JessicaJessica
211
asked Jan 23 at 14:23
JessicaJessica
211
asked Jan 23 at 14:23
JessicaJessica
211
asked Jan 23 at 14:23
JessicaJessica
211
211
2
You could raise a feature request on the repo github.com/olahallengren/sql-server-maintenance-solution/issues
– James Anderson
Jan 23 at 14:44
add a comment |
2
You could raise a feature request on the repo github.com/olahallengren/sql-server-maintenance-solution/issues
– James Anderson
Jan 23 at 14:44
2
2
You could raise a feature request on the repo github.com/olahallengren/sql-server-maintenance-solution/issues
– James Anderson
Jan 23 at 14:44
You could raise a feature request on the repo github.com/olahallengren/sql-server-maintenance-solution/issues
– James Anderson
Jan 23 at 14:44
add a comment |
2 Answers
2
active
oldest
votes
Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.
What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.
You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.
From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
add a comment |
You can query the dbo.CommandLog table for a successful backup (since you are using Ola's backup solution) or even msdb history to double check that the backup is indeed successful or not.
Based on that, if the backup failed, then you can retry the backup.
There was an issue already filed (it was for backup to Azure URL- but MS released a hot fix for handling intermittent failures - so the issue is closed), but the problem with handling server side errors is that using error variable - it will give you the last error only.
Also, refer to Three cases where Ola Hallengren's Maintenance Solution won't backup a database
Now if you use dbatools (which I highly recommend)
- Still continue using Ola's solution. You should use
Invoke-DbaQueryand in query use-Query 'EXECUTE dbo.DatabaseBackup ..'along with-EnableException SomeExceptionErrorMessageswitch. Then you can just use the variableSomeExceptionErrorMessageto check for errors or warnings and implement a retry logic. - Switch to use dbatools native -
Backup-DbaDatabasewith-EnableException SomeExceptionErrorMessageswitch.
Alternatively, you can just add an extra step in your sql agent job to check for the commandLog or msdb for successful backup and based on that you can call your backup job again.
answered Jan 23 at 15:33
KinKin
53.8k481191
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
1
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go withbackup-dbadatabaseand based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.
– Kin
Jan 23 at 15:57
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227874%2fola-hallengreen-backup-retry%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.
What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.
You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.
From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
add a comment |
Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.
What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.
You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.
From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
add a comment |
Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.
What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.
You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.
From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.
What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.
You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.
From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
answered Jan 23 at 14:38
ShaulinatorShaulinator
2,4781622
2,4781622
add a comment |
add a comment |
You can query the dbo.CommandLog table for a successful backup (since you are using Ola's backup solution) or even msdb history to double check that the backup is indeed successful or not.
Based on that, if the backup failed, then you can retry the backup.
There was an issue already filed (it was for backup to Azure URL- but MS released a hot fix for handling intermittent failures - so the issue is closed), but the problem with handling server side errors is that using error variable - it will give you the last error only.
Also, refer to Three cases where Ola Hallengren's Maintenance Solution won't backup a database
Now if you use dbatools (which I highly recommend)
- Still continue using Ola's solution. You should use
Invoke-DbaQueryand in query use-Query 'EXECUTE dbo.DatabaseBackup ..'along with-EnableException SomeExceptionErrorMessageswitch. Then you can just use the variableSomeExceptionErrorMessageto check for errors or warnings and implement a retry logic. - Switch to use dbatools native -
Backup-DbaDatabasewith-EnableException SomeExceptionErrorMessageswitch.
Alternatively, you can just add an extra step in your sql agent job to check for the commandLog or msdb for successful backup and based on that you can call your backup job again.
answered Jan 23 at 15:33
KinKin
53.8k481191
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
1
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go withbackup-dbadatabaseand based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.
– Kin
Jan 23 at 15:57
add a comment |
You can query the dbo.CommandLog table for a successful backup (since you are using Ola's backup solution) or even msdb history to double check that the backup is indeed successful or not.
Based on that, if the backup failed, then you can retry the backup.
There was an issue already filed (it was for backup to Azure URL- but MS released a hot fix for handling intermittent failures - so the issue is closed), but the problem with handling server side errors is that using error variable - it will give you the last error only.
Also, refer to Three cases where Ola Hallengren's Maintenance Solution won't backup a database
Now if you use dbatools (which I highly recommend)
- Still continue using Ola's solution. You should use
Invoke-DbaQueryand in query use-Query 'EXECUTE dbo.DatabaseBackup ..'along with-EnableException SomeExceptionErrorMessageswitch. Then you can just use the variableSomeExceptionErrorMessageto check for errors or warnings and implement a retry logic. - Switch to use dbatools native -
Backup-DbaDatabasewith-EnableException SomeExceptionErrorMessageswitch.
Alternatively, you can just add an extra step in your sql agent job to check for the commandLog or msdb for successful backup and based on that you can call your backup job again.
answered Jan 23 at 15:33
KinKin
53.8k481191
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
1
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go withbackup-dbadatabaseand based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.
– Kin
Jan 23 at 15:57
add a comment |
You can query the dbo.CommandLog table for a successful backup (since you are using Ola's backup solution) or even msdb history to double check that the backup is indeed successful or not.
Based on that, if the backup failed, then you can retry the backup.
There was an issue already filed (it was for backup to Azure URL- but MS released a hot fix for handling intermittent failures - so the issue is closed), but the problem with handling server side errors is that using error variable - it will give you the last error only.
Also, refer to Three cases where Ola Hallengren's Maintenance Solution won't backup a database
Now if you use dbatools (which I highly recommend)
- Still continue using Ola's solution. You should use
Invoke-DbaQueryand in query use-Query 'EXECUTE dbo.DatabaseBackup ..'along with-EnableException SomeExceptionErrorMessageswitch. Then you can just use the variableSomeExceptionErrorMessageto check for errors or warnings and implement a retry logic. - Switch to use dbatools native -
Backup-DbaDatabasewith-EnableException SomeExceptionErrorMessageswitch.
Alternatively, you can just add an extra step in your sql agent job to check for the commandLog or msdb for successful backup and based on that you can call your backup job again.
answered Jan 23 at 15:33
KinKin
53.8k481191
You can query the dbo.CommandLog table for a successful backup (since you are using Ola's backup solution) or even msdb history to double check that the backup is indeed successful or not.
Based on that, if the backup failed, then you can retry the backup.
There was an issue already filed (it was for backup to Azure URL- but MS released a hot fix for handling intermittent failures - so the issue is closed), but the problem with handling server side errors is that using error variable - it will give you the last error only.
Also, refer to Three cases where Ola Hallengren's Maintenance Solution won't backup a database
Now if you use dbatools (which I highly recommend)
- Still continue using Ola's solution. You should use
Invoke-DbaQueryand in query use-Query 'EXECUTE dbo.DatabaseBackup ..'along with-EnableException SomeExceptionErrorMessageswitch. Then you can just use the variableSomeExceptionErrorMessageto check for errors or warnings and implement a retry logic. - Switch to use dbatools native -
Backup-DbaDatabasewith-EnableException SomeExceptionErrorMessageswitch.
Alternatively, you can just add an extra step in your sql agent job to check for the commandLog or msdb for successful backup and based on that you can call your backup job again.
answered Jan 23 at 15:33
KinKin
53.8k481191
answered Jan 23 at 15:33
KinKin
53.8k481191
answered Jan 23 at 15:33
KinKin
53.8k481191
answered Jan 23 at 15:33
KinKin
53.8k481191
53.8k481191
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
1
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go withbackup-dbadatabaseand based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.
– Kin
Jan 23 at 15:57
add a comment |
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
1
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go withbackup-dbadatabaseand based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.
– Kin
Jan 23 at 15:57
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure.
– Shaulinator
Jan 23 at 15:53
1
1
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go with
backup-dbadatabase and based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.– Kin
Jan 23 at 15:57
If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go with
backup-dbadatabase and based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.– Kin
Jan 23 at 15:57
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227874%2fola-hallengreen-backup-retry%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
You could raise a feature request on the repo github.com/olahallengren/sql-server-maintenance-solution/issues
– James Anderson
Jan 23 at 14:44