INF: How to Schedule Backup & DBCC Commands Using AT Scheduler

• Microsoft SQL Server, versions 4.2, 6.0, and 6.5

SUMMARY

There are times when you want jobs to be scheduled through the Windows NT Server AT scheduler rather than scheduled through the task scheduling manager used by the SQL Executive service. For example, you may want to schedule a job to stop SQL Server so that you can do a Windows NT Server backup of all the SQL Server device files, and then schedule a job to restart SQL Server once the backup is complete.

This article gives examples on using the AT scheduler to schedule jobs.

MORE INFORMATION

To Schedule Database Dumps or DBCC Commands Through the AT Scheduler

1. Open the Services Control Panel. Select the Schedule service and click Startup. Change the Startup Type to Automatic. Click OK and then click Close. If the Schedule service is not already started, start it now.

2. Create a text file with the dump commands and/or DBCC commands that you want to schedule, with the 'go' command between each statement and at the end of the file (you may want to manually test each command by going to the query window before you schedule this text file to run in production).

   NOTE: You can view all the options that are available through the AT scheduler by going to a command prompt and typing the following:

         AT /? |MORE
   

3. Schedule your file to run. For example, if you want your file to run every day at 4:00 P.M. and you want the results to be saved to an output file, then you would type the following at a command prompt:

         AT 16:00 /every:M,T,W,TH,F,S,SU "isql /Usa /P <SA password> /S
         <server name> /i c:\<input file name> /o c:\<output file name>"
   

   NOTE: If you want to view what jobs you have scheduled, type "AT" at the command prompt.

   NOTE: If you want to delete a specific job, type "AT" at the command prompt and write down the ID of the job you want to delete. Then type the following at the command prompt:

         AT <ID>/delete
   

4. Turn off any other scheduled tasks scheduled through SQL Server that you have already scheduled through the AT scheduler.

1. Create two text files using a text editor such as Notepad. You may want to name the files Users\Default\Stop.bat and Users\Default\Start.bat.

   In SQL Server version 6.0 or 6.5, the services are MSSQLSERVER, SQLEXECUTIVE, and optionally MSDTC. In SQL Server version 4.2x, the services are SQLSERVER and SQLMONITOR.

   Add the following lines to the Stop.bat file:

         net stop mssqlserver
         net stop sqlexecutive
   

   Add the following lines to the Start.bat file:

         net start mssqlserver
         net start sqlexecutive
   

2. Schedule your Stop.bat file to run. NOTE: It may be easier if you can combine the Stop.bat, tape backup, and Start.bat all in one batch file.

   For example, if you want to stop the SQL Server services at 8:00 P.M. every night, type the following:

         AT 20:00 /every:M,T,W,TH,F,S,SU"c:\users\default\stop.bat"
   

3. Once SQL Server is stopped, you can use whatever tape backup utility you want to make backups of the SQL Server device files.

4. Once the device files have been copied to tape, you should restart SQL Server. For example, if you want to start the SQL Server services at 11:00 P.M. every night, type the following:

         AT 23:00 /every:M,T,W,TH,F,S,SU"c:\users\default\start.bat"