John Mendez

I wrote this code a while back when I needed a way for some people to restart a SQL Server when the IT team wasn't around.  This code was used in an ASP.NET app, but can easily be used in a windows form if necessary.

Note:  If you are going to use it in an ASP.NET app please make sure the application has the correct security context so it can actually restart the SQL Server services.  For example this code I used I made sure the app ran under and Application Pool identity that had access to restart the service.

This code was written in VB so sorry to those C# folks!

Ths is in the handler for btnRestart.Click

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Wmi
Imports Microsoft.SqlServer.Management.Common

Protected Sub btnRestart_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRestart.Click

        Dim str As String = Nothing

        'Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
        Dim mc As ManagedComputer
        mc = New ManagedComputer(txtServer.Text)

        'Iterate through each service registered with the WMI Provider.
        Dim svc As Service
        Dim svcAgent As Service
        For Each svc In mc.Services
            Response.Write(svc.Name & "<br />")
        Next

        'Reference the Microsoft SQL Server services.
        svc = mc.Services("MSSQLSERVER")
        svcAgent = mc.Services("SQLSERVERAGENT")

        Try
            'Stop the service if it is running and report on the status continuously until it has stopped.
            If svc.ServiceState = ServiceState.Running Then
              
                svc.Stop()
                Do Until svc.ServiceState = ServiceState.Stopped
                    str = str & String.Format("{0} service state is {1} on {2}<br />", svc.Name, svc.ServiceState, txtServer.Text)
                    Label2.Text = str
                    svc.Refresh()
                Loop

                'Start the service and report on the status continuously until it has started.
                svc.Start()
                Do Until svc.ServiceState = ServiceState.Running
                    str = Nothing
                    str = str & String.Format("{0} service state is {1} on {2}<br />", svc.Name, svc.ServiceState, txtServer.Text)
                    Label3.Text = str
                    svc.Refresh()
                Loop

                Label3.Text = str & String.Format("{0} service state is {1} on {2}<br />", svc.Name, svc.ServiceState, txtServer.Text)

                svcAgent.Start()
                If svcAgent.ServiceState = ServiceState.Running Then
                    Label4.Text = String.Format("{0} service state is {1} on {2}", svcAgent.Name, svcAgent.ServiceState, txtServer.Text)
                End If
                Do Until String.Format("{0}", svcAgent.ServiceState) = "Running"
                    str = str & String.Format("{0} service state is {1} on {2}<br />", svcAgent.Name, svcAgent.ServiceState, txtServer.Text)
                    Label4.Text = str
                    svcAgent.Refresh()
                Loop

            Else
                Label2.Text = "SQL SERVER IS NOT RUNNING."
            End If

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try

    End Sub

Please notice that it takes the value from txtServer.Text.  This is the text box on the page that people will enter in the server name.

The one bug in this code is that since this is during one post back you actually don't see the label updating itself because it's not threading.

This usually takes about 1-2 minutes to perform depending on the server.

Again, this is useful for when you don't want to have to restart the server yourself and can have either someone beneath you do it, or someone you trust.

ASP.NET, Microsoft SQL asp.net, sql