Resolved SQL Server 08R2 Express cmd line properties

[alykins]

I was wondering if there is a way to set the startup properties of the sql server agent via command line... it would save me a ton of time managing all the machines I have if I didn't have to RDP into each one... rightclick-manage-sql server properties-startup mode auto > to manual

any ideas?

[WolfShade]

Like these? http://msdn.microsoft.com/en-us/library/ms190737.aspx

[alykins]

almost... those are commands to start up SQL if you are doing it from command prompt (I have used those in the past bc these machines do not have SQL Management Studio)- anyways what I was looking for was more of a fix to change the start-up mode (there is 'Automatic', 'Manual', as well as 'Disabled')

I am wondering if it is a registry setting maybe... I was hoping for a script I could just shoot @ it and flip the startup method

[Fou-Lu]

Originally Posted by alykins

almost... those are commands to start up SQL if you are doing it from command prompt (I have used those in the past bc these machines do not have SQL Management Studio)- anyways what I was looking for was more of a fix to change the start-up mode (there is 'Automatic', 'Manual', as well as 'Disabled')

I am wondering if it is a registry setting maybe... I was hoping for a script I could just shoot @ it and flip the startup method

Services?
If so then yep, it can be done by the registry or by issuing an sc. Lets see, I believe it would be sc config [ServiceName] demand (I think its demand :P). You can also put the server name/ip between the sc and config to force it on another machine. Requires administrative user of course.
To command the services from the registry, you should be able to find that under: HKLM\System\CurrentControlSet\services\* and the setting is under the "start" dword. Its values are 0x2 = auto, 0x3 = manual, and 0x4 = disabled. 0 and 1 are boot and system, you don't typically need to use those.
Check for the SQLServeAgent key. Not sure exactly what its called with 2008R2 express.
Hopefully that's what you need :P

[alykins]

It's not an issue of not knowing how to do it the 'normal way' it's an issue of having to remote into every single machine when I could do something like

PsExec.exe \\machine cmd.exe ??? Script to modify SQL   /k shutdown -t 0

in essence letting me one shot from my machine's cmd prompt rather than RDP'ing into a ton of machines

Edit: and there are a lot

[Fou-Lu]

I hear you I'm all for scripting; manually rdp'ing so many machines is a pain (I've 450ish active machines that I worry about, and if I sure wouldn't manually change something on all of them either).
If it is the services, you can effectively create a batch and iterate a list in a text file if you want. So long as your user account has administrative access on the machines, you can simply execute an sc MyServerName config ServiceName type=demand I think it would be. So you likely have a list of all the machine name/ips available, you can create a text file and iterate that. My batch skills are always rusty until I need to use them so I can't write that up for you atm, but the idea would be simply:

# your servernames (I've always wanted server names based on virus' :)):
EBOLA
MALARIA
TYPHOID
CHOLERA

And then when you iterate the read of the file, you'd have a call similar to sc !a! config SERVICENAME type= demand.

[Old Pedant]

You could also write a ".js" or ".vbs" script file to do it, if you hate MS command language as much as I do.

[alykins]

Sweet! Thanks guys for all the help so far! pushed me far forward to where I need to be. So I am halfway through writing the vbs for it when I stumbled across a warning in another post to never use vbs for logon purposes; it is a huge security risk... So I was wondering if this was a security risk or not? also I am not the greatest w/ vbs and I am having trouble attaching to the runas process I start... I commented it so you can see what I am trying to do; is what I am doing wrong glaringly obvious? (I know that the syntax is wrong and that it will kick off a process of cmd.exe; but idk the proper syntax)

machine = InputBox("Target Machine...")
UserInput = InputBox( "Password..." )


set WshShell = CreateObject("WScript.Shell")
set WshEnv = WshShell.Environment("Process")
WinPath = WshEnv("SystemRoot") & "\System32\runas.exe"

rc=WshShell.Run("runas /user:domain\username cmd.exe",2,FALSE)
Wscript.Sleep 30
WshShell.AppActivate(WinPath)
WshShell.SendKeys UserInput & chr(10) & chr(13)
'WinPath = WshEnv("SystemRoot") & "\System32\cmd.exe"
'Wscript.Sleep 30
'WshShell.AppActivate(WinPath)
'WshShell.SendKeys "cd\" & chr(10) & chr(13) & "PsExec.exe \\ & machine &_
'                    "cmd.exe sc config MSSQL$SQLEXPRESS start= demand"

*I also though of trying to attach to the process but when looking for it I kept getting the same inane threads posted across many site by the same person & I don't have a ref book

I then thought that something like this

rc=WshShell.Run("runas /user:domain\username cmd.exe | " & UserInput)

would be better than runas but I am not sure of the syntax for 'where' to put my commands after i execute cmd
ie would it be

rc=WshShell.Run("runas /user:cwopa\dhsvcwic03 cmd.exe | " & UserInput & " some command")
or
rc=WshShell.Run("runas /user:cwopa\dhsvcwic03 cmd.exe some command | " & UserInput)

[Fou-Lu]

Ugh, vbs.
You're a C# developer yeah? I would think that the C# would probably be a better solution for this; AFAIK you cannot thread vbs, and if you are talking about many machines to run against it would probably make sense to thread it up and send them off.
I would assume that C# would have easy interaction with WMI, but unless you specify username/password combinations somewhere, you would need to rely on a user that's an administrator on all machines or a domain/enterprise admin to execute it.

[Old Pedant]

I simply don't have a system where I can try that stuff any more.

I'm doing contract work from home and while I have VPN to a system where this could be done I don't have high enough privileges there to do it.

I do think it *could* be done via VBS, but what FouLu says re threading makes sense.

[alykins]

yeah I think a C# shell would be faster- and that I can bang out no problem (stupid little throw away form, or even console)

Thanks for the input guys!

[Fou-Lu]

Originally Posted by Old Pedant

I simply don't have a system where I can try that stuff any more.

I'm doing contract work from home and while I have VPN to a system where this could be done I don't have high enough privileges there to do it.

I do think it *could* be done via VBS, but what FouLu says re threading makes sense.

Definitely doable in vbs; I have apps that were written for cluster management in (of all things) vbs and WMI. The "threading" concept is actually a relay of a spawned sub process that detects its completion state before attempting to poll for current information. Ingenious, I have to admit, but far cry from the threading model. Unfortunately, the way its built (and due to the limitation of vbs), if I execute my own WMI management at the same time it will result in the script crashing since it'll never detect that the WMI has completed. Oh well.

If I were you alykins, I'd write it as an actual manager. Given a list of machines, you can do something like select a drop down for the service then have it populate in a table below the current status and start up type for the service. Choose the service (add some select/desselect all type options), and submit those for the WMI to update. Neat.
This way you could also handle if you get something new as well and want to add it to the management. Just use some files to construct the dynamic data you need.

Now I get to make an awesome mspaint picture
Lol that's awesome.