Home > Articles > Software Development & Management

Using Batch Scripts to Do Co-located Backups of Important Web and Database Files

📄 Contents

  1. Scripts that Do Maintenance
  2. Conclusion
Repetitive administrative tasks getting you down? Want to save time and keep your stress level low? Read Jesse Smith's tips for some thrifty ways to do co-location backups of general and service related files using batch scripts.
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.

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

Figure 1

Figure 1 Creating the batch (.bat) script.

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

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

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.

Figure 4

Figure 4 Selecting the frequency for the task.

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

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.

Figure 6

Figure 6 Viewing the WTS Advanced property sheet.

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

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

Figure 8

Figure 8 Viewing the SQL Server data directory.

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

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

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020