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


Risky Backups


Risky Backups

Author
Message
don.schaeffer
don.schaeffer
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 1057
I'm curious how many people verify their backups. I found that step can take twice as long as performing the backup itself.
Wayne West
Wayne West
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1544 Visits: 3694
I both like and don't like the concept of the backup file containing the date of backup, right now I don't code the date into the file name on our systems, and I don't use maintenance plans for backups. It's nice in that you can look at the file and see the database name and backup date, but the file has a timestamp that also shows the date, so the information almost seems redundant since the file timestamp is not altered when the file is restored. Plus, the thought of digging in to the system tables (though I love working with system tables!) to find file names to delete just really bugs me for some reason. I prefer to have my system just overwrite the backup and not worry about it. On occasion a backup will fail and something will have squirreled-up the backup file, in which case I delete it and do a backup with overwrite and that seems to take care of it.

-----
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.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1544 Visits: 3694
don.schaeffer (1/13/2009)
I'm curious how many people verify their backups. I found that step can take twice as long as performing the backup itself.

I always verify all backups at the SQL Server level, but that doesn't verify against the data, just that the backup is structurally sound. My guy who does the actual Tivoli backups confirmed that he does compares, but it might just be at the computed CRC value. Tivoli is a pretty bloody complex system, he said he'd have to research exactly what a verify consists of to give me a better answer.

Yeah, it could easily double your backup time, though with compressed backups....

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476
All I have to say is:

If you do not want to get hit by a train ...
do not sit on the railroad tracks !

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
FFalcon1961
FFalcon1961
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 346
Just remember your backup is only as good as the restore of the data. If the restore fails then as far as the customer is concern you didn't backup their data.BigGrin
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
I do each backup to separate files. Name with DB name and datetime YYYYMMDDHHMM (hours and minutes matters with log backups). Single file = single point of failure = not a good idea.

I test the backups at least weekly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
FFalcon1961
FFalcon1961
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 346
Yes you can setup to go to minutes
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 801
Having seen corrupt backup catalogs, disk failures, and the like, I prefer simplicity on recovery; so my vote and practice is to separate the backups, not over-write existing files, and permanantly archive date-named regular intervals.
Chris Hornung-847288
Chris Hornung-847288
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: 69
Always run a verification. It is also a real good idea to run restores periodically.

One of the worst feelings I have had was pulling a backup from tape (approx 6 hours) and restoring (another 6 hours) to find the database could not be brought back on-line. After a call to Microsoft support we determined the best course of action was a previous days backup.

It was a very good thing the backup from the previous day worked, and the nature of the business allowed re-processing the missing day's work.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
This is funny to me, because until my 1st 2k8 installation Christmas week, I didn't know that SQL Server would actually append to an existing disk file. That didn't stop me from anally not selecting 'Append to existing media" when doing a manual backup.

Size, simplicity, manual file manipulation. Those are all good enough reasons for me. I wouldn't want to be the stranger picking up the pieces that came across something named "ALL_SYSTEM_DBS.BAK." There really isn't any difference between multiple DBs in a single file, and multiple files in a single folder.

I'll stick to separate files.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

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