Microsoft KB Archive/162294

= How to schedule SQL Server backup or DBCC commands by using AT scheduler command =

Article ID: 162294

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q162294



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.



To Schedule Database Dumps or DBCC Commands Through the AT Scheduler
 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. 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'''

 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  /S

/i c:\ /o c:\ "'''

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 /delete

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

Note If you are using SQL Server 2005, use the Sqlcmd utility instead of the Isql utility in step 3. For more information about the Sqlcmd utility, see the "Sqlcmd Utility" topic in SQL Server 2005 Books Online.

To Stop or Start SQL Server Through the AT Scheduler at Specific Times, to

Perform Backups of the SQL Server Device Files
  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 4.2x, the services are SQLSERVER and SQLMONITOR. In SQL Server version 6.0 and 6.5, the services are MSSQLSERVER, SQLEXECUTIVE, and optionally MSDTC. In SQL Server version 7.0, the services are MSSQLServer, SQLServerAgent, and optionally MSDTC. 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 </li> 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"

</li> Once SQL Server is stopped, you can use whatever tape backup utility you want to make backups of the SQL Server device files.</li> 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"

</li></ol>

Additional query words: checkdb newalloc dat log data database

Keywords: kbinfo kbinterop kbusage KB162294

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.