Solved

Script to start or stop services

Posted on 2007-11-29
28
4,332 Views
Last Modified: 2010-08-05
Experts!  Here is the situation.  I have a server running Websense and I would like to backup the database.  In order to do that, I need to stop the MSSQL$ and SQLAgent$ service before running the backup.  I posted a similiar question before and was given this script:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colService = objWMIService.ExecQuery("Select * from Win32_Service where Name='mssql$websense'")
For Each objService in colService
    if objService.State = "Stopped" Then
      errReturn = objService.StartService()
      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )
      For each objService2 in colService2
            'msgbox objService2.DisplayName
                errReturn = objService2.StartService()
      Next
    Elseif objService.State = "Running" Then
      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )
      For each objService3 in colService3
            'msgbox objService3.DisplayName
                errReturn=objService3.StopService()
      Next
      errReturn = objService.StopService()
    End If
Next

What I was hoping to have the script do was check to see if those two services were running and if they were, to shut them off.  Then I would schedule the script to run again and turn them back on.  What seems to happen is only one service shuts down and the other stays on when the script runs the first time.  When it runs the second time, it turns the first service back on and shuts down the other service.

So what I need help on is how to modify the above script to give the desired results (stated above) or use a new script that will solve this issue.  Either way, the points are available for whoever provides a fix.  Thanks for your help!!
0
Comment
Question by:samiam41
  • 17
  • 8
  • 3
28 Comments
 
LVL 5

Expert Comment

by:usarian
ID: 20375901
SQL Server doe not need to be shut down to run a backup.
you can use NTBACKUP, any commercial backup software (which mostly use NTBACKUP behind the scenes), or use the SQL Server command BACKUP.

0
 
LVL 5

Expert Comment

by:usarian
ID: 20375947
Wow did I just let a brain fart rip thru this place.. sorry..
0
 
LVL 9

Author Comment

by:samiam41
ID: 20376278
: )

Not a problem.  I tried using HP Data Protector's (we used this backup software because of the mix of UNIX and Windows servers) SQL Agent to backup the database but the MSDE database still became "suspect".  So now I am just going to the services down while I back it up.
0
 
LVL 8

Expert Comment

by:deadite
ID: 20377589
Give this a shot, just change strService1 and strService 2 to match the names of the services.   I'm sure you can take the time to make the code a bit more efficient by making it a sub to call, but it was quicker this way...
strComputer = "."

strService1 = "mssql$websense"

strService2 = "SQLAgent$websense"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colService = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objService in colService

	'CASE 1

    If objService.Name = strService1

	    If objService.State = "Stopped" Then

	      errReturn = objService.StartService()

	      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService2 in colService2

	            'msgbox objService2.DisplayName

	                errReturn = objService2.StartService()

	      Next

	    Elseif objService.State = "Running" Then

	      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService3 in colService3

	            'msgbox objService3.DisplayName

	                errReturn=objService3.StopService()

	      Next

	      errReturn = objService.StopService()

	    End If

	'CASE 2

    Elseif objService.Name = strService2

	    If objService.State = "Stopped" Then

	      errReturn = objService.StartService()

	      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService2 in colService2

	            'msgbox objService2.DisplayName

	                errReturn = objService2.StartService()

	      Next

	    Elseif objService.State = "Running" Then

	      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService3 in colService3

	            'msgbox objService3.DisplayName

	                errReturn=objService3.StopService()

	      Next

	      errReturn = objService.StopService()

	    End If			

	End If	

Next

Open in new window

0
 
LVL 8

Expert Comment

by:deadite
ID: 20377611
I should mention, the only draw back with that script is, it will either start or stop the service depending on the current state.  If you know they will always be started at the same time, it will stop them both.  However, if one is stopped and one is started, it will start the first one and stop the second.
0
 
LVL 8

Expert Comment

by:deadite
ID: 20377656
You can even do this with a BAT file, minus the dependencies using

@Echo Off
Rem Stop Serives
Net Stop MSSQL$websense
Net Stop SQLAgent$websense


and have a second BAT File to start them
@Echo Off
Net Start MSSQL$websense
Net Stop SQLAgent$websense


If you prefer VBS, here are 2 files you can have to stop and start it:
' Stop Running Services

