verify backup integrity sql server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » verify backup integrity sql server

verify backup integrity sql server

Rate Topic

Display Mode

Topic Options

Author

Message

MAK-1128556

Posted Tuesday, December 01, 2009 9:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 7:44 AM
Points: 176, Visits: 290

I have a 300 GB 2005 SQL db, and backup run around 5hours.
How much extra time is requird, if I check the verify backup integrity sql server

Post #826820

GSquared

Posted Tuesday, December 01, 2009 10:04 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

It will probably take a while to verify. That's a fair chunk of data.

On the other hand, the last thing you want is to go to restore the database, and find that your backup is junk. You definitely need to validate them.

Usually, with larger databases like that, you wouldn't use the standard maintenance wizard backups. You'd back up based on files, and a more complex but more efficient backup scheme.

One thing that can often help with backup and restore speed is data compression. I/O is usually the largest bottleneck for backups, and compression can reduce that by a large margin. Since you're using SQL 2005 (2008 Enterprise can do compressed backups on its own), I'd look into a third party backup tool. RedGate (link at the top of the page) has one that works quite well, and can speed up backups by a huge margin.

- 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

Post #826834

GilaMonster

Posted Tuesday, December 01, 2009 10:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:29 PM
Points: 37,730, Visits: 29,997

Just bear in mind that unless you did the backup with the CheckSum option, a successful RESTORE VERIFYONLY does not guarantee that the backup is restorable. As far as I'm aware, it checks the header and not very much more. If you want to ensure that the backup is restorable, restore it somewhere else

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #826838

« Prev Topic | Next Topic »

Permissions