查看mysql备份记录吗_查看备份记录

--回档策略

--一、源数据库改名

use master

go

EXEC sp_renamedb 'db_tank', 'db_tank_backup'

--二、查看数据备份记录，并备份

SELECT

CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,

bs.database_name,

bs.backup_start_date,

bs.backup_finish_date,

bs.expiration_date,

CASE bs.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

END AS backup_type,

bs.backup_size,

bmf.logical_device_name,

bmf.physical_device_name,

bs.name AS backupset_name,

bs.description,

'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''

+bmf.physical_device_name+ '''WITH NORECOVERY;' as 'restore_sql',

row_number() over()

FROM msdb.dbo.backupmediafamily bmf

INNER JOIN msdb.dbo.backupset bs

ON bmf.media_set_id=bs.media_set_id

WHERE bs.backup_start_date>DATEADD(DAY,-3,GETDATE())

AND bs.backup_start_date >='20181106 2:05:00'

AND bs.backup_start_date <= '20181106 10:05:00'

order by bs.backup_start_date

备份还原的记录都在msdb里。

1.备份记录

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是本机，那么备份是在本机生成的；

如果server_name是别的主机名，那么备份是被拿到本机做过数据库还原；

2.还原纪录

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 'Verifyonly'

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.media_set_id

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

还原数据库的时候是会写backupset和backupmediafamily系统表的，用来记录还原所用到的备份文件信息。