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.
- It’s reliable.
- It’s easy to do (I’ve got plenty of scripts sitting around to use)
- 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
