Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL ReTry Logic Expand / Collapse
Author
Message
Posted Saturday, September 22, 2012 5:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:24 PM
Points: 1, Visits: 237
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
Post #1363180
Posted Sunday, September 23, 2012 2:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:03 AM
Points: 1,949, Visits: 8,303
Have you tried setting the database to using 'RESTRICTED_USER' then killing off the spids ?




Clear Sky SQL
My Blog
Kent user group
Post #1363204
Posted Sunday, September 23, 2012 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1363238
Posted Sunday, September 23, 2012 10:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:03 AM
Points: 1,949, Visits: 8,303
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
Kent user group
Post #1363244
Posted Sunday, September 23, 2012 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1363246
Posted Sunday, September 23, 2012 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:03 AM
Points: 1,949, Visits: 8,303
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
Kent user group
Post #1363257
Posted Sunday, September 23, 2012 11:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1363259
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse