4

I'm a .NET Developer and I know how to write SQL but am clueless at doing any form of database maintenance scheduling yadda

So, I've recently taken over a project from a previous client. The site is hosted with Rackspace.

Anyway, the previous client was doing three full backups daily (every 8 hours). I'm just curious what the proper standard is?

Some questions I have.

Do I append or overwrite?

Do I set the daily backups to expire? [Rackspace does a daily differential and weekly full i believe as well]

Do I need to backup the transaction log?

Do I need to shrink the transaction log?

Please share some insights, best practices. They'd be greatly appreciated!

Thanks!!

Jack Marchetti
  • 561
  • 1
  • 6
  • 15

3 Answers3

3

There really isn't a "proper standard" as much as it is a question of:

How much data can you afford to lose?

A fairly standardized backup is to do something like this:

  • Full backup every week (or 2-3 times a week)
  • Differential backups every day (or several times a day)
  • Secondary server with log shipping implemented

Obviously it depends on what your load looks like, how much disk space you have for backups, etc.

Based on the information you gave, I don't think three full backups daily is necessary. You could easily get away with one full backup a day and then as many differential backups as you deem necessary.

The REAL key to backups isn't to plan your backups as much as it is to plan your RECOVERY PROCEDURE.

If you start with the end in mind... and the recovery process involved and the maximum amount of data you can possibly lose (or be willing to lose) then you'll find the backup plan that works for YOU.

KPWINC
  • 11,394
  • 3
  • 37
  • 45
  • I'm fairly certain disk space won't be an issue. The current DB backup i received is 9 gigs. Doing a full backup every 8 hours seems a bit taxing/excessive. – Jack Marchetti Jul 18 '09 at 04:38
3

Do I append or overwrite?

If it's going to tape afterward I'd overwrite otherwise pick an acceptable on disk timeframe for the backups to expire (based on your recovery time) and append.

Do I set the daily backups to expire? [Rackspace does a daily differential and weekly full i believe as well]

Yes unless disk space is not an issue

Do I need to backup the transaction log?

Yes as frequently as you require for a point in time recovery. for example if your SLA says that you will only lose 15 mins of data in case of a failure you need to back up the trans log every 15 mins.

Do I need to shrink the transaction log?

No- unless you've somehow let it grow to ginormous size at one point and it never uses more than 1% now, and you are running low on disk space, then maybe

Please share some insights, best practices. They'd be greatly appreciated

You need to work with the business to determine what type of data they expect to lose in case of an issue and how long then will allow the DB to be unavailable. Once you have the agreements on those in place the rest becomes a math exercise. In your example you mention that you do 3 fulls a day. You need to find out if that's just someones shot in the dark or if there is a reason it's 3 a day (maybe due to recovery times incrementals aren't possible)

Note that only after you get business requirements can you plan your recovery. The mantra of "plan your recovery" is so overused that many admins actually believe that and simply make the same types of mistakes they'd make if they just worried about backups.

Jim B
  • 24,081
  • 4
  • 36
  • 60
  • Actually, transaction log backup is only required if point-in-time recovery is required. If the requirement is acceptable data loss is 1 hour, then differentials can be used and the database kept in simple mode. It really boils down to requirements, as you've pointed out. – K. Brian Kelley Jul 18 '09 at 06:00
0

In my shop I run t-log backups every hour. My diffs run nightly and my full backups run Sundays. I don't have my backups setup to auto expire. I delete them manually when they are no longer needed. I haven't yet implemented log shipping as I don't have the resources available...yet.

RateControl
  • 1,207
  • 9
  • 20