[SQL Server]查询清除SQL Server数据库备份还原历史记录

楼主^#

更多发布于：2012-10-22 14:05

[] 1


	查询清除SQL Server数据库备份还原历史记录曾经遇到过一个用户MSDB数据库非常大，让我帮忙查查是什么原因。使用sp_spaceused找出了所有表的数据大小，发现问题是SQL Server备份和还原历史表数据太大。用户经常会做日志备份，但是从来没有清除过历史记录。后来在设置了维护计划定期清除备份记录，问题就不在发生了。这里列出了查询SQL Server备份还原记录的语句以及如何清理数据库备份还原的脚本： www.atcpu.com --查询SQL Server备份历史 SELECT CONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END ASbackup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.nameASbackupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupsetONmsdb.dbo.backupmediafamily.media_set_id=msdb.dbo.backupset.media_set_id ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date www.atcpu.com -----查询SQLServer还原历史 select bus.server_nameas'server',rh.restore_date,bus.database_nameas'database', CAST(bus.first_lsnASVARCHAR(50))asLSN_First, CAST(bus.last_lsnASVARCHAR(50))asLSN_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 ASrhType FROM msdb.dbo.backupsetbus INNER JOINmsdb.dbo.restorehistoryrhON rh.backup_set_id=bus.backup_set_id ---清除20120101之前所有的备份还原记录（没有参数指定只删除备份或者欢迎记录） www.atcpu.com use msdb go exec sp_delete_backuphistory@oldest_date='20121010' --删除'AdventureWorks2012'数据库的备份还原记录（没有参数指定保留日期，All或者None） USE msdb; GO EXEC sp_delete_database_backuphistory@database_name='AdventureWorks2012'