系统数据库中的表关系如何查看

[Mic_Gary]

在系统数据库msdb中，我需要根据数据库名称获得数据库备份文件路径、备份时间，已知在表【backupmediafamily】存储备份路径，表【backupset】存放着备份时间，可以根据表【bookupfile】里的数据库名称得到backup_set_id和表【backupset】关联起来，但是却找不到和【backupmediafamily】关联起来的字段，有哪位大大知道的，求告知

[Mic_Gary]

我找到了！！！！

[Mic_Gary]

送分啦，看谁爽就给谁

[發糞塗牆]

--还原历史
USE DB_NAME
go
select bus.server_name as 'server',rh.restore_date,bus.database_name as 'database',
  CAST(bus.first_lsn AS VARCHAR(50)) as LSN_First,
CAST(bus.last_lsn AS VARCHAR(50)) as LSN_Last,
CASE rh.[restore_type]
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'V' THEN 'Verifyonly'
END AS rhType
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.restorehistory rh ON rh.backup_set_id = bus.backup_set_id
WHERE bus.database_name = DB_NAME()  


--备份历史
--Display backup history for a single database 
use [myDatabase]
GO
SELECT bus.server_name as 'server',
bus.database_name as 'database',
CAST(bus.backup_size /1024/1024/1024 AS DECIMAL(10,2)) as 'buSize_GB',
CAST(DATEDIFF(ss, bus.backup_start_date,bus.backup_finish_date) AS VARCHAR(4)) as 'buDuration_Sec',
bus.backup_start_date as 'buDateStart',
CAST(bus.first_lsn AS VARCHAR(50)) as LSN_First,
CAST(bus.last_lsn AS VARCHAR(50)) as LSN_Last,
CASE bus.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
WHEN 'F' THEN 'File\FileGroup'
WHEN 'G' THEN 'Differential File'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END AS buType,
bus.begins_log_chain ,
bus.differential_base_lsn,
bus.recovery_model,
bume.physical_device_name,
is_snapshot
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
WHERE bus.database_name = DB_NAME() 
ORDER BY bus.backup_start_date DESC, bus.backup_finish_date DESC
GO

[發糞塗牆]

公开一下你的脚本

[唐诗三百首]

引用 1 楼 Mic_Gary 的回复:

我找到了！！！！

分享一下呗,

[w091b]

楼主大天才，超级棒，给力，smart,superman。看在这么卖力奉承的份上，交上20分吧！

[Mic_Gary]

引用 5 楼 ap0405140 的回复:

[quote=引用 1 楼 Mic_Gary 的回复:] 我找到了！！！！

分享一下呗, [/quote] 真是关公面前耍大刀了，版主大大，我sql语句不太会写，望指点啊

use msdb
select physical_device_name,backup_start_date from backupset A,backupmediafamily B,backupfile C where A.media_set_id=B.media_set_id and C.backup_set_id=A.backup_set_id and C.logical_name='MyTest'

[Mic_Gary]

引用 6 楼 w091b 的回复:

楼主大天才，超级棒，给力，smart,superman。看在这么卖力奉承的份上，交上20分吧！

就你了