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
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:
- Download this sample script and save it as
rename_restore_db.s(use link above) - Modify the first two lines of the script to match your database name and the name of your 32-bit DSN.
- Launch Robo-FTP Server Console program
- Change to the Administration menu and uninstall the service
- Change the service Login to your new Windows user and then reinstall the service
- Select Automation in the navigation bar on the left
- Select "Enable for individual client(s)" under Server Event Scripts on the right
- Click the Apply button
- Select Users in the navigation bar on the left
- Create and configure a new Robo-FTP Server user account.
- On the configuration page for the new user, select "RenameComplete" from the Server Event drop-down list
- In the Event Script File Name box enter the full path to the
rename_restore_db.sscript file that you saved earlier
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