Figuring out how log shipping is doing

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.

[sql]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[/sql]

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

[sql]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[/sql]

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

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?