Log shipping across domains with FTP

If you have decided to use SQL Server Log Shipping as a Disaster Recovery (DR) solution, but for whatever reason your offsite data centre or external host isn’t on the same domain as you is Log Shipping still viable?

Yes, but you are going to have to go outside of the usual interface and work those cross company / cross domain issues yourself.

Basic Log Shipping Process

  1. Take Log backups on a schedule every N minutes
  2. Transfer Log backups to N* machines you wish to host a warm DR copy of the database
  3. Restore the Log backup (in either “no recover” or “standby”)
  4. Repeat 1-3
  5. If DR event occurs, restore last Log “With Recovery” and inform users of new DB location

Points 2 and 3, the transfer and restore process, is what you are interested in once outside of the standard process.


Use the Transaction Log Shipping setup option for your desired master database, but stop short at configuring the secondary server instances.  This will create the master Log backup jobs and schedule them as required.

FTP Transfer

I’ve used FTP as the method to transfer files offsite from the local domain to a hosted DR server in an external data centre. WinSCP is good because it offers a few transmission choices including file synchronisation, is scriptable and free.

Using a scheduled task run WinSCP and load a script.

“C:\Program Files (x86)\WinSCP\WinSCP.exe” /console /script=C:\<yourFilePath>\<BDScript.txt>

Here is the script to sync the directories. You will need to create a connection profile first in WinSCP using the GUI to reference in the script so you don’t need to worry about username / passwords being available in a text file.

# Abort automatically from script on errors
option batch abort
# Disable overwrite confirmations for previous files
option confirm off
#Setup site connection in WinSCP first, ref here
open vm_ls@
#change local dir
lcd "E:\SQL\LS_Path"
#Change remote path
cd /
#file transfer in binary mode
option transfer binary
#Sync with the remote server, check by size, preserve file time and allow delete
synchronize -delete remote -criteria=size -preservetime -resumesupport=on
#Exit WinSCP

SQL Restore

On the secondary server(s) (yes, you can send files to as many servers as required and have licenses for) you need to create the SQL Agent restore job as outside of the usual process SQL Server won’t create the jobs for you on the secondary’s.  You will also need to perform the first time initialisation of the database and leave it in a restoring state to accept further Log backups.

SQL Agent job script

USE [master]
 @LSCopy_Job uniqueidentifier,
 @LSRestore_Job uniqueidentifier,
 @LSSecondary_Id uniqueidentifier

exec sp_add_log_shipping_secondary_primary
 @primary_server = '<machine_name>',
 @primary_database = '<db_name>',
 @backup_source_directory = 'C:\<path>\<here>',
 @backup_destination_directory = 'C:\<path>\<here>',
 @copy_job_name = 'JobNameCopy',
 @restore_job_name = 'JobNameRestore',
 @file_retention_period = 4320, 
 @overwrite = 1,
 @copy_job_id = @LSCopy_Job OUTPUT ,
 @restore_job_id = @LSRestore_Job OUTPUT ,
 @secondary_id = @LSSecondary_Id OUTPUT

exec sp_add_log_shipping_secondary_database
 @secondary_database = '<db_name>',
 @primary_server = '<machine_name>',
 @primary_database = '<db_name>',
 @restore_delay = 0,
 @restore_all = 1,
 @restore_mode = 1,
 @disconnect_users = 1,
 @restore_threshold = 120,
 @threshold_alert_enabled = 1

This script will create two jobs, ‘JobNameCopy’ and ‘JobNameRestore’.  Thanks to the FTP process we don’t need to pull the log backups from the primary server source so the ‘JobNameCopy’ can be deleted or disabled. More info from MSDN here and here.

Now you can configure the timing of the restore job to run at suitable intervals to tie up with your copy process.  Remember that you need to allow time for the FTP copy process to complete before the restore job runs as SQL requires an exclusive lock on the Log backup and part transferred files cannot be included in any running restore process.

Initialise database

Take a full backup of the source database, ship it over (perhaps using your new FTP link?) and restore it, setting the restore option, recovery state to either “Restore with NoRecovery” or “Restore with Standby”


Hopefully you will have a smooth DR process with Log Shipping, even when implemented across the usual company domain boundaries using FTP.  Log Shipping is a highly robust process and works well even over high latency satellite connections with FTP.

Posted in Backups, Microsoft, Networking, SQL Server | Leave a comment

Remove multiple CR LF characters from the end of text strings in SQL

Data entry, it’s where users love to copy and paste. Especially when their text is nicely arranged with multiple spaces, cartridge returns and line feeds amongst other special character formatting.
In a perfect world this would be captured during input (see end of article), but if some slips through the net you need to be able to clean the text string within the SQL database when faced with a situation like this.

User formatted input with multiple CR LF characters

To clean this up you could blaze in and use the function REPLACE

SELECT REPLACE(column_name, CHAR(13) + CHAR(10), '')
FROM table_name

This will remove ALL entries so the users will be calling up complaining their formatting has gone.

Alternatively you need to find the LAST valid text and remove the remainder.  Finding the end of a string is not straightforward in SQL as most functions only take an expression such as ‘foo’ and not a pattern.  The function PATINDEX does accept a ‘% like’ statement, but finds the first occurrence.  Yes, you can use it along with the function REVERSE if you are using SQL 2008+ but it’s not very nice.

My solution, in this instance, where we have multiple formatting characters is to look for the first instance of two identical characters and then keep all preceding information.

This update statement will clean and update a column storing a string contaminated like the above example.

UPDATE table_name
SET column_name = SUBSTRING(column_name,0,CHARINDEX(CHAR(10)+CHAR(10), column_name))
WHERE column_name LIKE '%'+CHAR(10)+CHAR(10)+'%'


Prevention is better than the cure, so if users are submitting data in Excel you can use this combination of Excel functions to pre-clean the data.

Posted in Excel, Microsoft, SQL Server | Leave a comment