SQL ReTry Logic

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL ReTry Logic

SQL ReTry Logic

Rate Topic

Display Mode

Topic Options

Author

Message

larso105

Posted Saturday, September 22, 2012 5:31 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:07 PM
Points: 1, Visits: 191

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

Dave Ballantyne

Posted Sunday, September 23, 2012 2:35 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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

opc.three

Posted Sunday, September 23, 2012 9:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 6,713, Visits: 11,748

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 Smile

__________________________________________________________________________________________________
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

Dave Ballantyne

Posted Sunday, September 23, 2012 10:14 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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

opc.three

Posted Sunday, September 23, 2012 10:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 6,713, Visits: 11,748

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 Wink

Post #1363246

Dave Ballantyne

Posted Sunday, September 23, 2012 11:36 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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

Complete pita Wink

Clear Sky SQL
My Blog
Kent user group

Post #1363257

opc.three

Posted Sunday, September 23, 2012 11:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 6,713, Visits: 11,748

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 ...

Post #1363259

« Prev Topic | Next Topic »

Permissions