Is there anyway to do a copy only log shipping

Censor1983

We currently require a copy of a database that is kept as real time as possible. 
With the database being on the same instance mirroring is out of the question, so we were looking at log shipping but would prefer to use copy only log shipping if anyone knows if this is possible?

Eddie D

Hi, Thank you for your forum post.

What you are looking to achieve is not possible using the Log Shipping wizard on its own, however what you are seeking is achievable by being a little creative outside of the log shipping wizard.

--Create a new destination database, take a copy only Full backup of the source database and restore it to the destination database to initialize the process.

1. First create a new empty database.
2. Perform a COPY_ONLY Full backup of the source database.
3. Restore the Full COPY_ONLY created in Step 2 and restore using either WITH NORECOVERY or WITH STANDBY to initialize the database created in Step 1.

Now you have a choice of methods:
Method 1 is to use the Log Shipping wizard and manually add the COPY_ONLY keyword to the log backup job . 

• On Step 1 of 6, for the destination database, select Use an existing database to restore to option and in the drop down select the database created in Step 1 above.
• Uncheck the Overwrite: initialize the destination with a full backup of the source database.
• Complete the remaining steps of the wizard.
• On completion, you will have a scheduled Log backup job and a Log restore job.
• Via SSMS ->SQL Agent ->Jobs ->Locate the scheduled Log backup job -> Right click and select properties ->Job Steps and Edit the Job Step -> Add the COPY_ONLY keyword to the job syntax after the WITH keyword.

Method 2 is to create a scheduled log backup job for the source database which includes the COPY_ONLY keyword in the syntax.  Followed by a scheduled log restore job for the destination database. 

Many Thanks
Eddie

montana

In order to set up copy-only log shipping, you can follow these high-level steps:

Configure your primary server to create transaction log backups with the COPY_ONLY option using a scheduled SQL Server Agent job.
Configure your secondary server(s) to restore the transaction log backups using the NORECOVERY option.  
Manually copy the transaction log backups from the primary server to the secondary server(s) using a shared network location or other suitable method.
On the secondary server(s), use the RESTORE LOG statement with the STANDBY option to apply the copied transaction log backups in read-only mode.
 but it has since grown to be capable of converting virtually any operating system into a bootable USB or SD card.