asked on

Best practices for .bak and .trn file retention

I am using the database maintenance schedule in SQL 2000 and backing up the database and transaction files.

I have the schedule to retain the .bak and .trn files for two weeks but they are using up a lot of space. I have about 10-15 databases in use so you can see that the amount of space eaten up is quite substantial.

What is the best practice as to the length of time to keep these files?

ASKER CERTIFIED SOLUTION

Atlanta_Mike

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

On tape, I suggest keeping a week's worth at least. On disk keep only as much as you have space for.

To provide data further back, but without keeping many additional copies, you could consider keeping *one* copy of a bi-weekly or even monthly backup. That is, after two weeks or a month when the next copy was made, the original would be scratched. That allows you to go further back but only requires one addtional backup.

Atlanta_Mike

Of course this all depends on the IT infrastructure, staff and budget. You have to do the best with what you have available.

mcnuttlaw

ASKER

I can go back as far as needed.

I was concerned with how much to keep on hand for emergency recovery situations.

So are we in agreement that the database be backed up daily/purged weekly and the transaction logs backed up every 10-15 mins/purged every couple days?

Atlanta_Mike

If you have space for a weeks worth then you are in great shape. I usually keep on hand a copy of the previous months backup just as a precautionary (need to look at old data) measure. It's saved me a couple of time from having to go to the of site storage of tapes.

mcnuttlaw

ASKER

Okay Atlanta Mike,

Thanks for steering me in the right "thinking" direction.

Atlanta_Mike

One other thing... if you want to conserve additional space, ZIP the backup files. I've created a SQL job that replaces the backup files in a zip file nightly and moves them to another server for backup.

I use WinZip and the WinZip command line utility (scheduled in job as last step after backups are completed) and a vbscript file (scheduled in Windows Scheduler)

mcnuttlaw

ASKER

How much space (% wise) does zipping save you?

Atlanta_Mike

You'd be surprised. I get way over 60% - 70% in most instances...

mcnuttlaw

ASKER

Very intriguing. I'll give it a shot. I've never used the WinZip clu so this will be something new to try.