Frequently Asked Questions

The FAQ list is a great starting point for basic questions about what you can do with Robo-FTP.

Orange_arrowOrange_arrow Browse the FAQ list now

Knowledge Base

Our searchable knowledge base covers most of the common errors and implementation problems you are likely to encounter.

Orange_arrowOrange_arrow Search the Knowledge Base now

Contact Technical Support

If you can't find the answers you need online, get in touch with our Technical Support staff.

Orange_arrowOrange_arrow Contact Technical Support now

Professional Services

Need more than basic technical support? Our Professional Services team can help you deploy a complete solution.

Orange_arrowOrange_arrow Contact Professional Services now

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.


rename_restore_db.s

Download

This sample script was written for use with Robo-FTP Server v3.3. The steps may vary slightly for newer or older versions. This sample script is NOT for use with the Robo-FTP client.

Log Shipping is a technique for keeping a warm backup of your SQL Server database. The features of Robo-FTP Server make it an ideal choice for offsite log shipping. With Robo-FTP Server, you can receive database backup files via encrypted protocols and then automatically restore database backup files in real-time, as they are uploaded.

The sample script below is triggered by the Rename Complete event because it is designed to work together with a Robo-FTP Client script that automatically uploads files as <filename>.partial and then renames the file after confirming the integrity of the uploaded file. When triggered, it connects to Microsoft SQL Server and executes SQL commands to restore a backup file.

