Query SQL Server backup history and restore history records


SQL Server備份還原紀錄

1.使用以下TSQL語法查詢備份檔紀錄

SELECT 
 bs.backup_set_id,
 bs.database_name,
 bs.backup_start_date,
 bs.backup_finish_date,
 CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
 CAST(DATEDIFF(second, bs.backup_start_date,
 bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
 CASE bs.[type]
 WHEN 'D' THEN 'Full Backup'
 WHEN 'I' THEN 'Differential Backup'
 WHEN 'L' THEN 'TLog Backup'
 WHEN 'F' THEN 'File or filegroup'
 WHEN 'G' THEN 'Differential file'
 WHEN 'P' THEN 'Partial'
 WHEN 'Q' THEN 'Differential Partial'
 END AS BackupType,
 bmf.physical_device_name,
 CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
 CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
 bs.server_name,
 bs.recovery_model
 From msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
 GO
 

透過SERVER_NAME欄位,可以判斷該備份檔是否是在這台SQL Server上執行的備份

如果SERVER_NAME欄位顯示別台SQL Server主機名稱,表示這個備份檔是從別台SQL Server複製過來並且在這台執行過RESTORE

 

2.使用以下TSQL查詢還原紀錄

SELECT rs.[restore_history_id]
 ,rs.[restore_date]
 ,rs.[destination_database_name]
 ,bmf.physical_device_name
 ,rs.[user_name]
 ,rs.[backup_set_id]
 ,CASE rs.[restore_type]
 WHEN 'D' THEN 'Database'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Log'
 WHEN 'F' THEN 'File'
 WHEN 'G' THEN 'Filegroup'
 WHEN 'V' THEN 'Verifyonlyl'
 END AS RestoreType
 ,rs.[replace]
 ,rs.[recovery]
 ,rs.[restart]
 ,rs.[stop_at]
 ,rs.[device_count]
 ,rs.[stop_at_mark_name]
 ,rs.[stop_before]
 FROM [msdb].[dbo].[restorehistory] rs
 inner join [msdb].[dbo].[backupset] bs
 on rs.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 GO

PS.RESTORE操作會寫入backupset與backupmediafamily資料表,紀錄還原所使用的備份檔資訊

 

 

 

Comments (4)

  1. Dylan says:

    您好!sqlserver2014可以将前版本的备份文件还原到数据库么?

  2. Brad Chen says:

    可以的,SQL Server預設向下相容,所以舊版的backup file,可以還原到新版的SQL Server

  3. Thomas Mucha says:

    Hi,

    You seem to have a bad join in the 2nd query. 'on rs.backup_set_id = bs.media_set_id' should be 'on rs.backup_set_id = bs.backup_set_id'

    Otherwise, great queries, exactly what I was looking for.

    Tom

    1. Brad Chen says:

      fixed it. thank you Thomas

Skip to main content