On Site Backup App

Support

What can be Backed up?

Pasted Graphic 1
SQL
Any number of MS SQL Server, MySQL, PostgreSQL and SQLite databases.

FTP
Any number of files and folders from FTP servers.

Directory
Files from any local or network-mapped drives.
How do I begin?

Pasted Graphic 4
On Site Backup001

Start
The Start button begins the countdown until the next Backup.

Stop
The Stop button cancels the countdown timer. If a Backup is running, it will cancel the current and all remaining sets.

Preview
The Preview button allows you to get an approximation of the amount of data that would be backed up if a Backup was requested. It counts the number of SQL records , displays the FTP files list and Directory files list. Preview does not actually perform a Backup of any files.

Now
Click the Now button to start a Backup immediately.
What Preferences can I set up?

Pasted Graphic 3
Pasted Graphic 2
Location
Click the Location button to change where On Site Backup will store the Backup sets and cache files. This can even be a mapped drive (such as DropBox, Google Drive or OneDrive). You can click on the path name to show the enclosing folder.

Zip Compression
When a new Backup is run, the files are stored into a folder called 'Latest Backup'. This folder is not compressed. If a folder called 'Latest Backup' already exists, then that folder will be renamed (with the backup date added) and compressed (if possible). Note that Zip files have a maximum size of about 4 Gigabytes, so it will not compress your backup if it is larger than this size.

Backup At
Shows you the hour that the backup will begin (e.g. 1 am). If left unattended the backup should run automatically once per day at this time. Note, it will not wake the computer from sleep (you may want to set a wakeup time).

Backup Every
Causes the backup to run only on the interval of minutes indicated.

Keep the Last
You can set the number of backups that the program keeps (not including the Last Backup) — the default is to keep them all. Backups in excess to these will be deleted.

After Backup
Allows you to perform a System task once the backup is complete including: do nothing, log out, quit app, restart, shutdown and sleep the computer.

Latest Backup
Show the path to, well, the latest backup! You can click on the path name to show the enclosing folder.

Preferences
Show the path to the encrypted SQLite preferences file. You can click on the path name to show the Preferences folder. It takes a while to set up many Backup sets, so you may want to keep a copy of the preferences file. Please note that you cannot edit the preferences file without the password.
How do I create a new Backup set?

Pasted Graphic
Add
Click the Add button to choose a new backup.

Edit
Click the Edit button (or double-click a backup set) to edit an existing Backup.

Duplicate
Click the Duplicate button to make an exact copy of an existing Backup.

Remove
Click the Remove button to delete an existing Backup. Please note that there is no warning and it cannot be undone!

Options
Once you have created a backup click the Options button to make all the selected backups Active or Inactive. You can also get information about the common SQL database types.

How can I get a Preview of what will be backed up without actually performing a backup?

Pasted Graphic
Preview
The Preview tab displays the list of potential backup items after the Preview button is clicked.
Can I get informed once the backup is complete?

Pasted Graphic 1
Email
The Email tab stores the SMTP settings for your email provider. This allows you to receive an email containing the log file (and error log if available) after the backup has completed. You can add as many email recipients as you want — just separate them with commas.


Pasted Graphic 2
SMS
Stores the SMS account details for
2SMS.com. This allows you to receive a text notifying you after the backup has completed.You can add as many mobile numbers as you want — just separate them with commas, but note that you will be changed for every text sent to multiple recipients.
What happens when my Backup runs?

Your Last Backup (if available) will be renamed and zipped (if requested) and a new folder called ‘Last Backup’ will be created. The Backup will begin with the active SQL sets first, from top to bottom, then the active FTP sets and finally the active Directory sets. If there are no active backup sets for a category then that category will be ignored and will skip to the next category. Any progress in a backup set is displayed in the Backup tab and the FTP Log window. The Backup tab shows the latest Transferring status, a progress bar of any existing transfer and a progress bar for the total Backups.
What is the Previous Backup folder for?

The ‘Previous Backup’ folder contains the cached FTP and Directory files from previous backups. This folder is created on the first Backup and downloaded files are copied both to the Latest Backup folder as well as the Previous Backup folder. After the first backup, all subsequent backups check to see if the FTP file on the server or Directory is identical to the locally cached copy. If it’s values are unchanged, then the local cached file will be placed into the new backup instead of downloading the server file again. This makes the backups extremely fast when most of the files are unchanged (such as web sites). We regularly backup over 30 gigabytes of web files over the internet per night in about 1.5 hours — this would be impossible if they all had to be transferred each time. If files are deleted from the source FTP sites and directories, the cached file still remains. To stop this cache folder getting too large with unnecessary files (e.g. web sites often create temporary files), you may want to get your FTP and Directory backups to ignore any temporary files or folders. For a cached file to be considered a match it must have the same name, be in the same sub-folder, have exactly the same size and have the same modified date — if any one of these doesn’t match the file will be downloaded again.
Are there any Log files created?

