Download Safari Books Online apps: Apple iOS | Android

Entire Site

Safari Books Online

    Free Trial

    Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.


    • Create BookmarkCreate Bookmark
    • Create Note or TagCreate Note or Tag
    • DownloadDownload
    • PrintPrint
    Share this Page URL
    Help

    5.3. SERVER SHUTDOWN

    5.3.1. MSS 2000

    Use the following steps to gracefully shut down an SQL Server in MSS 2000.

    1.
    Broadcast a shutdown message. (See BOL: Broadcasting a Shutdown Message). Execute the following in a Windows Command Window (DOS window).

    Code View: Scroll / Show All
    C:\> net send   /users   "SQL Server AMY is going down for maintenance in 20 minutes.
   Please finish work and disconnect asap."

    Note: net send /user msg -- sends msg to all users connected to the sending host. net send /domain msg -- sends msg to all users in the domain

    2.
    Open an Enterprise Manager and Query Analyzer as sa. This must be done before pausing the server since you can't connect after pausing.

    3.
    PAUSE the SQL Server.

    You can use Enterprise Manager or Service Manager.

    According to Books Online, when you pause Microsoft SQL Server, users who are connected to the server can continue, but new connections are not allowed.

    4.
    Broadcast a Final Shutdown Message when the allotted time has expired.

    net send /users "SQL Server is going down immediately."

    5.
    Set each user database you want to work on to RESTRICTED_USER mode (dbo and sa), probably by a script. For example:

    SQL
    												ALTER DATABASE  pubs
												SET RESTRICTED_USER  WITH ROLLBACK IMMEDIATE
												ALTER DATABASE  northwind
												SET RESTRICTED_USER  WITH ROLLBACK AFTER 60
												...


    “IMMEDIATE” causes open connections to be terminated immediately, “AFTER 60” or “AFTER 60 SECONDS” terminates open connections after 60 seconds.

    6.
    STOP the SQL Server.

    You can use Enterprise Manager or Service Manager.

    7.
    START the SQL Server.

    It is now in RESTRICTED_USER mode allowing you to do maintenance.

    8.
    Do your work on the server.

    9.
    When finished, set each user database back to multiuser mode, possibly via an sql script.

    SQL
    												ALTER DATABASE  pubs       SET MULTI_USER
												ALTER DATABASE  northwind  SET MULTI_USER
												...


    10.
    Broadcast a message that SQL Server is back up.

    C:\>  net send   /domain   "SQL Server AMY is operational."


      

    You are currently reading a PREVIEW of this book.

                                                                                            

    Get instant access to over
    $1 million worth of books and videos.

      

    Start a Free Trial