Restore Database if Job fails

Question

Post reply

Restore Database if Job fails

erics44

SSCertifiable

Points: 6311
More actions
June 19, 2013 at 4:44 am

#276476

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

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

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

Not sure what exactly you need here

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

erics44 SSCertifiable Points: 6311 More actions · Answer 2

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

Abu Dina SSChampion Points: 14155 More actions · Answer 3

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

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.

kevaburg SSCoach Points: 18131 More actions · Answer 5

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?

Lowell SSC Guru Points: 323518 More actions · Answer 6

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!

kevaburg SSCoach Points: 18131 More actions · Answer 7

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.