Using sp_delete_backuphistory to delete backup history

You might be aware that system database msdb in SQL Server contains tables related to backup and restore operations of that Instance. Every time a database backup is performed, details regarding that backup is stored in multiple tables in msdb database. This information is useful when you want to restore a database to a point in time using the SQL Server Management Studio. But after certain period, the old data about the backups is not that useful anymore.  

It is common to have few dozens of databases on a SQL Server instance and when they are regularly backed up as part of being able to recover to a most recent point in time. Assuming the transaction log backups are scheduled at every 10 or 15 mins for most part of every day, the number of rows generated due to those backups would be slowly but surely adding up rows in those backup tables in msdb. In order to let database admins contain the size of msdb database, SQL Server provides a System Stored procedure called sp_delete_backuphistory to purge backup history from the underlying backup and restore tables in msdb.

The stored procedure accepts a date parameter @oldest_date. The stored procedure takes the value passed into that parameter and deletes the data in the backup and restore tables up to that date.  Let us assume you have never purged the backup information and the msdb database contains the backup information from the beginning of the SQL instance which was setup a couple of years back, the chances are the msdb database would be already huge in size (depends on how many db’s reside on the instance and how frequent the databases are backed up). In that situation, in order to delete all the backup history before 1/7/2011, you will have to pass the date value 1/7/2011 to the stored procedure as follows.

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/7/2011'

It would be good to purge the backup history on a regular basis such as weekly or daily using a SQL Agent job.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post

— Bru Medishetty

12 things to know about MSDB

MSDB is one the System Databases in SQL Server. You might be aware that System Databases are important for the normal functioning of SQL Server. Here is a list of 12 areas in which  MSDB is used in a SQL Server deployment. 

  • SQL Server Agent
  • Backup and Restores
  • Maintenance Plans
  • Performance Data Collector
  • Log Shipping
  • Utility Center Point
  • Database Mail 
  • Database Mirroring
  • Replication
  • Policy Based Management
  • SQL Browser
  • DTS and SSIS Package Store

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

SQL Server Backup File info using T-SQL Query

Few days back, I was asked this question. Using T-SQL is there a way to find the backup details about a database? Including physical location of the database where the backup of a particular database was performed. I did recollect that I wrote such a T-SQL query sometime back to find the backup locations of database backup. I came up with the below query that helped finds the backup information of a particular database.

Find the T- SQL Script at the end of the post. Remember to change the value of the local variable @DBNAME when running in your system. The query has been tested in SQL Server 2008.

The output of the query was wide enough not to fit in the image, hence it has been broken into 2 and displayed. The query displays the details in the reverse chronology, i.e the most recent backup first.

T-SQL Query used in the article

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DATABASENAME'

SELECT A.database_name, B.physical_device_name
,A.media_set_id,A.backup_size,
A.backup_start_date,A.backup_finish_date
FROM msdb.dbo.backupset A
INNER JOIN msdb.dbo.backupmediafamily B
ON A.media_set_id = B.media_set_id
WHERE A.Database_Name= @DBNAME
ORDER BY A.backup_finish_date DESC

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty