Windows Forum

Ask something
Discussion Locked

HELP : Need SQL auto stop/start script

0 helpful
3 replies

by lt. john mclain

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Background:
SQL 2000 running on Windows 2000 SP 4
Problem:
Backups register as FAILED because open file agent cannot backup sql dbs.
MY possible solution:
Use a script to stop the sql service before the back-up kicks off and to start back up once the backup job is comlete.
NEED:
please point me in the right direction where i can locate documents on how to write a script to do this or if anyone has any advice on where i can download a script like this. ***THANK YOU***

3 replies

by -bw-

John;

What I have done in the past for a scenario exactly as you describe is create batch files to stop and start the DB and scheduled them via Windows Task Scheduler. Primitive as it sounds, it functioned properly, and I didn?t know scripting very well at the time.

I made a stop.bat and scheduled it to run prior to the SQL DB being backed up, depending on the amount of data you are backing up, you should be able to get a good idea when the backup procedure is trying to backup the DB. Then I made a start.bat to restart the DB after the backup procedure had completed, like I said, primitive but functional.

Net stop ?service to be stopped?
Net start ?service to be started?

There are a bunch of websites that will help you with details on performing this task, here are a few. Otherwise search on ?batch file to stop a Windows service?
http://www.tech-recipes.com/batch_file_programming_tips235.html
http://analog.fyxm.net/how-to/batch/
http://tweak.us/cms/article_windows_services_batch.html

Good luck!

by p.j.hutchison

I use the SQL Agent to schedule a backup of databases to a file on HD, then you can use NTBackup to backup the files to tape.

1. Open Enterprise Manager
2. Expand Server, management and click on Backups
3. Select Backup a Database..
4. Select database
5. Select Full Backup
6. Add a new Destination and select a file
7. Overwrite file
8. Enable schedule and set it up.

The backup job should appear under SQL Server Agent/Jobs where it can be modified.

Alternatively, you can use the isql.exe program in a batch job to backup databases or trans logs on a regular basis (no need to stop DBs).

This discussion is closed to new replies.