As mentioned in this article, using the integrated copying function (the COPYTO function) in SQL Backup may not always be the best option if the copying process blocks other scheduled backups.  This article will discuss how you can use the CopyTool utility to perform log shipping using SQL Backup.

CopyTool is a free utility offered by Red Gate Software to copy files from one folder to another.  You can download CopyTool from here.

This is an overview of how log shipping will be set up in this example:

In this walkthough, the transaction log backup files are created in the G:\Backups\LogShip\AdventureWorks folder on the primary server, YOHZ_PROD01.  They will then be copied over to a remote share located on the secondary server, YOHZ_STANDBY01.  The remote share is named \\yohz_standby01\LogShip\AdventureWorks, and maps to H:\Backups\LogShip\AdventureWorks on the secondary server.  Transaction log backups which have been restored are moved to the H:\Backups\LogShip\AdventureWorks\Restored folder.

Initialization

I need to first initialize my secondary database so that it is in a recovery state, and is able to restore transaction logs made from the primary database.  I will make a full database backup on the primary database, and restore it on the secondary database using the NORECOVERY option e.g.

EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [H:\Backups\LogShip\AdventureWorks\AdventureWorks_FULL_20080801 0900.sqb] WITH NORECOVERY

If the need arises, you can also use the STANDBY option to make your secondary database read-only.  However, note that SQL Backup does not automatically disconnect active connections to the secondary database when it needs to restore the transaction logs.  You'll need to disconnect the active connections using your own script.

The backup job

Using the SQL Backup GUI, I will set up a scheduled job on the primary server to back up the transaction log every 10 minutes.

Starting the schedule job wizard

The backup job step created by SQL Backup

The copy task

I will schedule a task to run every 10 minutes on the primary server, to copy the backup files from the G:\Backups\LogShip\AdventureWorks folder to the \\yohz_standby01\LogShip\AdventureWorks remote share.  I can use either SQL Server Agent to run the CopyTool utility, or I could use Windows Scheduler.  The main consideration is whether the user running the task has rights to both those folders.

If I schedule the task using a SQL Server Agent job, I need to ensure that my SQL Server Agent service startup user has rights to both the source and destination folders.  If I schedule the task using Windows Scheduler, I need to ensure that the user set up to run the task has rights to both the folders.  In this example, I will set up a SQL Server Agent job to run CopyTool.

The parameters that CopyTool accepts are listed in the accompanying readme.txt file included in the download.  CopyTool can be launched using parameters explicitly e.g.

CopyTool /source "G:\Backups\LogShip\AdventureWorks\*.*" /target "\\yohz_standby01\LogShip\AdventureWorks" /filelist "G:\Backups\LogShip\AdventureWorks_copied.txt"

or you can set up a simple text file to list down the parameters to use e.g.

source=G:\Backups\LogShip\AdventureWorks\*.* target=\\yohz_standby01\LogShip\AdventureWorks filelist=G:\Backups\LogShip\AdventureWorks_copied.txt smtphost=mail.myco.com:25 smtpuser=dba@myco.com smtppassword=dbapassword smtpmailfrom=admin@myco.com smtpmailto=dba@myco.com

and launch CopyTool using the /use parameter to use the settings from that file e.g.

CopyTool /use "G:\Backups\LogShip\AdventureWorks_copysettings.txt"

In this example, CopyTool will copy new backup files from the folder specified in the 'source' parameter (G:\Backups\LogShip\AdventureWorks\*.*).  The files are copied to the folder specified in the 'target' parameter (\\yohz_standby01\LogShip\AdventureWorks).  A list of  files that have been copied successfully are stored in the file named in the 'filelist' parameter (G:\Backups\LogShip\AdventureWorks_copied.txt).  Since I want to be notified when errors occur, I set my the various SMTP parameters with the relevant values.

Setting up the copying job

The job step that runs CopyTool

The restore job

I will set up a SQL Server Agent job on the secondary server to restore the transaction logs every 10 minutes.  Since SQL Backup does not have a wizard to do this, I will need to set this up manually.

Setting up the restore job

The job step that restores the transaction logs

The T-SQL command used is as follows:

EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks FROM DISK = [H:\Backups\LogShip\AdventureWorks\AdventureWorks_LOG*] WITH NORECOVERY, MOVETO = [H:\Backups\LogShip\AdventureWorks\Restored]"'

As the naming convention for my backup files is <database>_<type>_<datetime yyyymmdd hhnn>.sqb, I can use the search pattern AdventureWorks_LOG* to pick up all transaction log backup files in the H:\Backups\LogShip\AdventureWorks folder, and let SQL Backup figure out the order in which the files should be restored in.  In addition, SQL Backup will not pick up files which are in the process of being copied.  Finally, all log backups that have been restored will be moved to the H:\Backups\LogShip\AdventureWorks\Restored folder, so that SQL Backup does not attempt to restore them a second time.

Summary

And that's basically all there is to it.  SQL Backup will make backups of the primary database's transaction log every 10 minutes.  The CopyTool utility will copy new backup files to the secondary server every 10 minutes.  Finally, SQL Backup will restore new transaction log backups every 10 minutes to the secondary database.

In addition to CopyTool, Red Gate Software also offers FTPTool.  As its name implies, it can be used to transfer files using the ftp protocol.  Aside from this, its operations are very similar to CopyTool.  You can download FTPTool from here.

You cannot use CopyTool nor FTPTool to set up log shipping using native SQL Server backups for 2 reasons: ·SQL Server cannot tell when a file is in the process of being copied, and will attempt to restore from any file you provide it. ·SQL Server restores need actual file names, unlike SQL Backup which can use search patterns.

Document history

8/1/2008

Initial release.