Stopping SQL using a Bat file

[ful56_uk]

Hi

 

at the momment we have a bat file that runs just before backup starts so it can stop our sims sql database. I have notice that it has stopped working becuase the file askes a questions do you really want to do this because it is connected to sql server agent.

 

My question is what command do I put in the bat file to auto answer Yes to this question if it comes up again.

 

Mark

[srochford]

If you give us a clue what's in the batch file we might be able to give a better answer :-)

 

I'm guessing you've got something like "net stop mssqlserver" - if so, then you want "net stop mssqlserver /y"

 

I'm assuming that you're just backing up the mdf/ldf files - you can do this but it's much better to use SQL's built in backup routines to backup the db and then back up the files it generates - exclude the "data" folder from your normal backup. Working like this means you don't have to stop the SQL server (may not matter for SIMS if you're doing it over night) but also means you can do things like flushing the transaction logs.

 

There have been threads on here before about the best way to backup SIMS - worth checking them out.

[ful56_uk]

Cheers, thats the info i need, yeah it runs over night, ill add the /y to the file seems the way. Not very good with sql so i wouldnt know what to do with sqls own backup routines.

 

Thanks for your help

 

Mark

[webman]

Speaking from experience, I would also set a separate script to run every morning before people get in to work to re-start the SQL - just incase the backup script fails for whatever reason leaving the SQL in a stopped state.

[ChrisH]

Speaking from experience, I would also set a separate script to run every morning before people get in to work to re-start the SQL - just incase the backup script fails for whatever reason leaving the SQL in a stopped state.

 

I would second that suggestion. We had a problem with the backup being too big sometimes and the server would just be stuck on the backup until I fixed it. I made sure SQL had started by 7 am after that.