• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • Last Modified:

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?  
0
mcnuttlaw
Asked:
mcnuttlaw
  • 5
  • 4
1 Solution
 
Atlanta_MikeCommented:
What many DBAs use as a plan is they have the full backup run daily, transaction log backup every ten minutes. Keep them for several days and have the it staff backup to tape only those two days worth of files.

I personally keep a weeks worth of full backups of the development database for quick restore of database in case I have to retrieve an older version without going to tape.

0
 
Scott PletcherSenior DBACommented:
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.
0
 
Atlanta_MikeCommented:
Of course this all depends on the IT infrastructure, staff and budget. You have to do the best with what you have available.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
mcnuttlawAuthor Commented:
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?
0
 
Atlanta_MikeCommented:
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.
0
 
mcnuttlawAuthor Commented:
Okay Atlanta Mike,

Thanks for steering me in the right "thinking" direction.
0
 
Atlanta_MikeCommented:
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)
0
 
mcnuttlawAuthor Commented:
How much space (% wise) does zipping save you?
0
 
Atlanta_MikeCommented:
You'd be surprised. I get way over 60% - 70% in most instances...
0
 
mcnuttlawAuthor Commented:
Very intriguing.  I'll give it a shot.  I've never used the WinZip clu so this will be something new to try.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now