SQL Server services - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server services

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-30 : 08:58:08

Rod writes "How do I start and stop SQL server services within a sql script?

Thanks,
Rod"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-05-30 : 09:21:44

Perhaps one of the more learned gentleman can correct me, but I don't think you'll find any way of doing this on the server itself. If you could stop the SQL server with a SQL script, then there would be no SQL server to run the Start SQL server part...

Might be possible from another SQL server (or instance) where you invoke XP_CMDSHELL and stop the SQL server instance . Can't think of any other way, and the userid running the invoking SQL server would have to have the authority to stop and start the SQL iserver instance being targeted..

good luck with this one....

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-30 : 19:35:21

I guess you mean on another box - I believe NT and 2000 resource kits
have scripts to allow remote start/stop of services which could be run via xp_cmdshell

Since I was bored I wrote a procedure to do it on the same box
(Assumes a default instance of SQL and SQL Server console is logged in as a local admin on the box.)

create a file called stopstart.bat on the c:\ drive and in it put

net stop MSSQLSERVER /Y >>c:\stopstart.txt
net start MSSQLSERVER >>c:\stopstart.txt
net start SQLSERVERAGENT >>c:\stopstart.txt
osql -E -Q "exec pubs.dbo.stopstart 'BAT'"

In QA create the following procedure in pubs database

create proc stopstart @source char(3)='PRC'
as
set nocount on
declare @cmd varchar(200)

--check if this is the bat file re-entering
If @source<>'BAT'
begin

exec master..xp_cmdshell 'echo This is before we stop SQL >c:\stopstart.txt'

set @cmd='at '+(LEFT(convert(varchar(8),dateadd(mi,1,getdate()),108),5))+' "c:\stopstart.bat"'

exec master..xp_cmdshell @cmd

return
end

exec master..xp_cmdshell 'echo This is after we restart SQL >>c:\stopstart.txt'
return
go

Then in a QA window on the server console run exec dbo.stopstart
This will :
1)Create a text file c:\stopstart.txt
2)Write to it
3)Create an AT job to run c:\stopstart.bat in one minutes time
4)exit

Then in one minute or less the AT job will kick in and
1)Stop SQL and AGENT services
2)Restart the services
3)execute stopstart procedure with 'BAT' parameter which writes the completion message to stopstart.txt

You should end up with a stopstart.txt file like:

This is before we stop SQL

The following services are dependent on the MSSQLSERVER service.

Stopping the MSSQLSERVER service will also stop these services.

SQLSERVERAGENT

The SQLSERVERAGENT service is stopping.
The SQLSERVERAGENT service was stopped successfully.

The MSSQLSERVER service is stopping..
The MSSQLSERVER service was stopped successfully.

The MSSQLSERVER service is starting.
The MSSQLSERVER service was started successfully.

The SQLSERVERAGENT service is starting.
The SQLSERVERAGENT service was started successfully.

This is after we restart SQL

HTH
Jasper "very bored 2nite" Smith

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-05-31 : 03:26:53

Well I hope ROb likes it , because I'm quite impressed!

Nice one !

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-05-31 : 06:07:02

A similar/related one from my site:
http://vyaskn.tripod.com/restart_sql_server_service.htm

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources