If the secondary database server is out-of-sync, you need to find out what log backups have been restored. You need to query the msdb database for the list of log backups that have been restored
select a.restore_date, a.destination_database_name, c.backup_size/1048576 as backup_size_MB, c.database_name as source_database, c.backup_start_date
from restorehistory a
inner join backupset c on a.backup_set_id=c.backup_set_id
inner join backupmediafamily b on c.media_set_id=b.media_set_id
where a.destination_database_name='name of database'
and a.restore_date > 'date that you think the database is up-to-date'
order by 1 desc
Once you have a list of log backups that have been restored on the secondary database server, find out what is missing by looking at the backup history on the primary database server.
SELECT database_name,Backup_type=
CASE type
when 'D' then 'FULL'
when 'I' then 'DIFFERENTIAL'
when 'L' then 'LOG'
when 'F' then 'FILE/FILEGROUP'
when 'G' then 'DIFFERENTIAL FILE'
when 'P' then 'PARTIAL'
when 'Q' then 'DIFFERENTIAL PARTIAL'
END
, physical_device_name,backup_start_date
FROM msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b
on a.media_set_id = b.media_set_id
Where database_name='name of database'
order by a.database_name,a.backup_start_date DESC
Obviously, you can only run this query if the primary database server is still online. Otherwise, you're out of luck. That's why you need to constantly monitor the status of your log shipping configuration.
Edwin Sarmiento SQL Server MVP Edwin Sarmiento SQL Server MVP
Blog |
Twitter | LinkedIn
SQL Server High Availability and Disaster Recover Deep Dive Course
