Functionally, I don't see any major difference between the two. Seriously. However, since we're dealing with human beings, I agree with you. Never over-write an existing back-up, using a single backup per file, name the backup files meaningfully so that you can find them later and understand what they are (usually this entails using a date as well as the db name)... All very good practices to prevent human beings (or me) from screwing up.

I never really understood (unless I try to disconnect reality and travel back in time to the days when SQL was created and tape drives were directly attached to the database servers) why this option exists. Using a single file for multiple backups is completely outdated. Really, even backing up to individual files on disk is somewhat outdated given the power of current backup software. Using a hierarchical model in our backup software, we keep multiple days worth of backups (either fulls & logs or just fulls) on disk. Those are migrated to tape for offsite and longer term retention during the day.

If all you have available is standard SQL backups and the integrated tools, then certainly individual files, in a scheme that meets your business requirements will keep you out of your bosses office. A strong redundancy/backup solution above and beyond the free tools, or better still, a replication strategy to an off site location, may keep you from needing the backup for more than something to fill your spare time.

I always like to have more than one place to restore from. We backup to both the hard drives (SAN based storage) as well as sending backups to Tivoli with TDPSql. We only store one backup per file and they also get moved to Tivoli as part of the OS drive backups.

Maybe I'm just naive here, but if you're doing multiple backups to a single file, wouldn't that file become pretty large after even a few runs? Granted, you do several individual backup files it's the same amount of space required, but I know in my experience working with a single large file becomes more cumbersome than a few smaller ones. Not to mention the whole single point of failure thing.

It depends on how you've configured SQL Server. It can pare off the older backups from the file while adding new backups on. Much like it does with individual files, this is just done within the same file.

One of the key things that you lose with multiple backups in the same file is the ability to externally assess the backup. Was this backup at or about the same size as the last one? was there a substantially smaller backup this time? was it unusually large? With everything merged into a single file, you just can't tell. With individual files, you can actually gain some intelligence about how your DB's are accumulating data without having to open a file and evaluate it with tools.

I would think you could get a handle on the sizes simply by querying the backup history tables.

For me, much of this is simply ease of administration. As stated, it's easier to juggle the multiple small files. Then again, the dbs here aren't horrifically large. So our backups aren't that big either. They're compressed, so they're only around 5-10gb.

Our biggest db is only 256 gb. Which make me ask, in the community's experience, what is average size of the dbs? Understood that size is only part of the question (xactions/sec is equally important). But our transactional dbs are split into multiple small (<20gb) dbs. The "big" one is a consolidated reporting db.

I back up all three system databases to a single file and don't have a problem with that, otherwise each DB backs up to its own file nightly with overwrite. I do a noon incremental of our ERP system to the same file that I backed up to the previous night, but it's already been backed up by Tivoli at that point, but I think the risk is negligible as I'm also backing up the transaction log every 10 minutes. Unfortunately I don't have enough disk space to maintain multiple copies of our ERP system online; normally it backs up to our SAN box that has 6 TB of space, but that's not working at the moment so I'm dependent on Tivoli right now.

-----Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson

Jason Miller (1/13/2009)... Our biggest db is only 256 gb. Which make me ask, in the community's experience, what is average size of the dbs? ...

Wow. That'd require a lot of work! Most of my databases (individually, not collectively) would fit on my 8 gig flash drive, my biggest is our ERP system which is 24 gig. So anywhere from a few dozen meg to 24 gig for my place, but any answers would be wildly variable. I don't really see the database size as significant, though. You'd also need to consider how many servers, how many instances (not the same thing), the organization size, what they're being used for, and are you including the system databases, which are also databases, just not directly production.

-----Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson