I've been looking at the setup we've implemented for a hosted client, who has a database backed up several times per day (and the last week of backups always available). Say the backups require 20gb, and the drive they are hosted on (partition, really) is 30gb. There is over 10gb of other 'stuff' on the drive, across a sql 2000 install, OS and various other things, which means there is a constant struggle to keep the drive somewhere under completely full.

I see it as unnecessary to have a week of backups stored on a SCSI drive, but I'm no expert in this area and I'm really just looking for advice from other DBA's on how they manage this kind of backup scheme?

It's worth noting, we are using 3 drives at present, mirrored raid setup, and a spare to swap in at any point. So Disaster Recovery is at the heart of the problem, while minimising cost.

Any input welcomed....

Thanks in advance folks.

  • 3
    How often is the sql backup that's on disk backed up to tape? this really should be done ASAP, so you only need the previous nights backup on disk. If there's no offline backup, then I'd say disk space is the least of your worries! – Nick Kavadias Mar 3 '10 at 14:36

what I've implemented before as our standard SQL Server setup/backup procedures (SQL 2000-2008) is as follows

Data Partitioning Split the OS/SQL binaries, Data files and Log files onto 3 separate physical volumes, OS/SQL on RAID 1, Data on RAID 1 (This update following comment from Paul Randall (http://www.sqlskills.com/), Logs on RAID 1. Also recommend 1-2 hot spares. I would recommend at least some level of RAID for them all wherever possible but if space/cost is an issue, RAID 1 and place them all on the one set of drives, you'll still have the same level of redundancy (max of 1 drive fail) as RAID 5 (assuming 2 and 3 drives respectively)

Database Setup Implement 3 data files for each your databases, the default MDF and LDF for data and logs and also an NDF, set the NDF as your default data file in your database properties, leave the MDF alone. I've set all my databases to the Full Recovery Model as I do mirroring and need to be able to do point-in-time restores. I would recommend doing this anyway but it does come with a warning (below)

BACKUPS The single most important part of your database setup is getting your backups (and associated restores) correct. My standardised setup is a Full backup @2am when we have a narrow window of people not using the databases (global operation with staff in multiple timezones from Singapore to New York) then a transaction log backup every 5 hrs starting @ 8am (0800, 1300, 1600, 2100). Disclaimer Make damn sure that if you have your databases set to Full Recovery Model that you are doing Transaction Log Backups otherwise you can end up with monstrous log files and then you might have to truncate and then shrink them, which according to Paul Randall (Hi Paul!), who I would trust implicitly relating to SQL, is aVeryBadThing(tm). The daily backups (including the transaction logs relating to them) are kept locally to the machine then scooped up nightly by our backup service and taken offsite by the admin on duty. This way if the machine is lost we lose only the data from the last full backup and in the case of someone screwing up the data, only the information input between the last T-Log backup and that point in time (we can go into the current log and playback any other transactions but we usually don't as our SLAs are built to accomodate this) You can of course send the transaction log backups off machine or even off site at the time of creation but that's a decision for you to make.

Data File Shrinking DON'T DO IT (unless you get into a situation where you can't avoid it) Certainly don't go scheduling it!

Indexing and Fragmentation This should be a manual (but scripted) operation that is carried out only when required, you should be doing regular maintenance and index fragmentation should be on this list. On my monthly plan I check the sizes of the data files, the index fragmentation and the number of databases (we sometimes have developers add databases and not tell us, the maintenance plans take care of the backups but sometimes they screw up the data file layouts and naming conventions), if we detect any new databases they get audited with respect to their size, data file layout and backups (never hurts to check and double check backups)

Please feel free anyone, to pick holes in the above or at least to tell me that I'm talking nonsense.


Don't come at it from the "backup" perspective, come at it from the "restore" perspective. Sit down and define what you need in order to be able to get things back, test some recoveries to make sure that you've got the list correct and complete, and only then start considering your backup strategy.

In general with a server application, backing up the OS, the program files, the configuration and all the other doo-dahs is something I would consider essential. Recovery of the raw data is easy, but recovery of the configuration is not. As well as that you have current patch levels to get back, OS as well as application config, any local user accounts you may have created for services to use, and so on.

So back up everything and you're certain that you've missed nothing is my mantra.

Now, you're keeping several backups per day for a week onto the same storage as the OS. This is fine if you accidentally lose some data; if you lose the OS or have a hardware failure, you are EFF-YOU-SEE-KAY'ed and no mistake.

One approach I have used before is to do regular data (only) dumps to disk during the day, but a full backup of everything to tape at night. This cuts down on storage requirements for the daytime dumps, which can in turn give a longer recovery window for accidentally lost data (by keeping more than a week's worth). Keeping the full to tape means that I can do a full recovery of everything even to an alternate server if required. I could go further, but right now my business requirements don't need me to - your's might.

  • 1
    Up-vote just for your lead: Don't come at it from the "backup" perspective, come at it from the "restore" perspective. Very wise statement. – dwightgunning Mar 3 '10 at 20:11

Your current backup plan, as described, does not really cover a lot of scenarios where disaster recovery would be needed.

You should at the very least have backups away from that one machine in case something drastic like a power supply malfunction kills all the drives at once, preferably at another site in case something happens to the building that the server is housed in.

There is nothing wrong with keeping a local copy of the backups on the server itself as well for easy access, such as on a new larger drive as you suggest, but these should not be your only backups.

From your current space constrained position I would recommend keeping only the latest couple of backups local to the machine and keep the rest on a larger drive on another machine, preferably off-site if possible (protocols like rsync can reduce the bandwidth required for off-site backups considerably). If the machine is where you have physical access to it then you could instead (or as well) keep offline backups by copying them to an external drive and keeping that away from the machine at other times (or better still, have two or more external drives and cycle them - large drives in USB cages are very cheap these days). In the case where you have physical access, a tape drive with a number of tapes in the cycle may be a better option.


So, while you have three drives, you are really just using 1 drives worth of space, right ? Two drives in mirror + the third as the spare ...

You could gain some space through a migration to RAID 5

This being said, you could just add another large drive to hold the backups as just suggested in your question.

  • 4
    RAID5 is often not recommended for database work due to potential write performance issues (though for some environments write speed is not a major issue, so this advice depends on the write/read characteristics of the application(s) involved), RAID10 being preferred if a fourth drive will fit and is affordable. – David Spillett Mar 3 '10 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.