Sample scripts are provided as-is with no warranty of fitness for a particular purpose. These scripts are solely intended to demonstrate techniques for accomplishing common tasks. Additional script logic and error-handling may need to be added to achieve the desired results in your specific environment.
This sample script was written for use with Robo-FTP client v3.10.
Log Shipping is a technique for keeping a warm backup of your SQL Server database. Robo-FTP is an ideal choice for offsite log shipping because of its ability to execute the SQL commands to create database backup files and then send them to a remote server using a secure, encrypted connection.
To configure a system to send backups for offsite log shipping, first create a Windows user account and grant the user full permissions on the ProgramData folder under the Robo-FTP installation folder. Next, use the SQL Server Management Studio to create a SQL Server Login for the new user with Windows authentication instead of SQL Server authentication. Grant the Login dbowner or dbbackupoperator permissions on the database. Note: To use log shipping, a database's Recovery Model must be set to either Full or Bulk-Logged.
Now use the Windows ODBC Data Source Administrator program to create a system DSN to access your database with Windows authentication. Note: If you are using 64-bit Windows be sure to run the version of
odbcad32.exe under the
sysWOW64 folder. DSNs created by the 64-bit version of the ODBC Data Source Administrator (located in the
system32 folder) will not work with Robo-FTP.
Use the Robo-FTP Configurator to create a new Managed Site record. It should be named "LogShipping" and must contain the server address and all credentials needed to connect to the destination server.
Download this sample script and save it as
send_transaction_log.s (use link above) then modify the dbName and odbcDSN settings in the script to match your database name and the name of your 32-bit DSN.
As the user you created run the script in the main console to verify that it creates a database transaction log backup file and sends it to the remote server. After confirming this behavior, Set the scheduler in the configurator to run as the user you configured for log shipping and schedule the script to run as frequently as you wish using the Robo-FTP Scheduler.
Robo-FTP Server software can be installed on the remote (secondary) database server and used to automatically restore transactions logs in real-time, as they are received.
1 BEGINFUNCTIONS 2 FUNCTION BackupLog 3 DBUSE odbcDSN /odbc 4 IFERROR RETURN 5 SET BackupLog_file = staging + "\" + dbName + "_" + %datetime + "_log.bak" 6 DBQUERY "USE master" 7 IFERROR RETURN 8 SET sSQL = "BACKUP LOG " + dbName + " to disk='" + BackupLog_file 9 SET sSQL = sSQL + "' WITH INIT, SKIP, NAME='Transaction Log Backup'" 10 DBQUERY sSQL 11 IFERROR RETURN 12 DBCLOSE 13 RETURN $ERROR_SUCCESS 14 ENDFUNCTION 15 ENDFUNCTIONS 16 17 SET dbName = "MySQLServerDB" 18 SET odbcDSN = "MySQLServerDB_Offsite" 19 SET ManagedSite = "LogShipping" 20 SET staging = %installdir + "\ProgramData\LogShipping" 21 SET archive = staging + "\archive" 22 ARCHIVEDIR archive 23 24 BackupLog 25 IFERROR RETURN 26 27 WORKINGDIR staging 28 IFERROR RETURN 29 30 FTPLOGON ManagedSite 31 IFERROR RETURN 32 FTPTMPNAME "" ".partial" 33 34 :send_files 35 GETNEXTFILE "*" /oldest 36 IFERROR RETURN $ERROR_SUCCESS 37 38 SENDFILE %nextfile /archive 39 IFERROR RETURN 40 GOTO send_files