Sql-server – how to find out the log backups that I need to restore in order to bring log shipping from standby to online

high-availabilitylog-shippingrestoresql serversql-server-2016

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

Best Answer

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))
                    ,A.type
                    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]
                       ,r.Database_Name
                       ,[Backup Type] = r.type 
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,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
--http://www.sqlservercentral.com/scripts/Backup/127480/
-----------------------------------------------------------------
SET NOCOUNT ON
SELECT
     rh.*,
    destination_database_name
    ,bmf.physical_device_name
    ,restore_date   
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
Related Question