I have log shipping set up from server1 to server2.

on server1 transaction logs backups are taken every 15 min to a network share folder.

these transaction log backups are then copied (afte 1 hour) to server2 where they are restored.

question: in the event that I need to bring any of these databases from standby to online, how do I find out what transaction logs need to be restored for each database?

I found these 2 good links:

Your poor man’s SQL Server log shipping-PowerShell version

Automate Restoration of Log Shipping Databases for Failover in SQL Server

this second one did not work for me:

on msdb:

 SELECT destination_dir, destination_database, last_file_loaded 
 FROM log_shipping_plans a INNER JOIN log_shipping_plan_databases b 
 ON a.plan_id=b.plan_id 

enter image description here

  • I'm not sure I follow. Are you saying if you need to bring Server1 online and up to date with Server2? If then wouldn't you just restore all of the logs you have?
    – Zane
    Jan 9 '19 at 20:09
  • what was the last log restored? Jan 9 '19 at 20:23
  • As above said, I'd restore ALL log files in your backup path (including the ones yet to be copied over)
    – Jerry Hung
    Jan 10 '19 at 7:40

this script is to be run on server1 and gives me the latest log backup that is available

--the latest backup for each database
--filtered by backup type
declare @backup_type CHAR(1) = 'L' --'D' full, 'L' log

                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name, 
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    FROM sys.databases B 

                    LEFT OUTER JOIN msdb.dbo.backupset A 
                                 ON A.database_name = B.name 
                                AND A.is_copy_only = 0
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type


                 SELECT r.[Server_Name]
                       ,[Backup Type] = r.type 
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name

and this script is to run on server2 and gives me the latest transaction log backup that has been restored:

-- get the latest transaction log backup restored
-- modified from
FROM        msdb.dbo.restorehistory rh
INNER JOIN  msdb.dbo.backupset          as bs   ON bs.backup_set_id = rh.backup_set_id
INNER JOIN  msdb.dbo.backupmediafamily  as bmf  ON bs.media_set_id  = bmf.media_set_id 
WHERE restore_history_id IN 
    SELECT MAX(restore_history_id)
    FROM msdb.dbo.restorehistory
    WHERE restore_type = 'L' 
    AND destination_database_name IN 
        SELECT DISTINCT destination_database_name 
        FROM msdb.dbo.restorehistory
    GROUP BY destination_database_name 
ORDER BY rh.restore_date DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.