(I considered tacking this on the end of the thread from July about NTBackup and batch files, but it is different enough to not have to reference anything in that thread.) I am running Backup Exec for Windows Servers on Windows Server 2003 SP-2; our email program is FirstClass.
Dec 27, 2019 5. If you like to use this freeware to handle the scheduled backup, you can just click OK and go to the next step. If you want to Task Scheduler to run the backup, click on the Advanced tab, and check the option Use Task Scheduler to run this backup. Click “Start Backup” to start this file backup. Go to Task Scheduler to find this. You can't schedule a backup operation. The Task Scheduler service must be running before you can schedule a backup. If the Task Scheduler service isn't already running, follow these steps to start it: Click Start, and then click Run. In the Open box, type cmd, and then click OK. At the command prompt, type net start schedule, and then press ENTER.
Home > Articles > Information Technology
Sql Server Batch File
Like this article? We recommend
Like this article? We recommend
Repetitive administrative tasks on any network, large or small, can become tedious and annoying. These tasks, however, are essential to the performance and system up time of your servers. The types of service you are running (web, FTP, and so on) will determine which types of tasks are needed and how often they need to be performed.
As an administrator or even a general user, you might be aware of the Windows Task Scheduler (WTS), which is a handy utility that allows you to schedule tasks and determine the frequency to which they are to be performed. WTS comes with all Windows 2000/2003 Server and desktop operating systems.
In this article, I'll give you administrative examples of performing various monitoring and cleanup tasks for different types of services. You're welcome to use these scripts to get your creative juices flowing and create some of your own. My examples will be real-world examples that I use—I can't overemphasize the time and peace of mind they save me. When things really do become busy and you don't have time for the smaller tasks because you're too busy tending to some larger ones, they will already be taken care of.
Scripts that Do Maintenance
In this section, I want to talk about and give you some example scripts that do maintenance. Maintenance can be any of those tasks that require the 'cleaning up' of your system file structure, the backing up of files, or even hard drive defragmentation. None of these tasks you should have to do manually, and many programs will come with built-in options for doing these things. For example, Win2k and Win2k3 Server come with their own backup and defrag utilities. The backup has options for scheduling and the defrag utility does not. It doesn't mean we cannot schedule the defrag utility, which can be done rather easily by using a batch script and WTS.
First, even if a program such as Windows Backup and Restore comes with options for scheduling backups, it works only to back up to a drive or storage array on your network. Today's backups need to be more robust, and although backing up to somewhere else on the network is good, it only gives us file recovery from somewhere else on our local network. If your network should go belly up or your machines become infected with a malicious virus that infected our files, we're still in trouble. This introduces the concept of offline storage. I won't get into this concept here, but it is something else we can do to alleviate the local network codependency in terms of backing up files. I like to refer to this as co-location storage, or simply put, storing files from one location to another.
Schedule Server 2003 Backup Batch File Opener
Large businesses already have this covered in some or many ways. They often do it thru WAN links or VPN. With a wide area network, you have your own building-to-building network with all the fiber lines laid out between them, allowing you to share and transfer files between these locations with ease and privacy. With a VPN, you're creating the same concept, but using the Internet as a transport medium to achieve the same purpose.
Let's say it's not in your budget to do either WAN links or VPN. So you have to come up with your own clever way to achieve the task of transferring files from one network location to another. You can do this with a batch script and FTP. FTP is still the most popular and inexpensive method of transferring files between two networks at different locations, and you can use it to back up important files during the night or any time using WTS. The only advantage of a VPN or a WAN over FTP has is that the data is not encrypted or made private when going over the wire. You can however, get FTP programs that do encrypt the data over the wire, but I'm using general FTP as the example here. If there is nothing really sacred that you're transferring (such as credit card numbers), chances are you'll be okay using FTP.
To invoke this inexpensive method of co-location storage, you need to create only a couple of scripts. There are a number of ways to do this, but my example will use one .bat script and one .scr script. A .scr script is known as a FTP batch script. It is a script that is read by the FTP utility when using the –s switch. It is a convenient way for FTP to run a series of long commands from an external file, which in this case is the .scr script. The batch script (.bat) in this example will launch the FTP utility with the –s switch, followed by the .scr script name (see Figure 1).
I called my .scr script webbk.scr (for web backup). I'm using this script to back up my web files. Replace the IP address with the machine you want to FTP your files to. Call this batch script whatever you like and save it with the .bat extension. My batch script is called webbk.bat (the same name as my .scr file). Next, let's take a look at a sample .scr script file (see Figure 2).
Figure 2 Creating the FTP batch (.scr) script.
Starting from the top of this script, I first provide the FTP account login and password. Next, I tell FTP to transfer all my files in binary form, not ASCII text form. The Prompt Off command tells FTP to turn off interactive prompting when saving multiple files. If we leave this on (the default), our script will not execute, but instead be waiting for a yes or no before transferring our files. Something we definitely don't want because this will be a scheduled script with no user interaction.
I am now ready to begin transferring files, but first need to tell FTP where to navigate for these files on the source machine and where to put them on the destination machine. The cd command tells FTP to change directories on the destination machine, whereas the lcd command tells FTP to change directories on the source machine. The l in lcd is for local.
In this example, I navigate to my backup directory and index into the webs4business website directory on the destination machine. On the source machine using lcd, I navigate to the same web directory. If my .scr script was located in this directory, I wouldn't need to do any navigating at all, but because it is outside of this directory, you always need to navigate from the script directory. After setting the source and destination locations, I use the FTP mput command with the *.* wildcard, telling FTP to transfer all files in this directory.
If you want to do other directories, you need to back out with the cd and lcd commands and reindex to the next group of source and destination directories. You can then repeat the process until you have gone through the entire directory structure. A script like this is often built over time, so as you add new directories to your machine, you then need to update this script so that when the next backup takes place, this directory will be included. After the transfer is done, you tell FTP to quit or exit. Use this example to create your own .scr file and name it whatever you like.
Now that both scripts have been created, WTS can now be used to set up the schedule for executing the backup script. To access the WTS Wizard on Win2k3 server, click Start and select Control Panel > Scheduled Tasks > Add Scheduled Task. You should see a screen similar to the one shown in Figure 3.
Figure 3 Viewing the first screen of the WTS Wizard.
Click Next; you'll see a screen asking you to choose or browse to an application you want WTS to create a schedule for. The batch file we need to schedule isn't in the list, so we click Browse to target the file and click Next to advance to the screen shown in Figure 4.
Choose the frequency for the task. Click Next to define the task time and the start date, and finally tell WTS the credentials to use when running the task. After entering the credentials, you should be at the last screen of the wizard (shown in Figure 5).
Figure 5 Finishing the WTS Wizard.
Choose the check box that says 'Open advanced properties for this task when I click Finish'. Click Finish and you should see the advanced properties dialog box shown in Figure 6.
Let's say you want to run this task more frequently (for example, every 3 hours during every 24 hours). You didn't have this option while running the WTS Wizard, but now you can tweak the advanced task duration and frequency parameters by selecting the Schedule tab and clicking Advanced. You should see the Advanced Schedule Options property sheet, as shown in Figure 7.
Figure 7 Fine-tuning the task frequency and duration.
Select the Repeat Task check box if you want the task to repeat every certain number of minutes or hours until a certain time or for a set duration. Click OK and Apply; the task will now be set to run at the next scheduled running time and repeat at the day/week and hour/minutes frequency you defined for the task.
In my example, I am having my files backed up nightly at 2:00 a.m. You can easily expand on these scripts to do other more complicated tasks. For instance, if you have an email or database server, doing a routine cleanup of your mail directories or a backup of your database file transaction logs and data files is only a simple modification of the scripts we've looked at. The only catch with an email or database server is that the Simple Mail Transport Protocol (SMTP) service for email or SQL Server for database needs to be stopped before performing such operations to avoid a sharing violation. This is because the files we're removing or backing up are still in use by the service.
To expand on the preceding scripts, let's say you have an SQL database server serving some websites. A quick-and-dirty (but very effective) way to back up your databases to another server in another location over the Internet using FTP is to copy the database transaction log and data files from the SQL server's data directory (see Figure 8).
The location of your data files may vary, but by default they are located in your SQL Server's Data directory. To accomplish backing up the database, first create a new batch file that will be similar to the one in the previous example for backing up web files. This time, however, you'll stop and start the SQL Server service by using the net stop and net start commands shown in Figure 9.
Figure 9 Creating our database backup batch script to stop and start SQL Server.
In Figure 9 you can see that I first stop the SQL service and than perform the tasks in what will be the new .FTP batch script (.scr). After backing up the files, we tell the batch script to start the SQL service again. Now let's create a new .scr script to back up the database files (similar to the one shown in Figure 10).
Figure 10 Creating the database backup FTP batch script to back up the data files.
This script backs up the Northwind database to a directory on the target server called Databases. Because the script is executed from the same location as the database files, we need to do no local directory navigation to find the files we are backing up. You can see how similar this .scr script is to the one in the previous example. The only difference is that now we are backing up database files as opposed to web files. The key difference with the batch (.bat) script is starting and stopping the service so that no sharing violation occurs when attempting to back up the database files. To schedule the task, simply run the WTS Schedule Wizard using the same steps already discussed in this article.
Related Resources
- Online Video $159.99
- Premium Edition Video $239.99
- Online Video $239.99