Red Gate forums :: View topic - One to Many Log shipping -- doable?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

One to Many Log shipping -- doable?

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
samjones11



Joined: 06 Mar 2009
Posts: 10

PostPosted: Tue Dec 25, 2012 8:20 pm    Post subject: One to Many Log shipping -- doable? Reply with quote

Hello!

I use SQL Backup 6.5, and need to set up one to many log shipping (to on site spare SQL server, to off site, to development...)

I have it set up now to the on site warm standby.

Any tips or pointers on doing one to many?

Thanks!
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 890
Location: Red Gate Software

PostPosted: Thu Dec 27, 2012 4:36 pm    Post subject: Reply with quote

Hi samjones11

What you are seeking cannot be achieved using the Log Shipping Wizard. However you can modify your existing setup to perform a one to many. The instructions below if you wish to log ship to two destination servers is as follows:

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.

I hope the above helps.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group