There are potentially four log files: Log.txt, Errors.txt, OSB FTP Files.txt and OSB Directory Files.txt. These are included in with the backup folder and give a journal of all the transfers. The Log.txt file shows all files that were added as downloads, as cached files or were ignored. If there were any errors (e.g. FTP server offline) then they will appear in both an on-screen Error Messages window plus in the Errors.txt log. The ‘OSB FTP Files.txt’ log lists the path names of all the FTP files transferred. The ‘OSB Directory Files.txt’ log lists the path names of all the Directory files transferred.
How do I configure an SQL Backup set?

SQL003
SQL Type
Choose the SQL Type from the popup menu. This sets the source of the database data. When you change the SQL Type, the port number changes to the most common port number for that database.

Description
The Description field can be any text.

Host
The URL of the SQL server to connect to. This can be either an IP Address or a domain name. When the SQL brand is SQLite then the Choose button becomes enabled. You must use this button to select your SQLite database. Once selected, the path to the SQLite database is shown in the Host field.

Username and Password
The Username and Password fields are those of the database and not your computer. The Username and Password fields are not available for SQLite databases as it is a file-based database. While SQLite databases can be encrypted with a password, you cannot use On Site Backup to backup an encrypted database via the SQL option. In this case you will need to use the Directory option to backup the whole file.

Port
The port number on the Host that is used to communicate with the database. For MySQL this is 3306 by default. For PostgreSQL this is 5432 by default. SQLite is a file-based database, so the port number is blanked.
SQLServerConfiguration001
MS SQL Servers will need to be configured with a port number as they don’t use port numbers by default — this is set in the SQL Server Configuration application.
MSSQLServerManagementStudio001
They will need to be configured with a username and password as they normally use domain logins — this is set in the MS SQL Server Management Studio application.
SQLServerConfigurationManager001
You can then test if this worked by logging into MS SQL Server using your new username and password.

Database
If you enter a Database name then only that database will be backed up by this Backup set. If you leave the Database field blank then OSB will attempt to determine every database you have access to (other than system and protected databases) and back them all up. Leave this field blank is often the best option as it ensures that new databases created will be added automatically and databases removed don’t produce an error. For SQLite databases this field will contain the path to the SQLite file.

Table
If you enter a Table name then only that table will be backed up by this Backup set. If you leave the Table field blank then OSB will attempt to determine every table within the given database and back them all up. Leave this field blank is often the best option as it ensures that new tables created will be added automatically and tables removed don’t produce an error.

FIELDS
This field lets you download only the listed field names (separated by commas). Unless you want to only download a limited number of fields, then it’s best to leave this field blank.

WHERE
This field lets limit the range of records to be backed up (e.g. city = ‘London’ OR city = ‘Paris’). Unless you want to only download a limited number of records, then it’s best to leave this field blank.

ORDER BY
This field will set the order of the records as they are downloaded (e.g. city, name). It won’t change which records are downloaded.

Test
Click the Test button to verify that the settings you have provided are correct. If they are not correct then the SQL Database error message will be returned.

Restore
Click the Restore button to restore a Backup set. You can only restore the same backup type to the SQL server (e.g. a MySQL Backup to a MySQL server). It will request the Backup set folder and the SQL files must be in unzipped format. You must include a Database name before you can use the Restore command. This database must already exist as it will not be created — but the tables within this database should be created (if the Include Structure was checked at backup time).
This is a dangerous operation since the backup may contain commands to Drop the existing tables, losing all data. If you are not dropping any existing tables then ensure that the Primary Keys don’t cause a problem due to duplicate keys. You may want to restore into a different database first and examine the results before transferring to the main database.

Include Structure
Check the Include Structure checkbox to ensure the Create Table commands are included in the export file. If it is not checked this backup cannot be used to create the necessary table structure, and can only restore into an existing database with identically formatted tables.

Include Content
Check the Include Content checkbox to ensure the Insert data commands are included in the export file. If it is not checked this backup cannot be used to restore the data because it contains no data.

Include Drop Table
Check the Include Drop Table checkbox to ensure the Drop Table command is included in the export file. If it is not checked this backup will try to create the tables when restored, but may give an error if the tables already exist.

