Stop SQL Express service

[venerable bede]

Hi All.

I have some code which backs up a sql express database using the Microsoft.SqlServer.Management.Smo namespace.

For neatness I would like to first check to see if the service is running and stop it, perform my backup, then restart it.

I cant figure this one out. Any ideas my lovelies ?


TIA

[chris128]

Here's an example I just knocked up that stops the print spooler service if its running, I'm sure you can adapt it to look for the SQL Express service name instead of the print spooler

Dim Spoolercontroller As New ServiceProcess.ServiceController
Spoolercontroller.MachineName = "."
Spoolercontroller.ServiceName = "Spooler"
 
If Spoolercontroller.Status = ServiceProcess.ServiceControllerStatus.Running Then
  Spoolercontroller.Stop()
End If

You could just drag a ServiceControl component from the toolbox onto your form to do the first 3 lines of my code in the designer but just thought it would be easier to show you in code.
The "." that is used for the MachineName property just means "this computer" so obviously if your program was running on a different machine to where the SQL service was running then you would need to change this.

Hope that helps

[MaximilianMayrhofer]

MSSQL$SQLEXPRESS is the name

[chris128]

Isnt that only if you used the default instance name though? (I cant remember if you even get a choice in SQL Express or if its only full blown SQL Server)
Like if you named your SQL instance "testing" then the service would be named MSSQL$Testing wouldnt it?

[bmahler]

You can enumerate all servers to find the instance name. Here is an example function to find out the local instance on your machine

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Instance As String = GetLocalSQLService()
    End Sub

    Private Function GetLocalSQLService() As String
        Dim dt As DataTable = Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
        For Each dr As DataRow In dt.Rows
            If dr.Item("ServerName") = My.Computer.Name Then
                Return "MSSQL$" & dr.Item("InstanceName")
            End If
        Next
        Return String.Empty
    End Function