From Pickwiki
Jump to: navigation, search

Backing up a U2 dbms is an essential part of dbms management. This task is made difficult because of the different methods available to backup your data. Historically, MV dbms products have always had their own backup utilities (file-save anyone?) but in the new-age computing environments, such things have often been left to the O/S. This is a valid strategy for very large operations but is difficult and costly for small to medium businesses.

Below are several Windows scripts designed to automate the Windows O/S backup process. These scripts are designed to accomplish five things:

  1. Backup U2 data to a local file using the Windows NTBackup utility
  2. Copy the backup to an archive directory on the local machine
  3. Sftp the backup to another machine on the local network (or whereever)
  4. Clean up the archive to ensure oldest backup files are deleted
  5. Email the backup status to an email address.

There are six files that I use in this Windows solution.

  1. mybackup.bat - The main backup script
  2. backupconfig.bks - The NTBackup list of files to backup (Do NOT edit this by hand - use Backup to do so)
  3. mysftp.tlp - The Tunnelier (sftp client) configuration file
  4. deleteoldfiles.vbs - The VBA script to delete old archived files
  5. sftp.bat - The secure FTP script to run to move file to another server
  6. sftp.txt - The sftp commands used by the sftp.bat script

I maintain these files in a directory structure like:

    + E:\Archive

...where the backups are archived locally in the E:\Archive\Backups folder and the scripts are maintained in the E:\Backups\Scripts folder. When the backup runs the file is saved in E:\Archive, but is copied to the Backups sub-folder at the end of the script.

