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
- Anonymous
September 17, 2014
您好！sqlserver2014可以将前版本的备份文件还原到数据库么？ - Anonymous
September 21, 2014
可以的，SQL Server預設向下相容，所以舊版的backup file，可以還原到新版的SQL Server - Anonymous
February 22, 2015
The comment has been removed- Anonymous
September 19, 2016
fixed it. thank you Thomas
- Anonymous