SQL ReTry Logic

Question

Post reply

SQL ReTry Logic

larso105

SSC Rookie

Points: 33
More actions
September 22, 2012 at 5:31 pm

#393239

Hello All ,
I have a process where I need to restore a reporting database but sometimes there are reports running and when the script drop the active users they reconnect before the restore can begin. I would like to know if there is a way to add retry logic to the script if the restore fails to go back and start the script over again? Thanks

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

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Have you tried setting the database to using 'RESTRICTED_USER' then killing off the spids ?

Clear Sky SQL
My Blog[/url]

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

This technique works for me. It guarantees success when there are apps are running with sysadmin-level permissions:

USE YourDatabase;

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- forcefully kills all user sessions that are not yours and rolls back open transactions

RESTORE DATABASE ...

The fact that you change into the database context before setting it to SINGLE_USER makes sure that user is you 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

Done the same sort of thing, but when there are apps that fire continously then they can get the single user connection .

Had to re-sort to pausing the service 🙂

Clear Sky SQL
My Blog[/url]

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

Dave Ballantyne (9/23/2012)
Done the same sort of thing, but when there are apps that fire continously then they can get the single user connection .
Had to re-sort to pausing the service 🙂

That's why you change into the database before setting it to SINGLE_USER 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 5

True, maybe im thinking of snapshots then, have to be out of the database to rollback to a snaphot.

Complete pita 😉

Clear Sky SQL
My Blog[/url]

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

I could see that for restoring a snapshot in a production rollback scenario.

To be fair and complete you have to be out of the DB to restore WITH REPLACE too but it's not likely that anything will get in between the USE master and the RESTORE DATABASE in a scenario (likely non-prod or DR) where you're restoring WITH REPLACE:

USE YourDatabase;

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- forcefully kills all user sessions that are not yours and rolls back open transactions

USE master; -- must do, not explicitly shown before

RESTORE DATABASE ...

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato