Scheduled Tasks

 

Back Office > Administration > SQL Tools > Database Maintenance > Advanced Tasks > Scheduled Tasks

 

Description

 

This menu item is used to create scheduled tasks for database backup and maintenance purposes. The tasks that can be performed are Backup and Reindex/Shrink. Both of these functions need to be done to maintain the health of the database.

 

To Be Considered

 

  1. Is recommended to run Scheduled Tasks manually to ensure that enough time is allocated to each Task to complete before the next Task is started.

 

Scheduled Tasks

 

With products like https://www.dropbox.com/ or https://onedrive.live.com/ where you can get 5GB of free storage, it is not difficult to work out a simple backup strategy that will keep the customer safe. The solution is to backup your important files directly to the dropbox folder (Usually called C:\Users\%USERNAME%\Dropbox) or (C:\Users\%USERNAME%\OneDrive) and the syncing to the web will ensure that gets backed up away from the local computer. How often you do this will depend if you pay for uploads. Some ISP's charge for uploads  so you may be happy to only do this backup weekly instead of each day.

The benefit of using these backup services is that they keep backup copies under version control so you can go back to a prior version that has not been damaged if required.

 

 

Grid

 

  • Task Name - Displays the name of the Task.
  • Status - Displays the status of the Task.
  • Last Date/Time - Displays the last Date/Time the Task was run.
  • Next Date/Time - Displays the next Date/Time the Task will be run.
  • Create - Select to invoke the Scheduled Task Wizard to create a new Task.
  • Drop Now - Select to remove the selected Task from the grid
  • Refresh - Select to refresh the grid. Use to refresh the Status.
  • Start Now - Select to run a Task now.
  • Close - Select to exit.

 

Scheduled Task Wizard

 

  1. Create Task

Task Type

 

  • Task Type - Select from the drop down list one of the following available Tasks:

    • Database Backup
    • Database Shrink
    • Database Reindex


  • Task Description - Displays/Enter an appropriate description for the current Task being created.

 

Database Details

 

  • Set SQL Details - Select to open the SwiftPOS - SQL Server Login screen and enter the login details required to establish a connection to the database for which the current Task is being created. This is mandatory.
  • Displays the SQL Server. The following fields are populated by the Set SQL Details button: SQL Server, Database, Integrated Security, Username and Password.

 

Footer

 

  • Back - Select to return to the previous step in the wizard.
  • Next - Select to invoke the Run Time step.
  • Cancel - Select to exit the wizard.

 

  1. Run Time

  • Begin the task - Select from the drop down list one of the following available Tasks:

    • One time only
    • Daily
    • Weekly
    • Monthly


  • Start Date/Time - Displays/Enter the start date and the time the Task is to run.
  • Additional Settings - Set the additional settings (if any) displayed depending on the above selection.

 

Account Information

 

  • Domain/User Account / Password - Set the Username & Password of the Windows user account that will run the scheduled task. Note : This user account MUST be an Administrator account, and MUST have a password.

 

  1. Options

These options will only be displayed when the  Database backup Task Type is selected above.

 

  • Filename format - Select either one of the options displayed to determine the format of the filename to be used when creating the backup file. If you want to have a unique backup each time you backup then select the first option. If you are happy to have a single backup each day of the week, then choose the second option and it will just overwrite the file for that day, the following week. This saves you from filling up your hard disk with backup files. 
  • Copy Backup To - Select to open the Browse for Folder screen to select a folder to which a copy of the database will be written.
  • Create Backup In - Select to open the Browse for Folder screen to select a folder to which the backup of the database will be written. Note :  If no folder is selected, the default backup folder is C:\SwiftPOS
  • Move Backup To - Select to open the Browse for Folder screen to select a folder to which a the database will be moved to.
  • Compress - Select to create a zipped backup file.

 

  1. Summary

This step displays a summary of the selections/settings made in the previous steps of the wizard. Select Finish to create the Task and exit the wizard, or select Cancel to exit without creating the Task. Select Back to return to the previous step in the wizard.

 

Editing Scheduled Tasks in Windows

 

Scheduled Tasks created using SwiftPOS Back Office can also be edited via the Microsoft Windows Task Scheduler tool.

 

To access it select in windows > Start > Control Panel > Administrative Tools > Task Scheduler.

 

 

If the scheduled tasks do not run, check your security user/password on the windows scheduled task. It maybe that the user or password has changed since the scheduled tasks were created.

 

Backing up Databases Using Windows BATCH Files

 

It is recommended to use the SwiftPOS Back Office Scheduled Tasks feature to do this.

 

Description

 

The Swiftpos.SQLCMDUtilities.exe is an application that is part of the SwiftPOS suite of software and it is designed to create scheduled tasks that can be automated in Windows to occur on a daily basis. For Microsoft information regarding scheduling a task in Windows see http://windows.microsoft.com/en-US/windows7/Schedule-a-task

 

Example text to add to a file called C:\SwiftPOS\Backup.BAT for a Windows 32bit Operating Systems

