如何找出我需要恢復的日誌備份，以便將日誌傳送從備用狀態變為線上狀態？

我已經設置了從 server1 到 server2 的日誌傳送。

在 server1 上，事務日誌每 15 分鐘備份一次到網路共享文件夾。

然後將這些事務日誌備份（1 小時後）複製到恢復它們的 server2。

問題： 如果我需要將這些數據庫中的任何一個從備用變為聯機，我如何找出每個數據庫需要恢復哪些事務日誌？

我找到了這兩個很好的連結：

你窮人的 SQL Server 日誌傳送-PowerShell 版本

為 SQL Server 中的故障轉移自動恢復日誌傳送數據庫

第二個對我不起作用：

在 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 

此腳本將在其上執行server1並為我提供可用的最新日誌備份

--------------------------------------------------------
--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

並且此腳本將執行server2並為我提供已恢復的最新事務日誌備份：

-----------------------------------------------------------------
-- 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

引用自：https://dba.stackexchange.com/questions/226717