Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Risky Backups


Risky Backups

Author
Message
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19404 Visits: 32324
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Erich Huemoeller
Erich Huemoeller
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
bklein2
bklein2
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 504
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.
austin.mcguire
austin.mcguire
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
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
Brnbngls
Brnbngls
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 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.
Erich Huemoeller
Erich Huemoeller
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1117 Visits: 2383
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
Jason Miller-476791
Jason Miller-476791
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
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
Wayne West
Wayne West
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 3694
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
Wayne West
Wayne West
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 3694
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search