strComputer = "."

strService1 = "mssql$websense"

strService2 = "SQLAgent$websense"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colService = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objService in colService

	'CASE 1

    If objService.Name = strService1

	    If objService.State = "Running" Then

	      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService3 in colService3

	            'msgbox objService3.DisplayName

	                errReturn=objService3.StopService()

	      Next

	      errReturn = objService.StopService()

	    End If

	'CASE 2

    Elseif objService.Name = strService2

	    If objService.State = "Running" Then

	      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService3 in colService3

	            'msgbox objService3.DisplayName

	                errReturn=objService3.StopService()

	      Next

	      errReturn = objService.StopService()

	    End If			

	End If	

Next
 
 
 
 
 
 
 

' Start Running Services

strComputer = "."

strService1 = "mssql$websense"

strService2 = "SQLAgent$websense"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colService = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objService in colService

	'CASE 1

    If objService.Name = strService1

	    If objService.State = "Stopped" Then

	      errReturn = objService.StartService()

	      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService2 in colService2

	            'msgbox objService2.DisplayName

	                errReturn = objService2.StartService()

	      Next

	    End If

	'CASE 2

    Elseif objService.Name = strService2

	    If objService.State = "Stopped" Then

	      errReturn = objService.StartService()

	      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService2 in colService2

	            'msgbox objService2.DisplayName

	                errReturn = objService2.StartService()

	      Next

		End If

	End If	

Next

Open in new window

0
 
LVL 9

Author Comment

by:samiam41
ID: 20382182
deadite-  thanks for the script.  I have to admit that scripting is something I am still learning.  I am more network and server geared and I am trying to learn more scripting.  Given that, I am not sure what you mean by "sub to call".  Will the first script you posted also start/stop the dependencies?
0
 
LVL 9

Author Comment

by:samiam41
ID: 20382250
The first script you posted I get an error when I run it.

Line 8
Character 37
Error:  Expected 'Then'
0
 
LVL 9

Author Comment

by:samiam41
ID: 20472091
Any help would be appreciated.
0
 
LVL 5

Expert Comment

by:usarian
ID: 20472603
take deadite's code and put the word "THEN" at the end of line 8, which is:
If objService.Name = strService1

change to:
If objService.Name = strService1 Then



strComputer = "."

strService1 = "mssql$websense"