"C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:SwiftPOS /L:C:\Backups

"C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:TableTracking /L:C:\Backups

"C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:SwiftPOS

"C:\Program Files\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:TableTracking

 

Example text to add to a file called C:\SwiftPOS\Backup64.BAT for a Windows 64bit Operating Systems

"C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:SwiftPOS /L:C:\Backups

"C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /B1:TableTracking /L:C:\Backups

"C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:SwiftPOS

"C:\Program Files (x86)\Samford Software\BackOffice\SwiftPOS.SQLCMDUtilities.exe" /R:TableTracking

 

"Double Click" on the file called Backup.BAT or Backup64.BAT to run them manually to make sure they work correctly.

https://www.howtogeek.com/school/using-windows-admin-tools-like-a-pro/lesson2/all/ - How to create scheduled tasks in Windows that will do the backup and reindex as a single command.

 

CMD Command Line Switches

 

SwiftPOS.SQLCMDUtilities.exe [/B[:databasename]] [/C:[copy backup to location]] [/L:[backup location]] [/B1[:databasename]] [/BC] [/BT] [/M:[destination path]] [/O:[sqlservername]] [/R[:databasename]] [/S[:databasename]] [/P:[password]] [/U:[username]] [/Z]

 

Switch

Description

/?

Display this information.

/B

Initiate a backup of the last SwiftPOS Back Office connected database Filename format is: yyyyMMddTHHmmss-{Databasename}.bak

Optional ':' with database name can be used to backup a different database on the last server SwiftPOS Back Office connected to.

/C and/or /L options can be used with this command.

/B1

Initiate a backup of the last SwiftPOS Back Office connected database Filename format is: {Databasename}-{DayOfWeekName}.bak

Optional ':' with database name can be used to backup a different database on last server SwiftPOS Back Office connected to /C and/or /L options can be used with this command.

/BC

Initiate a backup of the last SwiftPOS Connect connected database.

Filename format is: {Databasename}-{DayOfWeekName}.bak saved into same location as MDF database file.

/BT

Initiate a backup of the last SwiftPOS Connect connected TableTracking database.

Filename format is: {Databasename}-{DayOfWeekName}.bak saved into same location as MDF database file.

/C

Specify a location to copy the backup file to.

Remember UNC path security limitations when setting up a scheduled task.

/L

Specify a location to create the backup file in.

Default is to use same path as the MDF database file.

Note : UNC paths may not be fully supported by SQL Server.

/M

Move backup file/zip to destination path.

Useful if you wish to move the final backup file to a UNC Path.

/O

Override the SQL server to connect too. Default is to connect to the last SwiftPOS Back Office connected server.

/R

Initiate a reindex of the last SwiftPOS Back Office connected database.

Optional ':' with database name can be used to reindex a different database on last server SwiftPOS Back Office connected to.

/S

Initiate a shrink of the last SwiftPOS Back Office connected database.

Optional ':' with database name can be used to shrink a different database on last server SwiftPOS Back Office connected to.

/P

Specify a password to login into the SQL server with.

/U

Specify a username to login into the SQL server with.

/Z

Compress backup to ZIP file of same name as backup file.

THIS SETTING IS ONLY VALID WHEN RUNNING ON THE SAME MACHINE AS THE SQL SERVER

 

If you are moving data via a UNC Path

 

Please use the /M {Move} or /C {Copy} option keeping in mind UNC path authorisation required by the scheduled task user who is running the task.

 

Practical Examples of the SQLCMDUtilities

 

Command

Description

SwiftPOS.SQLCMDUtilities.exe /B1 /L:C:\Backups SwiftPOS.SQLCMDUtilities.exe /BT:TableTracking /L:C:\Backups

This example will backup the SwiftPOS, and TabletTracking database to the C:\Backups folder.

SQLCMDUtilities.exe /B1:SwiftPOS /L:C:\Backups /Z

This example will backup the SwiftPOS database to the C:\Backups folder and Zip it up.

"C:\SwiftPOSConnect\Swiftpos.SQLCMDUtilities.exe" /B1:SWIFTPOS /L:C:\backups /Z /O:(local)\SWIFTPOS /M:M:\Backups /U:{SQL User Name} /P:{SQL User password}

This will backup the SwiftPOS database to the C:\Backups folder and move it to a different PC.

"C:\SwiftPOSConnect\Swiftpos.SQLCMDUtilities.exe" /B1:TableTracking /L:C:\backups /Z /O:(local)\SWIFTPOS /M:M:\Backups /U:{SQL User Name} /P:{SQL User password}

This will backup the Table Tracking database to the C:\Backups folder and move it to a different PC.

 

Create a Scheduled Task

 

In Windows via Start > Control Panel > Administrative Tools > Task Scheduler

 

Via the Windows Task Scheduler create a Task to run the newly created batch files and schedule it to run at the preferred times.

 

 

 

Related Topics

 

  1. Backup a Database
  2. Restore a Database
  3. SQL Server Management Studio
  4. SQL Tools

 

Published 28 Mar 2024