Blob as Hex
This field has three options for handling binary BLOB fields: Blob as Binary, Blob as Hex and Ignore Blobs. This only affects binary BLOBS, as character-based BLOBS (e.g. CLOBs) are treated as text fields. When exported as Blob as Binary, the BLOB fields are left unchanged. When exported as Blob as Hex, the BLOB fields are encoded as Hex (0-9, A-F characters) so they will import into most databases, but may need to be decoded later. When exported as Ignore Blobs, the BLOB fields are set to blank.

Save Format CSV
Choose this format to save your database data in comma-separated-value format. This is a common format for transferring to spreadsheets or other databases.

Save Format Tab-Delimited
Choose this format to save your database data in Tab-Delimited format. This is the smallest of all export formats, but is not commonly used for importing.

Save Format XML
Choose this format to save your database data in Extensible Markup Language (XML) format. This format will import directly into spreadsheets such as Excel and OpenOffice.

Save Format SQL Types
OSB support the saving of your SQL data in twelve possible SQL formats: DB2, FireBird, Informix, Interbase, MS SQL, MySQL, Oracle, PostgreSQL, SQLBase, SQLite (text), SQLite (database file) or Sybase. For most databases you will want the same Save Format to match the SQL Type. For each Source SQL brand there is a Save Format the same. This will often use the structure definition from the database itself (e.g. MySQL and SQLite), but for the other databases the structure has to be created based up on the field definitions.
Where the source and save SQL formats differ then OSB will include transcoding of the different data types from one SQL brand to another (e.g. some databases support Boolean while others do not and TinyInt may be used instead). Where there is a missing data type, the closes equivalent datatype is used instead. For this reason it is essential that you test the backups to ensure they can restore their data correctly.
Please note that:
  • Primary keys are transferred, but only where there is one key field involved. Tables with Primary Keys from multiple fields will need to be revised.
  • Indexes are transferred where available, but some indexes may need to be added after a Restore if they are not able to be determined from the data table or cannot be replicated across database types.
  • Auto-Incrementation of Primary Key fields is transferred where available, not in some cases the Auto-incrementation status of a field cannot be determined and may need to be added after a Restore.

Data Types
The data types recognised and transcoded across all SQL database types include:
Integer
AUTOINCREMENT, BIGINT, BIGSERIAL, BIT, BIT VARYING, COUNTER, IDENTITY, INT, INT2, INT4, INT8, INT64, INTEGER, LONG, MEDIUMINT, SERIAL, SMALLINT, SMALLSERIAL, TINYINT, UNSIGNED BIGINT, UNSIGNED INT, YEAR
Decimal
BINARY_DOUBLE, BINARY_FLOAT, CURRENCY, DEC, DECFLOAT, DECIMAL, DOUBLE, DOUBLE PRECISION, FIXED, FLOAT, MONEY, NUM, NUMBER, NUMERIC, REAL, ROWID, SMALLFLOAT, SMALLMONEY, UROWID
Date
ABSTIME, DATE, DATETIME, DATETIME2, DATETIMEOFFSET, INTERVAL, SMALLDATE, SMALLDATETIME, TIME, TIMESTAMP
Text
BOX, BSON, CHAR, CHARACTER, CHARACTER VARYING, CIDR, CIRCLE, CLOB, CURSOR, DBCLOB, ENUM, GEOGRAPHY, GEOMETRY, GEOMETRYCOLLECTION, GRAPHIC, GUID, HIERARCHYID, INET, JSON, LINE, LINESTRING, LIST, LONG, LONG VARCHAR, LONG VARGRAPHIC, LONGCHAR, LONGTEXT, LONGVARCHAR, LSEG, LVARCHAR, MACADDR, MEDIUMTEXT, MEMO, MUTLILINESTRING, MULTIPOINT, MULTIPOLYGON, MULTISET, NATIVE CHARACTER, NCHAR, NCLOB, NOTE, NTEXT, NVARCHAR, NVARCHAR2, PATH, POINT, POLYGON, ROW, SET, SPATIAL, SQL_VARIANT, TABLE, TEXT, TIMESERIES, TINYTEXT, TSQUERY, TSVECTOR, TXID_SNAPSHOT, UNIQUEIDENTIFIER, UNIQUEIDENTIFIERSTR, UUID, VARCHAR, VARCHAR2, VARGRAPHIC, VARYING CHARACTER, XML
Boolean
BOOL, BOOLEAN, LOGICAL, YESNO
Binary
BFILE, BINARY, BLOB, BYTE, BYTEA, GENERAL, IMAGE, LONGBLOB, LONG BINARY, LONG RAW, MEDIUMBLOB, OLEOBJECT, RAW, TINYBLOB, VAR BINARY, VARBINARY
If you think we have missed a data type or transcoded it incorrectly then please use the contact form to
contact us so we can fix it for you.
How do I configure an FTP Backup set?

