April 28, 2004 at 1:12 pm
Is it possible to automatically stop the sql service once the backup process/maintenance plan finishes? We have a situation where the SQL Service needs to be stopped to prevent users from updating the database while a backup of data files is completed (so there is no out-of-synch scenario between the db and the data files).
Any information or alternative solution is greatly appreciated!
Bill
April 28, 2004 at 2:19 pm
I have used the following in a batch file to reboot the SQL server:
net stop sqlagent$servername
net stop mssql$servername
net start mssql$servername
net start sqlagent$servername
You could call the batch file as a step in the job.
April 28, 2004 at 4:15 pm
Great - thanks Bill!
April 29, 2004 at 9:07 am
Bill,
I've never used the CmdExec function in the job steps before, but it seemed simple enough. Unfortunately I'm having an issue. I created a simple batch file with the commands above, modified to fit my particular services.
I then added a second step to my db backup job which simply calls the batch file. The database backup step completes fine. Unfortunately, step 2 executes the first command in the batch file to shut down sql server agent and just stops there. It doesn't stop or restart the sql server and it doesn't restart the sql server agent.
Any ideas? Any push in the right direction would be greatly appreciated! I've looked all over the Web and in my SQL Server System Admin book from Microsoft, but I just can't seem to find anything.
Thanks!
Bill
April 30, 2004 at 5:07 pm
Hmm. I think I gave you bad advice for your circumstance, but maybe like me you'll at least find that the batch file is handy for re-booting SQL.
The problem is that SQLAgent is used to run jobs. So, if you shut down SQLAgent, apparently all jobs will be shut down, too.
As I think about it, I seem to recall that I used Scheduled Tasks under control panel on the server to schedule the batch file to run. This makes more sense because how could SQL Server turn itself back on if it is already turned off? In other words, you need something outside of SQL Server to turn it off and on.
Another alternative to scheduling with the operating system would be to run/schedule (in SQL Server or through dtsrun) a dts package that runs an execute process task to call the batch file. The trick here is that once called from the dts task, the batch file runs asynchronously (outside of SQL Server).
To build in a delay before the SQL Server reboots, you could add the following line in the batch file:
ping 1.1.1.1 -n 1 -w 60000 > nul rem : 60 sec delay
(With cedit to: http://www.computing.net/dos/wwwboard/forum/13475.html)
Hopefully, these suggestions will work better for you.
Bill
April 30, 2004 at 5:31 pm
Another thought. Have you considered using sp_dboption to set the db as single user or offline?
May 3, 2004 at 4:50 am
Great ideas Bill! I'll look into both of them. I didn't even think about either of those options, but they make so much sense!
Bill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply