Query and clear SQL Server database backup and restoration history

I once met a user who has a very large MSDB database and asked me to help find out why. Use sp_spaceused to find the data size of all tables. The problem is that SQL Server backs up and restores the historical table to a large amount of data. You often back up logs, but never clear historical records. Later, when the maintenance plan was set up to regularly clear the backup records, the problem would not occur.

The statements for querying SQL Server backup and restoration records and scripts for clearing database backup and restoration are listed here:

 

-- Query SQL Server backup history

 

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

MSDB. DBO. backupset. database_name,

MSDB. DBO. backupset. backup_finish_date

 

 

----- Query sqlserver Restore History

 

Select bus. server_nameas 'server', Rh. restore_date, bus. database_nameas 'databas ',

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

 

--- Clear all backup and restoration records before 20120101 (No parameter is specified to delete only the backup or welcome records)

 

Use MSDB

Go

Exec sp_delete_backuphistory @ oldest_date = '2013'

 

-- Delete the backup and restoration records of the 'adventureworks2012 'database (No parameter specifies the retention date, all or none)

 

Use MSDB;

Go

Exec sp_delete_database_backuphistory @ database_name = 'adventureworks2012 ';