Restore Database if Job fails

  • Hi

    Is there a way of starting a database restore if an agent job fails?

    Thanks

  • Not sure what exactly you need here

    Want to restore a database if an Agent job fails? What does the Agent job do?

  • it clears and then repopulates a database, if it fails then there will be no data available

    so i want to restire the database back to how it was before the job started

  • Isn't it just a case of adding an On Failure Action in the job and create an additional step in your job to perform the database restore?

    BTW, why not take a snapshot of the DB before the job runs then use that to restore if job fails?

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • erics44 (6/19/2013)


    it clears and then repopulates a database, if it fails then there will be no data available

    so i want to restire the database back to how it was before the job started

    I agree with Abu - Look at the On Failure Action.

    Create an extra step in the job that includes the restore command, change the On Failure Action option on your original job step execute the step that contains the restore command.

  • I would be more inclined to send an Email notification to an operator if the job fails. Do you really want to trust the restoration to an automated job if something goes wrong?

  • it depends on why the job fails i guess;

    one possibility might be to have the job use SET XACT_ABORT ON, and put it in a transaction; then if an error occurs, it would rollback anyway, and no need to restore.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (6/19/2013)


    it depends on why the job fails i guess;

    one possibility might be to have the job use SET XACT_ABORT ON, and put it in a transaction; then if an error occurs, it would rollback anyway, and no need to restore.

    Actually, I think that combined with an Email notification might be what he is looking for. He needs to know it failed so he can troubleshoot and restart the job as required.

Viewing 8 posts - 1 through 7 (of 7 total)

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