January 13, 2009 at 8:07 am
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.
January 13, 2009 at 8:15 am
Jason Miller (1/13/2009)
I prefer the dates YYYYMMDD easier to sort, at least for me.
I have latched onto ISO-8601 for dates and times. I have long advocated that date/time be stored in UTC and let the UI convert to local time.
ATBCharles Kincaid
January 13, 2009 at 8:16 am
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.
Honor Super Omnia-
Jason Miller
January 13, 2009 at 8:18 am
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 13, 2009 at 8:27 am
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 13, 2009 at 8:45 am
Wayne West (1/13/2009)
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.
Our reporting db is 256 gb. Then there's the investment dbs, about 20, ranging from 3 - 18gb.
We're running 9 SAN based servers, 11 instances, plus two more servers in our DR location. Four instances, on two separate boxes plus a two node cluster are productive, 7 instances on five boxes are developmental. (plus there's a couple more instances out on VM for small stuff, Infrastructure team has a db for tracking tickets, Corp Accounting has their own, etc.)
Our productive reporting servers house our numbers for clients, so they're getting hits 20x7 (we have a 4 hour window). We're no google/Amazon, the site gets a couple of hundred reporting queries an hour. The investment system on the cluster is 20x7 as well, but only for internal users.
I didn't include the system dbs, for they're negligible in terms of size.
Honor Super Omnia-
Jason Miller
January 13, 2009 at 8:50 am
For naming convention, I use YYMMDDDatabaseName. Separate file for each database. Retention I keep a Yearly, Monthly and current Months weekly and two weeks of the daily. They are store on separate drive and tape backups. Even with reports saying the backup completed successfully, I have experience that some restores fail. I don't want to go to my boss and say I've lost all the data because our tape have be demagnetized or won't restore a space is not as costly as it use to be. I did have a co-worker that took over the backups and switch to only having two weeks of backups and all backups failed. Because I did not like him switching to the two-week method I made backups on my own and saved his job. He now does the good old grandfather method.
January 13, 2009 at 8:53 am
I have been running a full backup with format, then transaction log backups to a single file. That file then gets picked up by our backup job that backups file servers and everything else.
This process has worked well for me for quite a while.
Its nice to have multiple backups on the database server, but those backups should be copied to some other device soon after it is complete.
The backups stored on different systems have saved me more often than the backups on the server itself.
January 13, 2009 at 8:54 am
Wayne West (1/13/2009)
... 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.
The size of some databases depends on transaction volume. Your payroll database may get a few new rows per employee per day. Then consider phone companies. Then get a new row every time someone picks up or puts down the receiver. Industrial manufacturing plants doing flow control monitoring may log several readings per second from every sensor: Temperature, pressure, flow rate, viscosity, and so on. A lot of that is EAV in nature. Time Stamp, Sensor Number, Value.
Then are we talking data size, log size, or both. I have a production tracking system that uses production schedules. Very few new rows per day but each one gets updated for count and weight by each item that crosses the line. My MDF size is reasonable but the log size is scary.
ATBCharles Kincaid
January 13, 2009 at 8:57 am
Wayne,
Backing up all system dbs to one file actually makes some sense. You'd likely want all of these together in a DR situation. Thanks for that note.
January 13, 2009 at 9:02 am
I'd agree with Wayne on sizes as well. Most of the dbs I've managed have been low GBs. Even when we had a 700GB reporting db, we had hundreds of databases that were < 10GB. We tried to manage them in a similar manner to ensure there were few exceptions.
I've gone with dbname_yyyymmdd as a standard. That sorts them by dates easily. Also gives you a clue on how long a backup takes with the last modified date.
January 13, 2009 at 9:03 am
Jason Miller (1/13/2009)
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.
My largest DB is almost 600gb. After that they drop down to less than 30gb. I always do backups to seperate files, and have a script that moves them off of the server to two different network locations and then they get written to tape. With the 600gb db I get that .bak down to less than 60gb for backup - but even at that size the network transfer can take a while so I keep 2 copies of my full .bak on the server for quick restore.
January 13, 2009 at 9:12 am
Steve Jones - Editor (1/13/2009)
Wayne,Backing up all system dbs to one file actually makes some sense. You'd likely want all of these together in a DR situation. Thanks for that note.
Always glad to contribute! It made sense to me, and it seems to me that backing up the system databases is frequently neglected when talking about database backups. I shudder to think what my life would be like if I lost a server without those system database backups, especially losing all my DTS packages! I try to script them out as a part of monthly maintenance, but I'm not as consistent on that as I'd like.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 13, 2009 at 9:29 am
we backup 60 or so SQL servers with hundreds of databases. they backup to the same file (.bak) every nite, each database has its own file. The tape backup system grabs a copy of the dump every nite and archives it. The next nite's backup overwrites the dump. We've had loads of restores done with this system and it always seems to work.
Just our way of doing it, we've been doing this way for 8 years.
AW
January 13, 2009 at 9:37 am
Don't forget data governance in this equation. It's much harder to rotate out old backups (or to provide opposing counsel with just the most recent backup) if you've got them all lumped into one file...
:hehe:
Also, SPoF aside, the larger your "one file" is, the harder it is to copy for offsite storage.
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply