1

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

3
  • 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
    Commented Jan 9, 2019 at 20:09
  • what was the last log restored? Commented Jan 9, 2019 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
    Commented Jan 10, 2019 at 7:40

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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