Restarting SQL Server by code

For the past several months we have been having troubles with our DB. The IT can’t find the cause and the only application that doesn’t startup right is my team’s application because it uses ArcObject COM objects to connect to the SDE.

To investigate this I started by writing a simple coded unit test that tests the connection. The test wasn’t in any shape or form automatic, I had to stop it at a breakpoint and stop the DB service and then start it back and see if the application worked.

I decided I have to do this right and write an automatic unit test that restarts the local SQL Server service.

The code is fairly simple:

1. private void RestartSqlServer()
2. {
3.     var controller = new ServiceController {MachineName = ".", ServiceName = "MSSQLSERVER"};
4.  
5.     if (controller.Status == ServiceControllerStatus.Running)
6.         controller.Stop();
7.  
8.     controller.WaitForStatus(ServiceControllerStatus.Stopped, new TimeSpan(0, 0, 1, 0));
9.     if (controller.Status != ServiceControllerStatus.Stopped)
10.     {
11.         throw new Exception("Couldn't stop SQL Server.");
12.     }
13.  
14.     controller.Start();
15. }

line 3: Starting the ServiceController with the current machine and service name which was taken from:

Computer Management->Services and Applications->Services->SQL Server (MSSQLSERVER)->Properties:

line 5-6: we only stop the service if it’s running

line 8: we wait for a minute for the service to stop and if it doesn’t we throw an exception

line 14: starting the service again

 

That’s it!

Now to start those unit tests up…

 

Keywords: SQL Server, Unit Test, code, service

IceRocket Tags: SQL Server,Unit Test,code,service