Using the CopyTool utility

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

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.

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.


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.


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.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.