Home Forums SQL Server 2014 Development - SQL Server 2014 Script to stop service using XP_CMDSHELL, restore full and differential .bak files in a single script RE: Script to stop service using XP_CMDSHELL, restore full and differential .bak files in a single script

  • I'm not the one with the keys to the Web Services. When it happened to me and one of them grabbed the single connection, I had to get other several other people involved because as soon as you'd shut one Web Service down, another would pick up on the connection. It also meant shutting down some services that used other databases for different processes and they were as poorly setup as many of the jobs on SQL Server where you couldn't easily shut down only those parts that were affected by the restore of the one database. That meant that some functionality that shouldn't have needed to "go down" for a long term, did.

    I'm also not absolutely positive that immediately switching back to multi- or restricted user would save you if the timing were as unfortunate as what I experienced but it is a command that relies on nothing else and is very fast to execute. Think of it more as hedging a bet. If nothing else, the commented line in the code is a reminder to anyone that may be stepping through the code to troubleshoot (or whatever) to be keenly aware not to lose their SSMS session after setting the database to the single user mode.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)