How to schedule SQL Server backup or DBCC commands by using AT scheduler command (162294)

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.

Perform Backups of the SQL Server Device Files

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

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"