FTP002
Description
The Description field can be any text, but don’t change it unless necessary since the description is used in the Previous Backup cache folder name.

Host
The URL of the SQL server to connect to. This can be either an IP Address or a domain name.

Port
The port number for FTP servers is usually 21 but sometimes port 20 is used.

Username and Password
The Username and Password fields are those of the FTP server and not your computer. There may be directories that your login account doesn’t have access to — these directories may result in an error message and their contents will not be downloaded.

Directory
Your FTP login account will place you in a default directory set by the FTP server. Leave this field blank if you wish to back up all files from this level and all sub-folders. If you wish to only backup one of the sub-folders, then include it’s name in this field ending with a ‘/‘ character to indicate a directory.

Timeout
Enter a number of seconds in this field to indicate how long to wait on connecting to an FTP server before timing out. Once connected there is no timeout from OSB so you can download very large files, but the FTP server itself may maintain it’s own timeouts.

Buffer
The size of the memory buffer in characters that is set aside by OSB for transferring files to the server. This has little affect on download performance and no affect on the FTP server’s internal buffers.

Encrypt
This popup sets the security level of the transfer and has five possible options:
  • None — neither the initial connection, nor the FTP data transfer will be encrypted. This is extremely insecure since your password will be transferred in clear text. Use this only on older FTP servers that don’t support secure connections, and only over secure networks!
  • SSL Try — OSB will try to connect to the FTP server via SSL, but if that fails it will revert to the insecure FTP transfer.
  • SSL Control — the initial connection to the FTP server must allow a secure connection, otherwise the transfer will fail. Once connected the FTP data transfer will be not encrypted. This is fairly insecure since your data will be transferred in clear text and may include confidential field data.
  • SSL All — both the initial connection to the FTP server and the data transfer itself must allow a secure connection otherwise the transfer will fail. This is the most secure method of transfer.
  • SFTP — uses the SSH FTP protocol for both the initial connection to the FTP server and the data transfer itself. Not all FTP servers support SFTP. If SFTP is not available, the transfer will fail.

Ignore
Click the ‘+’ and ‘-‘ buttons to add and remove textual matches to ignore. If any of the included text items are in the path name or file name to the FTP file then that file (or directory) will be ignored and not downloaded.
How do I configure a Directory Backup set?

Directory002

Description
The Description field can be any text, but don’t change it unless necessary since the description is used in the Previous Backup cache folder name.

Directory
Click the Directory button to choose the folder you wish to back up. You can only choose one folder per backup set. If you want to backup more folder (other than sub-folders) then either choose a lower folder then mark those you don’t want to be ignored, or create additional backup sets.

Ignore
Click the ‘+’ and ‘-‘ buttons to add and remove textual matches to ignore. If any of the included text items are in the path name to a Directory file then that file (or directory) will be ignored and not downloaded.
Note that Macintosh and Linux use the ‘/‘ to delimit folders while Windows uses the ‘\’ character. Plus aliases and shortcuts being backed up will backup the actual file being pointed to,
not the alias itself.
FAQ

Can I run On Site Backup on as many servers as I want?
Yes. You may create an unlimited number of backup sets to an unlimited number of SQL servers, server instances, FTP sites and Directories. Please don’t give your OSB serial number to anyone else — it is for your use only.

Are there any record limits to On Site Backup?
Yes.
  • OSB cannot download single tables larger than 4 GB (approximately). OSB has been tested with database tables containing millions of records, but eventually there must be a limit on the number of records that can be backed up in one operation due to the RAM limits on your computer. If your database table has over 1 million records then it will download it in 1 million record chunks. If any of these 1 million record chunks is larger than 4 GB then the backup of that table will fail. If you want to backup up a database with massive (tens of millions) of records or with massive BLOBs (binary large object fields), CLOBs or other fields, then please create multiple backups using the WHERE command or get the database to create its own backup then download it via FTP.
  • Binary data types are converted into TEXT fields for compatibility as the data is normally converted into Hexadecimal format. After a restore you may need to decode the Hex data and convert the field type back to a Binary data type.
  • Certain Integer field types (INT, INTEGER, BIGINT, SERIAL, BIGSERIAL and TINYINT) are not given their precision number values (within brackets after the data type in the CREATE TABLE description) for maximum compatibility reasons.
  • There is no allowance made for illegal characters, illegal field lengths or pre-reserved field names. Some databases (e.g. SQLite) allow fields to contain longer text than their data type description allows without giving an error. The full data length will be backed up, but may give an error when Restoring to a database which will not allow illegal characters or lengths of data.
  • The data is backed up as-is. If you want to insert a backup into an existing database then you must resolve any conflicting primary keys (e.g. backup the table with all fields except the primary key). Note that when a table structure is created it includes all known fields, even if only a few fields of data are being backed up.

