SQL Log Shipping Question

Hi Experts,
I have a question about SQL Server Log Shipping:
I have set up a log shipping between two databases on two SQL Server instances (SQL 2008 R2).
It has been working with no issues  - every transaction is being replicated from the primary to the secondary instance in terms of minutes - all log shipping jobs are running successfully and on time.

Then I decided to test this with DDL, namely creating a new table in the primary instance.

I was surprised to see that 3 days later the table is still not showing up in the secondary database!
Log shipping is running and jobs are running as scheduled with no issues.

I was under the impression that log shipping creates an exact copy of the primary on the secondary and every transaction, DML OR DDL (created objects) will also be replicated.

Why then I do not see the newly created table from the primary to be replicated to the secondary?

What am I missing? Is there an option in LS to tell the process to also replicate objects, schema changes etc?

Please help with information or specific sources of information on this mystery :-)


P.S. Please refrain from questions like "Why are you using Log Shipping when...." and "Why don't you use this and that instead...". I am aware of the alternatives of database replication, in fact I have tested at least 9 other alternatives (SQL Server Replication, Backup/ Restore, CDC, Database Mirroring, Custom ETL etc). I am using Log Shipping because I have a reason to use it and cannot use any other method in this specific case>
Who is Participating?
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
OK, it looks like you either ran the restore on the secondary with a different name or there have been no restores on the other side.

Try this query on the secondary:
select top 100 *
from msdb.dbo.restorehistory
where restore_type = 'L' and destination_database_name = 'YOUR DATABASE NAME HERE'
order by restore_date desc

Open in new window

See if there are any results.  Also you should not have version mismatches especially on the secondary side.  You have a lower version of SQL (service pack RTM) than the primary (SP1) and that is not a favorable situation in the restore process.

Let me know what you find.  It appears to me from what you have posted that there have been no log restores.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was surprised to see that 3 days later the table is still not showing up in the secondary database!
That's weird. The table still exists in the primary database?
Also, others data modifications has been applied to the secondary database?
DBAduck - Ben MillerPrincipal ConsultantCommented:
I am guessing that you are using ReadOnly/Standby mode if you are looking at the log shipped data.

There is really no magic in Log Shipping because it is the same as backup / restore so there is no possibility that there are transactions that did not make it unless the transaction was rolled back.  If it is in the primary, then it was committed and should show up in the secondary.

If you can confirm that you are using Standby mode then you could right click on the database in Management Studio and choose Properties and look at the last Log Backup date.  You should see a recent date or in this case, a 3 day ago date if the data is not there.

Let me know, I am veteran with Log Shipping and I am happy to take you through all this to see what could be wrong.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

RICuserAuthor Commented:
Thank you Ben, yes the secondary is in stand by\read only mode.
I have attached some screenshots of the properties you've asked for.
Also, here are the versions of the two instances (not sure whether this makes any difference):

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1734.0 (X64)   Aug 11 2010 12:51:02

Thank you!
RIC User
RICuserAuthor Commented:
Yes, Vitor The table is still in the primary but not in the secondary database.
I wonder what will happen if I run some UPDATEs on that table in teh primary (since all other transactions are being replicated...)
I am going to try this now and will post  results.

RICuserAuthor Commented:
Ben, the query did not return any results. That tells me that there were no successful restores for some time.
I checked the LS Monitor job and the error confirmed that - see attached.
Where can I find more information as to why the restores are failing and are there any tools (like say, Replication Monitor for SQL replication) that would help monitor the Log Shipping process?

DBAduck - Ben MillerPrincipal ConsultantCommented:
The restore information will be in the Event Viewer on the server and should be in the ERROR log for SQL Server.

Let me know.
RICuserAuthor Commented:
...cont from previous comment...
And still the restore job on the secondary  shows a successful restore.
Isn't that misleading? (see attached)
RICuserAuthor Commented:
Tge SQL Server log is flooded with these messages starting from th 17th and still going on:

Date            2/17/2015 1:34:00 PM
Log            SQL Server (Current - 2/22/2015 3:14:00 AM)

Source            spid295

The log shipping secondary database <database_name> has restore threshold of 45 minutes and is out of sync. No restore was performed for 47 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
DBAduck - Ben MillerPrincipal ConsultantCommented:
Yes, it is misleading as it says that 0 files were restored.

This appears that Log Shipping is not set up correct. What does the dialog look like in the primary database Transaction Log Shipping tab in the Properties?
RICuserAuthor Commented:
The query provided by Ben Miller pointed out an issue with the restores - I found no successful restores to the secondary database.
I resolved this by reconfiguring Log Shipping from scratch and now all works as intnded.
The only difference from my first time setup was that then I used an already generated backup for the secondary initialization.
This time around I let SQL Server to create its first backup  - an option as part of the Log Shipping setup.
With its own initial backup\restore now the secondary is being updated with new transactions and also with DDL as well.
Thanks Ben!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.