SQL Server with Mr. Denny

May 28 2012   2:00PM GMT

Figuring out how log shipping is doing

Posted by: Denny Cherry
Backup & recovery
Log Shipping
Restore Database
SQL Server

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
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

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
SELECT database_name, max(backup_finish_date) DatabaseState
FROM dbo.backupset
WHERE database_name NOT IN ('master', 'model', 'msdb', 'distribution')
GROUP BY database_name

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.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: