Script Library

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.

send_transaction_log.s

Download

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

Browse complete list of scripts

See Also