--回档策略
--一、源数据库改名
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系统表的，用来记录还原所用到的备份文件信息。