In order to setup the backup process one should open NTBackup (Start > All Programs > Accessories > System Tools > Backup) in advanced mode. Select the Backup tab then click on Job > Load Selections. Open the above ".bks" file and configure which folders and files you want backed up. Save the job (which should save it where you opened it from.

Next, you'll need to create a "Scheduled" task (Start > All Programs > Accessories > System Tools > Scheduled Tasks). Create a scheduled task to run the main backup script "E:\Archive\Scripts\mybackup.bat" and make sure it starts in "E:\Archive\Scripts". You'll want to run this backup as the administrator because, lord knows what happens if you don't.  :-)

Here are the scripts:

rem ** This is a backup script to save the needed files and ftp them to
rem ** another server on the network.  We'll do the backup, zip it up and
rem ** ftp it to an internal ftp server on the network (at 192.168.1..).
rem ** Last Modified: 18 Mar 2007
rem ** Modified by--: wph
rem **********************************************************************
rem ** Set local variables
rem **********************************************************************

@set currMon=%date:~4,2%
@set currDay=%date:~7,2%
@set currYr=%date:~10,4%
@set [[DaysToSave]]=15
@set [[EmailDir]]=C:\inetpub\mailroot\Pickup
@set [[ToEmail]]
@set [[FrEmail]]=[[MyDev]]
@set [[ToDir]]=E:\Archive\
@set [[ToArchive]]=%[[ToDir]]%Backups\
@set [[ScriptFile]]=%[[ToDir]]%Scripts\
@set [[FilePrefix]]=ADev_
@set [[ConfigFile]]=[[BackupConfig]].bks
@set [[DeleteFile]]=[[DeleteOldFiles]].vbs
@set currName=%[[FilePrefix]]%%currYr%%currMon%%currDay%.bkf
@set currLog=%[[FilePrefix]]%%currYr%%currMon%%currDay%.log
@set zipName=%[[FilePrefix]]
@set rarName=%[[FilePrefix]]%%currYr%%currMon%%currDay%.rar

rem **********************************************************************
rem ** Begin building log/email file
rem **********************************************************************

echo x-sender: %[[FrEmail]]% >> %[[ToDir]]%%currLog%
echo x-receiver: %[[ToEmail]]% >> %[[ToDir]]%%currLog%
echo From: %[[FrEmail]]% >> %[[ToDir]]%%currLog%
echo To: %[[ToEmail]]% >> %[[ToDir]]%%currLog%
echo Subject: My backup >> %[[ToDir]]%%currLog%
echo.>> %[[ToDir]]%%currLog%
echo.>> %[[ToDir]]%%currLog%

echo My Backup now starting on %date% at %time% >> %[[ToDir]]%%currLog%
echo.>> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Delete oldest archived backups
rem **********************************************************************

echo deleting archive older than %[[DaysToSave]]% days on %date% at %time% >> %[[ToDir]]%%currLog%
cscript %[[ScriptFile]]%%[[DeleteFile]]% %[[ToArchive]]% %[[DaysToSave]]% >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Pause the [[UniData]] dbms for the backup
rem **********************************************************************

echo pausing the [[UniData]] dbms on %date% at %time% >> %[[ToDir]]%%currLog%
dbpause >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Execute the actual Windows backup
rem **********************************************************************

echo starting Windows NTBackup on %date% at %time% >> %[[ToDir]]%%currLog%
@ntbackup backup "@%[[ScriptFile]]%%[[ConfigFile]]%" /m normal /j "My Backup" /f "%[[ToDir]]%%currName%" /l:f >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Resume the [[UniData]] dbms for the backup
rem **********************************************************************

echo resuming the [[UniData]] dbms on %date% at %time% >> %[[ToDir]]%%currLog%
dbresume >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Zip up the file
rem **********************************************************************

echo ...zipping (RAR) is now starting on %date% at %time% >> %[[ToDir]]%%currLog%
D:\[[WinRAR]]\rar.exe A %[[ToDir]]%%rarName% %[[ToDir]]%%currName%

rem **********************************************************************
rem ** Ftp the backup to another server
rem **********************************************************************

rem echo ftp'ing files to My Dbms server on %date% at %time% >> %[[ToDir]]%%currLog%
@call E:\Archive\Scripts\sftp.bat >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Copy the current backup file and log
rem **********************************************************************

echo.>> %[[ToDir]]%%currLog%
echo archiving backup file on %date% at %time% >> %[[ToDir]]%%currLog%
@copy %[[ToDir]]%%rarName% %[[ToArchive]]%%rarName% >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Delete the current backup file and zip file
rem **********************************************************************

echo deleting backup file(s) %[[ToDir]]%%currName% >> %[[ToDir]]%%currLog%
@del %[[ToDir]]%%currName% >> %[[ToDir]]%%currLog%
@del %[[ToDir]]%%rarName% >> %[[ToDir]]%%currLog%
rem @del %[[ToDir]]%%zipName% >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Finish updating the log/email file
rem **********************************************************************

echo.>> %[[ToDir]]%%currLog%
echo My server backup now completed on %date% at %time% >> %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** Copy log to email directory and storage then delete it
rem **********************************************************************

@copy %[[ToDir]]%%currLog% %[[EmailDir]]%
@copy %[[ToDir]]%%currLog% %[[ToArchive]]%%currLog%
@del %[[ToDir]]%%currLog%

rem **********************************************************************
rem ** End script
rem **********************************************************************


This next file is the NTBackup configuration file. This file should never be edited by hand. It should be maintained via the Backup programs provided by Windows. An example looks like (ignore the

   C:\Documents and Settings\ <br/>
   C:\Documents and Settings\myuser\My Documents\ [[/Exclude]]  <br/>
   E:\[[AsiShare]]\ <br/>
   E:\[[DataTrust]]\ <br/>
   E:\IBM\ <br/>
   E:\IBM\Tools\ [[/Exclude]] <br/>
   E:\IBM\unishared\ [[/Exclude]] <br/>

The third file is VB script file used to purge old archives. It looks like:

' Deletes old files based on age. You must specify a target 
' directory and max age. This script looks for these items
' on the command line - The directory is the first argument, 
' and the max age is the second argument. If no arguments are
' supplied, the environment is checked for [[KILL_FILES_IN]] and 
' [[MAX_FILE_AGE]]. If no environment variables are found, the user 
' is asked.
' This script will recursively review sub-directories and remove
' any files that meet the age criteria there.
' Sample call: 
' cscript [[DeleteOldFiles]].vbs C:\Windows\Temp 15
' This deletes all files within the "C:\Windows\Temp" directory,
' and its subdirectories, that are older than 15 days old

' Initialization
Set objArgs = WScript.Arguments
[[FolderName]] = objArgs(0)
Days = objArgs(1)

' Set objects to get folders
set fso = createobject("scripting.filesystemobject")
set folders = fso.getfolder([[FolderName]])

' Calculate starting and ending dates
datetoday = now()
newdate = dateadd("d", Days*-1, datetoday)

' Output today
wscript.echo "File deletion process started at " & now()
wscript.echo ""
recurse folders 
wscript.echo "File deletion process finished at " & now()
wscript.echo ""

' Move through passed folder and delete old files
sub recurse( byref folders)
   set subfolders = folders.subfolders
   set files = folders.files
   [[DelCnt]] = 0
   for each file in files
      if file.datelastmodified < newdate then
         [[DelCnt]] = [[DelCnt]] + 1
         if [[DelCnt]] = 1 then
            wscript.echo "Deleting Files under " & folders.path & " older than " & newdate
	    wscript.echo "--------------------------------------------------------------------------"
         End If
         wscript.echo "Deleting " & folders.path & "\" & & " last modified: " & file.datelastmodified
         on error resume next
      end if

' Print blank like at end of deletion process (separate directories)
   if [[DelCnt]] > 0 then
      wscript.echo ""
   end if

' Delete files within subfolders (this is cool)
   for each folder in subfolders
      recurse folder

' Clear out objects from memory
   set subfolders = nothing
   set files = nothing

end sub

This file is interesting. It is designed to delete all files within a directory structure if they are older than the passed in number of days. What is interesting is the fact that it traverses all sub-directories looking for files to delete. I use it on one of our servers that stores backups from all the other servers. Output would look like:

File deletion process started at 6/27/2007 10:55:00 PM

Deleting Files under E:\Archive\Backups\[[AsiAsp1]] older than 6/17/2007 10:55:00 PM
Deleting E:\Archive\Backups\[[AsiAsp1]]\[[D3Lc_20070616231414]].d3p last modified: 6/16/2007 11:34:11 PM
Deleting E:\Archive\Backups\[[AsiAsp1]]\db_200706162300.tgz last modified: 6/16/2007 11:35:11 PM

Deleting Files under E:\Archive\Backups\[[AsiAsp2]] older than 6/17/2007 10:55:00 PM
Deleting E:\Archive\Backups\[[AsiAsp2]]\Asp2_20070616.log last modified: 6/16/2007 11:38:24 PM
Deleting E:\Backups\Archive\[[AsiAsp2]]\Asp2_20070616.rar last modified: 6/16/2007 11:38:24 PM

Deleting Files under E:\Archive\Backups\[[AsiDotNet1]] older than 6/17/2007 10:55:00 PM
Deleting E:\Archive\Backups\[[AsiDotNet1]]\Ht1_20070616.log last modified: 6/16/2007 11:40:18 PM
Deleting E:\Archive\Backups\[[AsiDotNet1]]\Ht1_20070616.rar last modified: 6/16/2007 11:37:19 PM

Deleting Files under E:\Archive\Backups\[[AsiDotNet2]] older than 6/17/2007 10:55:00 PM
Deleting E:\Archive\Backups\[[AsiDotNet2]]\ht2_20070616.log last modified: 6/16/2007 11:32:01 PM
Deleting E:\Archive\Backups\[[AsiDotNet2]]\ht2_20070616.rar last modified: 6/16/2007 11:32:01 PM

Deleting Files under E:\Archive\Backups\[[AsiWeb1]] older than 6/17/2007 10:55:00 PM
Deleting E:\Archive\Backups\[[AsiWeb1]]\tar_200706162330.tgz last modified: 6/16/2007 11:31:49 PM

File deletion process finished at 6/27/2007 10:55:00 PM

The next file is the sftp script used to actually ftp the backup file to another server. It looks like:

@echo OFF
rem * *******************************************************************
rem *                   This program is provided by:                    *
rem *                      Advantos Systems, Inc.                       *
rem *             (C) Copyright 2004, All Rights Reserved               *
rem *             Web Site URL:               *
rem *                   e-mail:                  *
rem * *******************************************************************
rem *
rem * This batch and text file illustrate the use of SFTP to upload an
rem * ASCII file and an EBCDIC or Binary file. The sftp.bat batch file
rem * references sftp.txt that contains...
rem *
rem *   cd [[MyDomainDir]]
rem *   put *.rar
rem *   put *.log
rem *   quit
rem *
@echo ON
@cd /d E:\Archive
sftpc -profile="E:\Archive\Scripts\[[MyMachine]].tlp" -cmdFile="E:\Archive\Scripts\sftp.txt"

This uses a piece of client software called Tunnelier from Bitvise. I believe any other client software would do the same, as long as it has command-line functionality. I use the client GUI to connect to the sftp server and negotiate all the secure stuff. Once done, it works just fine from within the backup script.

The final file is the sftp commands used by the above script. A typical file would look like:

cd [[MyDomainDir]]
put *.rar
put *.log

The Tunnelier connection script contains the base directory to connect to, so we're changing directories from this base directory. I keep this script in the "E:\Backups\Scrips" folder as the sixth file.

I hope this is useful.  :-)