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.
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.
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.
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.
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.
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.
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.
The T-SQL command used is as follows:
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.
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.
Discuss or comment on this article on our Facebook group.