SQL Server – Administration – Cleanup Database Backup History


Earlier this week, I had explored why the sequence of index creation is important in a rather long post on performance tuning. You can read the post here: SQL Server – Performance – Best Practice – Create Index Before or After a data insert?

Today’s post, therefore, will be a nice, small one – something that you might want to convert into a scheduled “spring-cleaning” task.

Viewing Backup Information

Microsoft SQL Server uses MSDB to maintain a record for each completed backup. In production environments, it is generally required that all backup history is maintained. However, in Development and Quality Assurance environments, maintaining database backup history is generally not that important – after all, databases change in these environments often.

The backup history is maintained in the following tables within the MSDB:

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory

To learn more about viewing backup history information, refer the Books On Line page at: http://technet.microsoft.com/en-us/library/ms188653.aspx

Cleaning up the backup history

In order to cleanup the backup history, we can use one of the following two system stored procedures:

Some Key points to remember:

  • Running these system stored procedures requires membership to the sysadmin fixed server role
  • Running these procedures may be performance intensive
  • Please do not use these without guidance from your DBA!

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Advertisements

4 thoughts on “SQL Server – Administration – Cleanup Database Backup History

  1. naikram.dba

    How often we need to clear this Backup history ????
    what are the major advantages of clearing the Bacup History performance wise ??

    Like

    Reply
  2. Nakul Vachhrajani

    Generally, when I no longer have a database on a particular instance, I clear out the backup history for that particular database.

    As for the performance issues that may occur if the backup history is retained, I haven’t experienced any. However, personally, I like my servers to be clean and almost as good as new – which is why I typically perform a cleanup exercise once every quarter or so. Again, the key point to remember is that these cleanup routines would probably never be used in production, but in development and QA environments where databases are frequently changed or recycled, performing such cleanups is a good idea.

    Like

    Reply
  3. Karthikeyan Anbarasan

    Nice post, I have a question here. Do this backup history options work for all types of backups accordingly ?

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s