strService2 = "SQLAgent$websense"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colService = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objService in colService

	'CASE 1

    If objService.Name = strService1 Then

	    If objService.State = "Stopped" Then

	      errReturn = objService.StartService()

	      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService2 in colService2

	            'msgbox objService2.DisplayName

	                errReturn = objService2.StartService()

	      Next

	    Elseif objService.State = "Running" Then

	      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService3 in colService3

	            'msgbox objService3.DisplayName

	                errReturn=objService3.StopService()

	      Next

	      errReturn = objService.StopService()

	    End If

	'CASE 2

    Elseif objService.Name = strService2

	    If objService.State = "Stopped" Then

	      errReturn = objService.StartService()

	      Set colService2 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService2 in colService2

	            'msgbox objService2.DisplayName

	                errReturn = objService2.StartService()

	      Next

	    Elseif objService.State = "Running" Then

	      Set colService3 = objWMIService.ExecQuery("Associators of " & "{Win32_Service.Name='" & objService.Name & "mssql$websense'} Where " & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

	      For each objService3 in colService3

	            'msgbox objService3.DisplayName

	                errReturn=objService3.StopService()

	      Next

	      errReturn = objService.StopService()

	    End If			

	End If	

Next

Open in new window

0
 
LVL 9

Author Comment

by:samiam41
ID: 20473058
Thanks for the post.  I made the change and received a similiar error for line 25/char 41.  I added it and am testing it now.
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473083
No dice.  The services stay "started".

Any other ideas?  deadite posted this and I wasn't sure what he meant:  

"just change strService1 and strService 2 to match the names of the services.   I'm sure you can take the time to make the code a bit more efficient by making it a sub to call, but it was quicker this way"

0
 
LVL 9

Author Comment

by:samiam41
ID: 20473097
I can start and stop them manually.  I just did it to make sure that it wasn't something else.
0
 
LVL 5

Expert Comment

by:usarian
ID: 20473148
Try deadite's cmd:
Net Stop SQLAgent$websense

That is, Start-->Run-->CMD
type:
Net Stop SQLAgent$websense

hit enter, what response do you get?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 9

Author Comment

by:samiam41
ID: 20473158
The SQLAgent$WEBSENSE service is stopping.
The SQLAgent$WEBSENSE service was stopped successfully.
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473167
Are services CasE sensitive?
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473171
Nevermind.  Dumb question.  I should have known better.
0
 
LVL 5

Expert Comment

by:usarian
ID: 20473174
Refresh my memory here.. why are we not using the SQL Server 'BACKUP DATABASE' TSQL command...?
0
 
LVL 5

Expert Comment

by:usarian
ID: 20473189
After running the CMD command, does the agent service say "Started" or "Stopped"
(bear with me here)
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473205
I'm not sure we went over that command.  I am trying to stop the MS/SQL services so I can backup the databases using HP Data Protector.  If the services are running and I try to back them up, they become "suspect" and I have to recreate/restore them.  

If you know of a way that I can copy the databases to a directory on another server and we can do away with this script, I am all ears.  As long as the solution allows me to backup the databases and not corrupt them in the process, I can be flexible.
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473228
After running the net stop sqlagent$websense command, the status for sql is blank under "computer management > services".  There is no status.  If I use pstools and check the status of that service, it shows me stopped.
0
 
LVL 5

Expert Comment

by:usarian
ID: 20473260
Do you have access to Query Editor or Enterprise Manager or TOAD for SQL Server or Microsoft SQL Server Management Studio or Visual Studio, and anythign else that can execute SQL commands?
If all else fails there's OSQL

Quick outline of where I'm going:
SQL Server has a BACKUP DATABASE command that can be executed the same manner in which you execute queries.
Although there is a way to backup directly to another computer on a network, it is better practice to save the file locally (gotta have enough disk space for the backup file of course), then copy/move it to where you want it to go.

0
 
LVL 9

Author Comment

by:samiam41
ID: 20473284
All good questions.  I have MSDE installed on this server as it came with Websense, so I am not sure what access we have to Query Editor, etc.  I seriously never thought having a script that would stop a service and it's dependencies and then start them up again could be this hard.  I just need the services to stop and start using task manager.  While the services are offline, I copy the database somewhere else.  I admit that I am a network and server engineer more then a developer or programmer.  Am I misunderstanding how complex this is?
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473404
Ok.  I think I figured it out.  Using PSSERVICE.exe, I can stop and start the services.  I stop sql first and then mssql second.  Vice-versa on start them.  Wow....  What a jungle of a mess.  

I appreciate everyone's help and suggestions.  I am thinking how to divide up the points.
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473430
Do you know how to use psservice in a script?  I will award full points if you can help with that.  Otherwise I don't see a solution.  Thanks usarian.
0
 
LVL 5

Accepted Solution

by:
usarian earned 500 total points
ID: 20473518
I'm happy you found a way to start and stop services.
Take a look at using OSQL to run BACKUP DATABASE though,  It enables you to perform backups without shutting down the database.

https://conference.websense.com/docs/releasenotes/v6.3/ReportingReleaseNotes.htm#0_pgfId-2589913

under the header:
Microsoft MSDE backup



another link:

http://www.microforge.net/kb/135

Your instance name is:
websense

Happy Holidays!
Usarian
0
 
LVL 9

Author Comment

by:samiam41
ID: 20473528
Complete!

To stop it:
c:\tools\psservice \\w2k3websec01 stop mssql$websense
ping -n 5 127.0.0.1 >NUL
c:\tools\psservice \\w2k3websec01 stop sqlagent$websense

To start it:
c:\tools\psservice \\w2k3websec01 start mssql$websense
ping -n 5 127.0.0.1 >NUL
c:\tools\psservice \\w2k3websec01 start sqlagent$websense

Thanks everyone!!  Have a great Christmas or other Holiday!
0
 
LVL 9

Author Closing Comment

by:samiam41
ID: 31411742
usarian, thanks for the ideas and for sticking with the question.  I am awarding you the points because I am going to look into the OSQL Backup Database option.  Take care!!

Best regards,
Aaorn
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now