查询数据库最后一次恢复使用的备份文件

来源：互联网发布：za淘宝旗舰店是真的吗编辑：程序博客网时间：2024/05/17 03:43

在做log shipping的时候经常遇到从数据库不同步的问题。于是我用下面的语句来查询最后一次恢复的log是什么时候的。

SELECT top 1 backup_set_idFROM msdb.dbo.restorehistoryWHERE destination_database_name = 'YourDB'ORDER BY restore_date DESC

然后使用backup_set_id继续查询

select first_lsnfrom msdb..backupsetwhere backup_set_id = @backup_set_id

最后查询到文件名称

USE DBYouWantToCheckGOSELECTs.database_name,m.physical_device_name,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,CASE s.[type]WHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Transaction Log'END AS BackupType,s.server_name,s.recovery_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_idWHERE s.database_name = DB_NAME()AND first_lsn = @first_lsnGO

0 0