C# .NET - RESTART Sqlserver - Asked By Lokesh M on 16-Feb-10 06:11 AM

Hi,

Is their a way to Restart sql server which is installed in windows authentication mode programmatically using c#.net or a suitable query will do.

Thanks
Lokesh
Santhosh N replied to Lokesh M on 16-Feb-10 06:14 AM
Here is a similar diswcussion which you can check for more info on how to do this and what limitations you have in that
http://www.eggheadcafe.com/software/aspnet/30215651/restart-sql-server-progra.aspx
Sagar P replied to Lokesh M on 16-Feb-10 06:16 AM
Try this code;

System.ServiceProcess.ServiceController[] sc = System.ServiceProcess.ServiceController.GetServices();

foreach (System.ServiceProcess.ServiceController s in sc)
{
    if (s.ServiceName.Equals("MSSQLSERVER"))
    {
         if (s.Status.Equals(System.ServiceProcess.ServiceControllerStatus.Stopped))
         {
                s.Start();
                Console.WriteLine("SQL Server Started..!");
         }
    }
}
Ryan C. replied to Lokesh M on 16-Feb-10 06:27 AM
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.

Taken from http://www.xdevsoftware.com/blog/post/Restart-SQL-Server-ASPNET.aspx
DL M replied to Lokesh M on 16-Feb-10 06:36 AM

you can call the console application exe from windows services timer elpased evnet. using

Process.Start("you console exe path");

you can use the insallutil tool to install your windows service as,

run the below command in command prompt

installutil -i 'windows service exe file path'

to uninstall the service

installutil -u 'windows service exe file path'

OR

Try this code sample
public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            // TODO: Add code here to start your service.
            StartService();

//set timer here
            double inter = 5000; //5 seconds
            System.Timers.Timer tim = new System.Timers.Timer(inter);
//set elapsed evnet for time
            tim.Elapsed += new 
System.Timers.ElapsedEventHandler(tim_Elapsed);
            tim.Enabled = true;
            tim.Start();
        }

        void tim_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
//call the start service function
            StartService();
        }

        protected override void OnStop()
        {
            // TODO: Add code here to perform any tear-down necessary to stop your service.
        }

//method for starting service
        private void StartService()
        {
            string strService = "MSSQL$SQLEXPRESS";
            ServiceController serv = new ServiceController(strService);
            if (serv != null)
            {
                if (serv.Status == ServiceControllerStatus.Stopped)
                {
                    serv.Start();
                    serv.WaitForStatus(ServiceControllerStatus.Running);
                }
            }
        }

//method for stopping service
        private void ReStartService()
        {
            string strService = "MSSQL$SQLEXPRESS";
            ServiceController serv = new ServiceController(strService);
            if (serv != null)
            {
                serv.Stop();
                serv.WaitForStatus(ServiceControllerStatus.Stopped);

                serv.Start();
                serv.WaitForStatus(ServiceControllerStatus.Running);
            }
        }
    }