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: Wednesday, April 02, 2014 8:10 AM
Points: 1, Visits: 229
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: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1363238
Posted Sunday, September 23, 2012 10:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1363246
Posted Sunday, September 23, 2012 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1363259
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse