October 18, 2010 at 1:32 pm
Back in April 2009, our system administrator left. He took care of our network, SQL Servers, etc. My job is primarily a developer, but because I was, "the closest one of the server when the system administrator left", handling SQL Server has fallen onto me. (I've been doing these tasks for a long time, but not as intensely until then.) ANyway, last week when I was converting a column in a table, I happened to notice that we have .bak and .trn files for all of our user databases going back to April 2009. Our tape backup system is beginning to get tight on space, so I'm thinking of deleting these files. And we maintain a tape backup going back 3 months.
Anyway, is it OK if I delete these files?
Kindest Regards, Rod Connect with me on LinkedIn.
October 18, 2010 at 1:37 pm
Yes, you can safely delete these files. I would setup, if you do not have one already, a Job that will run periodically and auto-remove the old backups you do not need.
Or you can go the Maintenance Plan route and use the pre-built Cleanup plan.
October 18, 2010 at 1:42 pm
I... hesitate to tell YOU that it's okay to destroy YOUR backup files. However, I can help you figure out if it's right for you.
First, keep any trn files back to the oldest backup you intend to keep. Sometimes backups go screwy and you have to go further back for a clean backup. All the T-logs from then to now can save you if something corrupts.
First, you have to look at the Service Level agreements (and possible audit laws) of your industry. Assume your marketing and/or bosses have that. Get them to sign off on how far back you need to keep historical records.
Once that's done, try to keep a revolving order of four to five backups. Why? Because users, inevitably, say "Oh, hey, a month ago I screwed up one of our clients Hooperscoops. What did it used to be?" Get a good gauge on how far back they might need to do this before you want it un-recoverable... and thus a possible earful.
Then, as mentioned above, local disk backups can be forced to roll off via the maintenance plans/automated jobs, and you'll just need to keep your tapes on a cycle. Speaking of tapes, you DO store all but the currently used one off site for disaster recovery... right?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 1:51 pm
upstart (10/18/2010)
Yes, you can safely delete these files. I would setup, if you do not have one already, a Job that will run periodically and auto-remove the old backups you do not need.Or you can go the Maintenance Plan route and use the pre-built Cleanup plan.
Pre-built Cleanup plan? I didn't know there was such a thing. Thank you, I'll look into that!
Kindest Regards, Rod Connect with me on LinkedIn.
October 18, 2010 at 1:54 pm
Craig Farrell (10/18/2010)
I... hesitate to tell YOU that it's okay to destroy YOUR backup files. However, I can help you figure out if it's right for you.First, keep any trn files back to the oldest backup you intend to keep. Sometimes backups go screwy and you have to go further back for a clean backup. All the T-logs from then to now can save you if something corrupts.
First, you have to look at the Service Level agreements (and possible audit laws) of your industry. Assume your marketing and/or bosses have that. Get them to sign off on how far back you need to keep historical records.
Once that's done, try to keep a revolving order of four to five backups. Why? Because users, inevitably, say "Oh, hey, a month ago I screwed up one of our clients Hooperscoops. What did it used to be?" Get a good gauge on how far back they might need to do this before you want it un-recoverable... and thus a possible earful.
Then, as mentioned above, local disk backups can be forced to roll off via the maintenance plans/automated jobs, and you'll just need to keep your tapes on a cycle. Speaking of tapes, you DO store all but the currently used one off site for disaster recovery... right?
Very good advise, Craig, thank you! As I said, our tape backups go back as far as 3 months, but it sounds as though I should let the .bak and .trn files hang around at least 6 months or so. I'll get a feel for how far back users want something restored, before I set up anything permanent.
Thanks again!
Kindest Regards, Rod Connect with me on LinkedIn.
October 20, 2010 at 9:00 am
but it sounds as though I should let the .bak and .trn files hang around at least 6 months or so.
It might be worth assessing your capacity requirements before making this decision? 180 days worth of backups could potentially take up a lot of space. I'd be looking at at least 5 petabytes! :w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply