Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Risky Backups Expand / Collapse
Author
Message
Posted Tuesday, January 13, 2009 5:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 14,022, Visits: 28,398
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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #635304
Posted Tuesday, January 13, 2009 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 6:43 AM
Points: 10, Visits: 22
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.
Post #635363
Posted Tuesday, January 13, 2009 7:25 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:49 AM
Points: 675, Visits: 424
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.
Post #635396
Posted Tuesday, January 13, 2009 7:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 2:22 PM
Points: 64, Visits: 28
I too agree with the one fire per backup. I use the one folder per database with the naming convention of dbname_TYPE_YYYYMMDD.bak
Post #635432
Posted Tuesday, January 13, 2009 8:01 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 2:32 PM
Points: 454, Visits: 172
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.



The distance between genius and insanity is measured only by success.
Post #635439
Posted Tuesday, January 13, 2009 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 6:43 AM
Points: 10, Visits: 22
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.
Post #635447
Posted Tuesday, January 13, 2009 8:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:23 PM
Points: 808, Visits: 1,994
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.


ATB

Charles Kincaid

Post #635452
Posted Tuesday, January 13, 2009 8:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, Visits: 694
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
Post #635455
Posted Tuesday, January 13, 2009 8:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 905, Visits: 2,006
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
Post #635458
Posted Tuesday, January 13, 2009 8:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 905, Visits: 2,006
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
Post #635466
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse