Configuring Log Shipping to two Target Databases

Category: How do I?
Date: 29 Sep 2009
Product: SQL Backup
Affected Versions:
SQL Backup 4.*
SQL Backup 5.*
SQL Backup 6.*
PROBLEM DETAILS
In some situations it may be a requirement to be able to Log Ship from the primary or source database to two or more secondary or target servers.
The SQL Backup Log Shipping wizard does not allow the user to specify multiple target databases, or if Log Shipping is already configured, how does the user configure SQL backup to start Log Shipping to a second target database.

SOLUTION 1 - HOW TO CONFIGURE SQL BACKUP to LOG SHIP to TWO TARGET DATABASES WHERE LOG SHIPPING WAS NOT PREVIOUSLY CONFIGURED
1. Run the SQL Backup Log Shipping Wizard. Follow the on-screen instructions and configure Log Shipping to Log Ship to the first target location.
2. Configure a network share for the second target server to use.
3. Restore the Full Seed Backup, created is step 1 to the second target server using the WITH NORECOVERY argument.
4. On the source server, Open SSMS or Enterprise Manager ->SQL Server Agent ->Jobs ->Locate the Transaction Log Backup job created by running the Log Shipping Wizard in Step 1.
5. Right Click the job and select Properties.
6. Select Steps and click on the Edit button. The syntax of the Transaction Log Backup job may look similar to the following:
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [<database_name>] TO DISK=''D:\Backup\<database_name>\LogShipping\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\server1\Share1\<database_name>''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END

7. Modify the job and add a second COPYTO parameter whose path is to the network share configured in Step 2, so the job becomes:

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [<database_name>] TO DISK=''D:\Backup\<database_name>\LogShipping\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\server1\Share1\<database_name>'', COPYTO = ''\\server2\share2\<database_name>''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END

8. On the second target server, create a new job to restore the Transaction Log Backup file to second database. Open SSMS ->SQL Server Agent ->Jobs ->Right Click Jobs and select new job. On the General Tab give the job a name and if required enter information into the Description field. On the schedules tab, set a new schedule on how often the restore job needs to run. On the Steps Tab modify the restore script below as required:
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [<database_name>] FROM DISK = ''<path to network share>_*.sqb'' WITH NORECOVERY, MOVETO = ''<path to completed log shipping folder>''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END
9. Check that everything is configured correctly; ensure that source server copies backup files to the second network share location and that the second target server restores the backup files from the network share.
SOLUTION 2 - LOG SHIPPING WAS PREVIOUSLY CONFIGURED AND THE REQUIREMENT HAS CHANGED TO HAVE A SECOND TARGET DATABASE.

1. Configure a network share for the second target server to use.
2. Take a full backup of the source database.
3. Restore the full backup created in step 2, to the second target database using the WITH NORECOVERY argument.
4. On the source server, Open SSMS or Enterprise Manager ->SQL Server Agent ->Jobs ->Locate the existing Transaction Log Backup job.
5. Right Click the job and select Properties.
6. Select Steps and click on the Edit button. The syntax of the Transaction Log Backup job may look similar to the following:
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [<database_name>] TO DISK=''D:\Backup\<database_name>\LogShipping\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\server1\Share1\<database_name>''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END
7. Modify the job and add a second COPYTO parameter whose path is to the network share configured in Step 1, so the job becomes:

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [<database_name>] TO DISK=''D:\Backup\<database_name>\LogShipping\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\server1\Share1\<database_name>'', COPYTO = ''\\server2\share2\<database_name>''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END
8. On the second target server, create a new job to restore the Transaction Log Backup file to second database. Open SSMS ->SQL Server Agent ->Jobs ->Right Click Jobs and select new job. On the General Tab give the job a name and if required enter information into the Description field. On the schedules tab, set a new schedule on how often the restore job needs to run. On the Steps Tab modify the restore script below as required:
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [<database_name>] FROM DISK = ''<path to network share>_*.sqb'' WITH NORECOVERY, MOVETO = ''<path to completed log shipping folder>''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END
9. Check that everything is configured correctly; ensure that source server copies backup files to the second network share location and that the second target server restores the backup files from the network share.