リストア履歴をシステムデータベースから確認するSQL
from SQL Serverのバックアップ・リストア仕様
バックアップ・リストア関係のmsdbデータベーステーブルのテーブルから
対のクエリ: バックアップ履歴をシステムデータベースから確認するSQL
code:sql
select
rh.restore_date
, bs.database_name as from_db_name
, d_from.state_desc as from_state
, rh.destination_database_name as dest_db_name
, d_dest.state_desc as dest_state
, bf.logical_name
,
left (bf.physical_name, 8) + '~' +
right (bf.physical_name, 16) as from_backuped_physical_name
/* , bf.physical_name as physical_name_full */
,
left (bmf.physical_device_name, 8) + '~' +
right (bmf.physical_device_name, 16) as physical_device_name
/* , bmf.physical_device_name physical_device_name_full */
,
left (rf.destination_phys_name, 8) + '~' +
right (rf.destination_phys_name, 16) as to_destination_physical_name
/* , rf.destination_phys_name as destination_phys_name_full */
, rh.user_name
, case rh.restore_type
when 'D' then 'データベース'
when 'L' then 'ログ'
when 'F' then 'ファイル'
when 'G' then 'ファイルグループ'
when 'I' then '差分'
when 'V' then 'Verifyonly'
else convert(varchar (1), rh.restore_type)
end as restore_type
, rfg.filegroup_name
, case rh.replace
when 0 then '指定なし'
when 1 then '指定'
else convert(varchar (1), rh.replace)
end as replace
, case rh.recovery
when 0 then 'norecovery'
when 1 then 'recovery'
else convert(varchar (1), rh.recovery)
end as recovery
, case rh.restart
when 0 then '指定なし'
when 1 then '指定'
else convert(varchar (1), rh.restart)
end as restart
, rh.stop_at
, rh.device_count
, case rh.stop_before
when 0 then 'マークされたトランザクションの前に復旧が停止された'
when 1 then ' マーク付きのトランザクションも復旧された'
else convert(varchar (1), rh.stop_before)
end as stop_before
from
msdb.dbo.restorefile as rf
left join msdb.dbo.restorefilegroup as rfg
on rf.restore_history_id = rfg.restore_history_id
left join msdb.dbo.restorehistory as rh
on rf.restore_history_id = rh.restore_history_id
left join msdb.dbo.backupset as bs
on rh.backup_set_id = bs.backup_set_id
left join msdb.dbo.backupfile as bf
on rh.backup_set_id = bf.backup_set_id
and rf.file_number = bf.file_number
left join msdb.dbo.backupmediafamily as bmf
on bs.media_set_id = bmf.media_set_id
left join sys.databases as d_from
on bs.database_name = d_from.name
left join sys.databases as d_dest
on rh.destination_database_name = d_dest.name
where
1 = 1
/* リストア日時 */
/* and rh.restore_date > '2023-08-24' */
/* リストア元の復旧状況 */
/* and d_from.state_desc = 'ONLINE' */
/* リストア元のデータベース名 */
/* and bs.database_name = 'msdb' */
/* リストア先の復旧状況 */
/* and d_dest.state_desc = 'ONLINE' */
/* リストア先のデータベース名 */
/* and rh.destination_database_name = 'production_test' */
order by
rh.restore_date asc;
全部表示用
code:sql
select
rh.restore_date
, bs.database_name
, d_from.state_desc
, rh.destination_database_name
, d_dest.state_desc
, bf.logical_name
, bf.physical_name
, bmf.physical_device_name physical_device_name_full
, rf.destination_phys_name
, rh.user_name
, case rh.restore_type
when 'D' then 'データベース'
when 'L' then 'ログ'
when 'F' then 'ファイル'
when 'G' then 'ファイルグループ'
when 'I' then '差分'
when 'V' then 'Verifyonly'
else convert(varchar (1), rh.restore_type)
end as restore_type
, rfg.filegroup_name
, case rh.replace
when 0 then '指定なし'
when 1 then '指定'
else convert(varchar (1), rh.replace)
end as replace
, case rh.recovery
when 0 then 'norecovery'
when 1 then 'recovery'
else convert(varchar (1), rh.recovery)
end as recovery
, case rh.restart
when 0 then '指定なし'
when 1 then '指定'
else convert(varchar (1), rh.restart)
end as restart
, rh.stop_at
, rh.device_count
, case rh.stop_before
when 0 then 'マークされたトランザクションの前に復旧が停止された'
when 1 then ' マーク付きのトランザクションも復旧された'
else convert(varchar (1), rh.stop_before)
end as stop_before
, bs.database_backup_lsn
from
msdb.dbo.restorefile as rf
left join msdb.dbo.restorefilegroup as rfg
on rf.restore_history_id = rfg.restore_history_id
left join msdb.dbo.restorehistory as rh
on rf.restore_history_id = rh.restore_history_id
left join msdb.dbo.backupset as bs
on rh.backup_set_id = bs.backup_set_id
left join msdb.dbo.backupfile as bf
on rh.backup_set_id = bf.backup_set_id
and rf.file_number = bf.file_number
left join msdb.dbo.backupmediafamily as bmf
on bs.media_set_id = bmf.media_set_id
left join sys.databases as d_from
on bs.database_name = d_from.name
left join sys.databases as d_dest
on rh.destination_database_name = d_dest.name
where
1 = 1
/* リストア日時 */
/* and rh.restore_date > '2023-08-24' */
/* リストア元の復旧状況 */
/* and d_from.state_desc = 'ONLINE' */
/* リストア元のデータベース名 */
/* and bs.database_name = 'msdb' */
/* リストア先の復旧状況 */
/* and d_dest.state_desc = 'ONLINE' */
/* リストア先のデータベース名 */
/* and rh.destination_database_name = 'msdb_test' */
order by
rh.restore_date asc;
履歴
backupsetとsys.databasesのdatabase_nameでつないでSQL Serverの復旧状態を表示してみるようにしたが、あっているかわからない(2023/08/24)
とりあえず復元元がRESTORINGになっているか確認したい