SQL Server log shipping using SQL Backup Pro

SQL Backup Pro makes log shipping much simpler than native SQL Server log shipping and can be used for this purpose in three easy steps:

1. Set up a shared folder on the secondary server, accessible from the primary server.
2. On the primary server, schedule SQL Backup Pro to perform a transaction log backup periodically. Use the COPYTO option to copy the resulting backup files to the secondary server.
3. On the secondary server, schedule SQL Backup Pro to perform a transaction log restore using wildcards for file names and the MOVETO option to move the files to another folder after the restore process has completed.

Too easy? Believe it. There is no need to set up a linked server, use SQL Server 2000 Enterprise Edition, nor write a single line of T-SQL script. Using SQL Backup Pro, not only do you have smaller backup files that are created faster, you now have an easier way to perform log shipping.

Prerequisites

• The database must be using the FULL RECOVERY or BULK LOGGED recovery models.
• On the secondary server, you must have already restored a full database backup with the NORECOVERY or STANDBY options, so that transaction logs may be applied.

Creating the shared folder

You need to create a shared folder on the secondary server, so that the primary server can place the transaction log backups there. Note that the SQL Server service startup account needs to be a domain user account, and not the local system account, in order for you to access network shares.

Backing up the transaction logs on the primary server

Set up a SQL Server Agent job to periodically make a backup of the transaction logs on the primary server using SQL Backup Pro. To give a unique name to the backup file, use the <AUTO> keyword in the value for the DISK = [filename] parameter. To copy the files to the secondary server's shared drive, use the COPYTO option of SQL Backup Pro.
Example:

SQLBackupC -sql "BACKUP LOG pubs TO DISK = 'F:\Backups\<AUTO>'
WITH COPYTO = '\\server2\pubs\logs\'"

SQL Backup Pro will perform a transaction log backup on the pubs database. The backup file name will be named using the naming convention you set up in the SQL Backup Pro GUI. After the backup process has completed, the backup file will be copied to the \\server2\pubs\logs\ network share.

Restoring the transaction logs on the secondary server

Set up a SQL Server Agent job to periodically restore the transaction logs. Use wildcard file names for the target files. Use the MOVETO option to move the backup file to another folder once the log(s) has been restored, otherwise SQL Backup Pro will pick up those files again to be restored (which will fail). Example:

SQLBackupC -sql "RESTORE LOG pubs FROM DISK = 'F:\Backups\pubs\logs\*.sqb' WITH MOVETO = 'F:\Backups\pubs\logs\processed\'"

SQL Backup Pro will pick up all files matching the *.sqb pattern in the F:\Backups\pubs\logs\ folder. It will discard locked files (in the process of being copied from the primary server), identify backup groups (or split backups), ensure all members of a backup group are present, arrange the files in sequence and finally restore each backup group sequentially. After each backup group has been restored, the backup file(s) for that group will be moved to the F:\Backups\pubs\logs\processed\ folder.

Other considerations

• You can place log files for different databases in the same folder, but the naming convention must be such that you can identify each database's log files uniquely, using a search pattern.
• If you are using encryption, all the files must be encrypted using the same password. You can mix encrypted files with non-encrypted files.
• The one drawback, compared to standard SQL Server log shipping, is that the logs are not restored immediately after the backup file has been copied to the secondary server. There will be a delay, depending on the interval between runs of the restore process.
• If you need to restore a whole bunch of transaction logs that are stored in different folders, simply use multiple DISK = [filename] parameters. You can mix wildcards with explicit names. For example, let SQL Backup Pro handle the validation, sorting and restoring process.

SQLBackupC -sql "RESTORE LOG pubs FROM DISK =
'F:\Backups\pubs\logs\*.sqb', DISK =
'G:\Backups\pubs\logs\*.sqb', DISK =
'H:\Backups\pubs\logs\pubs_log_001.sqb' WITH MOVETO = 'F:\Backups\pubs\logs\processed\'"