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:
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:
- sp_delete_backuphistory – To remove backup history for all databases within a SQL Server instance
- sp_delete_database_backuphistory – To remove backup history for a specific database
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.