Can I perform an incremental backup?
No. The SQL backups are never cached and always backup all the records every time. The FTP and Directory backups are cached for speed, but every backup is a full backup. Incremental backups are useful for speed and save drive space, but since OSB only downloads the incremental files anyway it is very fast. With our inexpensive, multi-terabyte drives today plus OSB’s compression space is not really an issue any more.

Can I backup to tape drives?
No. You can only backup to a mapped hard drive, but that mapped drive can be a networked drive (e.g. DropBox, GoogleDrive or OneDrive) but this may be much slower than a real hard drive.

Can I backup to an FTP site?
No.

Is the SQL Backup cluster-aware?
No.

Can I keep an unlimited number of my Backup versions?
Yes, until you run out of drive space!

Is the resulting backup small?
Yes. Each individual Backup set can be Zip compressed after it is complete (up to 4 GB). When a backup is no longer the Last Backup it can also be compressed again as a whole.

Can the zip compression also be password protected?
No. Your backups are a potential weak point in your security, so keep them well guarded.

What versions of SQL brands can be backed up?
MS SQL Server (or Express) 2012 or later (needs the OFFSET and FETCH commands). MySQL version 5 or later (new password scheme). PostgreSQL 9 or later (may work with earlier versions). SQLite 3 or earlier (may work with earlier versions).

What FTP types can be backed up?
As far as we’re aware any brand of FTP server supporting plain FTP, FTP via SSL (FTPS) or SSH FTP (SFTP). Files and directories with illegal characters in their name for that platform (‘\’ of Windows, ‘:’ for Macintosh, and ‘/‘ for Linux) may have those characters replaced on that platform. The FTP directory structure is replicated locally, even with matching empty folders.

What Directory types can be backed up?
As far as we’ve tested anything that maps as a readable (doesn’t have to be writable e.g. data CD-ROM) drive letter or name. File names are treated as UTF8, so accented filenames should be preserved, but this will depend on the Zip format too. Aliases (and Shortcuts and links) to files and folders will back up the original files (and not the aliases themselves), but be careful check a backup first before relying on this. You must ensure all aliases resolve to available files too, otherwise they will not be included in the backup. Note that aliases to Macintosh applications might not copy properly.

Can I run the application on other operating systems?
Yes. OSB runs on Windows, Linux and Macintosh. While most functionality is identical across the three platforms, there may be some differences.

How much RAM should I have in my computer?
The application itself doesn’t take much CPU or RAM, nor do FTP or Directory backups, so 2-4 GB RAM should be sufficient for just these operations. The RAM needs will be an issue for large SQL backups. OSB tries to ensure that RAM usage is limited even for massive numbers of records, but this is not always feasible. In these circumstances, 8-16 GB RAM is preferred.

Can I rent storage from OSB for my backups? Or can you perform the backups nightly for me?
Possibly. Please contact us with your needs proposal — we have servers in the USA and Europe.

Are there any hidden costs?
Not that I can think of. As the product is improved, you should get the latest version automatically.

Is there anything On Site Backup is not good for backing up?
Yes. Databases often include their own backup tools for remembering where they were up to at a given time and taking an accurate timely snapshot for all database tables, especially where there is a high transaction volume. OSB cannot provide this level of service. In this case, get the database itself to create the export file then use OSB to FTP this file instead. Open files (e.g. shared Access MDB databases) may also cause a problem for FTP servers.

How do I get support?
Use the
contact form to send us an email.

Can I get my money back?
Yes. If you purchased through the Apple Store you need to contact them for a refund. If you purchased OSB via the web site and received a serial number we are happy to cancel the purchase through PayPal within one month of purchase. You are thereby agreeing that you will not use OSB or the serial number again, nor give it to anyone else to use.
Please don’t purchase OSB until you are happy that it will meet your needs. Download the application first and use the Preview option to see if it will work for you before you purchase it. If you find one facility (SQL, FTP or Directory) doesn’t meet your needs, please also consider using the other facilities instead.

What other issues are there to consider?
Time is money. How much admin time do your backups take?
What would be the cost if you had no backup at all?
Will you need to backup more than just databases? Just FTP sites? Just directories? Why not have them all in one place!
Will I need only MS SQL support? Does my organisation also use MySQL or PostgreSQL? If you need more, then please contact Holy Mackerel Software (makers of On Site backup).