Figuring out how log shipping is doing

Published On: 2012-05-28By:

A lot of the time when doing data center migrations or system migrations I end up using Transaction Log Shipping to get the data from one SQL Server to another.  I do this for a few reasons.

  1. It’s reliable.
  2. It’s easy to do (I’ve got plenty of scripts sitting around to use)
  3. I can easily see just how far out of sync the databases are

When I’m using log shipping you’ll some times be asked how far behind the new system is from the production system.  There are all sorts of complex ways of doing this like bringing the database into standby mode and looking at some table to see the last value in the table.  However the method that I really like is to use the msdb database and simply query for the information using the dbo.backupset and the dbo.restorehistory system tables.

USE msdb
GO
SELECT database_name, max(backup_finish_date) DatabaseState
FROM dbo.backupset
WHERE EXISTS (SELECT * FROM dbo.restorehistory WHERE backupset.backup_set_id = restorehistory.backup_set_id)
GROUP BY database_name
GO

I then can compare the output from this query to the output from a similar query that you can run on the production server.

USE msdb
GO
SELECT database_name, max(backup_finish_date) DatabaseState
FROM dbo.backupset
WHERE database_name NOT IN ('master', 'model', 'msdb', 'distribution')
GROUP BY database_name
GO

If the output from these match then the destination server has all the log records that have been backed up.  If not then it doesn’t.  What ever the difference between those values tells you how much data is missing.

Denny


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via