To configure a system to act as a remote log shipping destination, first create a Windows user account and grant the user full permissions on the Robo-FTP Server 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 SQL Server administrator permissions. Create the following RoboRestore stored procedure in the "master" database:

    CREATE PROCEDURE dbo.RoboRestore (@DBName nvarchar(100)
                                    , @BackupFileName nvarchar(256)
                                    , @StandbyFileName nvarchar(256) = Null
                                    , @MDFName nvarchar(256) = Null
                                    , @LDFName nvarchar(256) = Null)
    AS
      SET NOCOUNT ON

      DECLARE @DefaultFolder as varchar(500) 
      SET @DefaultFolder = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1 )
      DECLARE @CMD as varchar(1000)
      SET @CMD = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupFileName + ''''
      DECLARE @Files TABLE 
                    (LogicalName nvarchar(128) null,
                     PhysicalName nvarchar(260) null,
                     [Type] char(1) null,
                     FileGroupName nvarchar(128) null,
                     [Size] numeric(20,0) null,
                     [MaxSize] numeric(20,0) null,
                     FileID int,
                     CreateLSN numeric(25,0),
                     DropLSN numeric(25,0) NULL,
                     UniqueID uniqueidentifier,
                     ReadOnlyLSN numeric(25,0) NULL,
                     ReadWriteLSN numeric(25,0) NULL,
                     BackupSizeInBytes bigint,
                     SourceBlockSize int,
                     FileGroupID int,
                     LogGroupGUID uniqueidentifier NULL,
                     DifferentialBaseLSN numeric(25,0) NULL,
                     DifferentialBaseGUID uniqueidentifier,
                     IsReadOnly bit,
                     IsPresent bit,
                     TDEThumbprint varbinary(32) Null)
      INSERT INTO @Files EXEC(@CMD)
      DECLARE @LogicalNameData as nvarchar(128)
      SET @LogicalNameData = (SELECT LogicalName FROM @Files WHERE [Type] = 'D')   
      DECLARE @LogicalNameLog as nvarchar(128) 
      SET @LogicalNameLog = (SELECT LogicalName FROM @Files WHERE [Type] = 'L')
      IF (@StandbyFileName Is Null) 
                     SET @StandbyFileName = @DefaultFolder + '\' + @LogicalNameData + '_UNDO'
      IF (@MDFName Is Null) 
                     SET @MDFName =  @DefaultFolder + '\' + @LogicalNameData + '_Offsite.mdf' 
      IF (@LDFName Is Null) 
                     SET @LDFName = @DefaultFolder + '\' + @LogicalNameData + '_Offsite_log.ldf' 

      SET @CMD = 'RESTORE HEADERONLY FROM DISK = ''' + @BackupFileName + ''''
      DECLARE @BackupFileContents TABLE
           (BackupName  nvarchar(128), 
            BackupDescription  nvarchar(255) ,
            BackupType  smallint ,
            ExpirationDate  datetime ,
            Compressed  bit ,
            Position  smallint ,
            DeviceType  tinyint ,
            UserName  nvarchar(128) ,
            ServerName  nvarchar(128) ,
            DatabaseName  nvarchar(128) ,
            DatabaseVersion  int ,
            DatabaseCreationDate  datetime ,
            BackupSize  numeric(20,0) ,
            FirstLSN  numeric(25,0) ,
            LastLSN  numeric(25,0) ,
            CheckpointLSN  numeric(25,0) ,
            DatabaseBackupLSN  numeric(25,0) ,
            BackupStartDate  datetime ,
            BackupFinishDate  datetime ,
            SortOrder  smallint ,
            CodePage  smallint ,
            UnicodeLocaleId  int ,
            UnicodeComparisonStyle  int ,
            CompatibilityLevel  tinyint ,
            SoftwareVendorId  int ,
            SoftwareVersionMajor  int ,
            SoftwareVersionMinor  int ,
            SoftwareVersionBuild  int ,
            MachineName  nvarchar(128) ,
            Flags  int ,
            BindingID  uniqueidentifier ,
            RecoveryForkID  uniqueidentifier ,
            Collation  nvarchar(128) ,
            FamilyGUID  uniqueidentifier ,
            HasBulkLoggedData  bit ,
            IsSnapshot  bit ,
            IsReadOnly  bit ,
            IsSingleUser  bit ,
            HasBackupChecksums  bit ,
            IsDamaged  bit ,
            BeginsLogChain  bit ,
            HasIncompleteMetaData  bit ,
            IsForceOffline  bit ,
            IsCopyOnly  bit ,
            FirstRecoveryForkID  uniqueidentifier ,
            ForkPointLSN  numeric(25,0) NULL,
            RecoveryModel  nvarchar(60) ,
            DifferentialBaseLSN  numeric(25,0) NULL,
            DifferentialBaseGUID  uniqueidentifier ,
            BackupTypeDescription  nvarchar(60) ,
            BackupSetGUID  uniqueidentifier NULL,
                    CompressedBackupSize NVARCHAR(128),
                    Containment int)
      INSERT INTO @BackupFileContents EXEC(@CMD)

      DECLARE @spid AS varchar(10)
      DECLARE cur CURSOR FOR 
            SELECT spid 
            FROM master.dbo.sysprocesses 
            WHERE 
                    dbid = (SELECT dbid 
                                    FROM sysdatabases 
                                    WHERE name = @DBName)
      OPEN cur
      FETCH NEXT FROM cur INTO @spid
      WHILE @@FETCH_STATUS = 0
            BEGIN
                    SET @CMD = 'kill ' + @spid
                    EXEC (@CMD)
                    FETCH NEXT FROM cur INTO @spid
            END 
      CLOSE cur
      DEALLOCATE cur

      DECLARE @iPosition smallint
      DECLARE @iBackupType smallint
      DECLARE cur CURSOR FOR
            SELECT Position, BackupType 
            FROM @BackupFileContents 
            ORDER BY Position ASC
      OPEN cur 
      FETCH NEXT FROM cur INTO @iPosition, @iBackupType
      WHILE @@FETCH_STATUS = 0   
            BEGIN   
                    IF (@iBackupType = 1)
                            BEGIN
                                    RESTORE DATABASE @DBName FROM DISK = @BackupFileName 
                                            WITH FILE = @iPosition
                                            , STANDBY = @StandbyFileName
                                            , MOVE @LogicalNameData TO @MDFName
                                            , MOVE @LogicalNameLog TO @LDFName
                            END
           IF (@iBackupType = 2)
                            BEGIN
                                    RESTORE LOG @DBName FROM DISK = @BackupFileName 
                                            WITH FILE = @iPosition
                                            , STANDBY = @StandbyFileName
                                            , MOVE @LogicalNameLog TO @LDFName  
                            END
           FETCH NEXT FROM cur INTO @iPosition, @iBackupType
      END   
    CLOSE cur   
    DEALLOCATE cur 

    DBCC CHECKDB (@DBName) WITH TABLERESULTS, PHYSICAL_ONLY

Verify that the store procedure works by using it to restore a full backup of your database. The calling syntax should be something like this:

RoboRestore 'MySQLServerDB', 'c:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Backup\MySQLServerDB.bak'

Running the stored procedure should leave the database in Standby / ReadOnly mode.

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 Server.

To configure Robo-FTP Server to launch the event script at the appropriate time, follow these steps:

Note: If your production server fails and you need to use your log shipping database as a fallback, activate it with syntax like this:

RESTORE DATABASE MySQLServerDB WITH RECOVERY


  1  SET dbName = "MySQLServerDB"
  2  SET odbcDSN = "MySQLServerDB_Offsite"
  3  LOG "rename_restore_db.log" /append /maxsize=1000 
  4  IFNUM!= %event_renameresult 0 GOTO done
  5  SETRIGHT last8 = %event_renamelocalfile 8
  6  IFNSTRCMPI last8 ".partial" GOTO done
  7  DBUSE odbcDSN /odbc
  8  SET sSQL = "USE master"
  9  DBQUERY sSQL
 10  SET sSQL = "ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
 11  DBQUERY sSQL
 12  SET StandbyUndoFile = %installdir + "\" + dbName + "_undo.bak"
 13  SET sSQL = "RoboRestore '" + dbName + "', '" + %event_renamenewlocalpath + "', '" + StandbyUndoFile + "'"
 14  DISPLAY sSQL 
 15  DBQUERY sSQL
 16  PAUSE /for=1
 17  DBGETRESULTS 
 18  *  expecting: CHECKDB found 0 allocation errors and 0 consistency errors in database ...
 19  DISPLAY %db_MessageText 
 20  SET sSQL = "ALTER DATABASE [" + dbName + "] SET MULTI_USER"
 21  DBQUERY sSQL
 22  DBCLOSE 
 23  SET ArchDir = %event_logonhome + "\archive"
 24  MAKEDIR ArchDir
 25  SET ZName = ArchDir + "\" + %event_renamenewlocalfile + ".done.zip"
 26  ZIP ZName %event_renamenewlocalpath /create

 27  DELETE %event_renamenewlocalpath
 28  :done
 29  DISPLAY

Browse complete list of scripts

Home | Solutions | Professional Services | Technical Support | Download | Company | Contact Us | Partners | Site Map | Terms of Service | Privacy Policy |