Have you ever inherited a server and find out that your msdb database is rather large in nature? Or maybe you have never set up any maintenance on your msdb with regards to retaining backup history? Okay, okay perhaps you don’t fall into those categories but maybe you have policies in place and you offline a database for whatever reason and your policies start to fail for backups not taken in the last 24 hours. Maybe you fall into one of these categories or maybe you fall into a different one, whatever the case may be there are options for removing the backup history cleanly and methodically without going all rogue style in SQL.
Microsoft has provided a couple system stored procedures within the msdb database for removing backup history, but I will only focus on one, sp_delete_database_backuphistory.
This little gem, if used properly, will remove the backup history for a particular database. This comes in very useful as recently taking a database offline to be decommissioned was messing with policy checks in place, particularly the one I mentioned above where a the policy checks to see if a database had been backed up within the last 24 hours.
|/*This code deletes backup history for a specific database*/
exec sp_delete_database_backuphistory ‘yourdatabasename’
The Tables Affected
There are tools available at ones disposal to help in situations with SQL. Microsoft has provided a number of system stored procedures but it would not be prudent to just go start running scripts or executing procedures without first knowing what they are going to do to your systems. Never run anything in production or any environment for that matter without first testing it on your local sandbox.
Add this script to your repository, you never know when you will need it.
I recently responded to a post on SQL Server Central regarding upgrading SQL Server 2005 to SQL Server 2008 R2. The suggestions laid out before my post were spot on; it was the one that came after my response of “agreeing” that backups were important……their is an age old saying I’ve always heard of “You’re only good as your last backup”. The response I pondered on this weekend was from a user who said, “Backups are worthless; restores are priceless“. Being the analytical person that I am I decided to dive into this statement for myself. I can only conclude that in order to do the priceless restore you first must have the worthless backup to restore. So, for me personally I cannot justify backups as being worthless.
I hope DR plans never have to be used but we live in a world that has error so it could be inevitable. No one knows when a disaster will strike, heck we just experienced one of the worst tornado’s around my area since 1974. Stuff happens, the world doesn’t stop turning so as a DBA I better be making sure that our systems have solid backups.
Backups should be pretty obvious, to have a back up is essential in order to recover ( to even be able to do the restore ) from most disasters. Backups aren’t just needed for disasters; think about upgrades gone bad, system issues, or even table corruption.
I know I probably shouldn’t have read to much into the statement, but to me that is a pretty big ordeal when hearing backups are worthless. I’d be interested to here from others what their thoughts are on backups and strategies, do you think they are worth it?
I won’t dive into the different methodologies of backing up databases; that would need to be an entirely different post, I personally like a good backup strategy and for any DR plan in place you need to have a good one. I also, like the idea of routinely verifying backups by restoring them to a DBA sandbox so to speak.
In any sense have a good one guys, thanks for listening to my ramblings on the matter!