sql server backup how to verify, your backup is correct and not corrupted

im go to take adhoc sql full backup, before application upgrade
eg:

BACKUP DATABASE testdb
TO DISK = 'C:\testdb.BAK'
WITH STATS, DESCRIPTION = 'Full backup for AdventureWorks'
GO

how do you I know, my backup is not corrupted, and will work after restore, incase if required

Sorry I dont have environment to test.. it..

Do i need to do consistency check or readability check before take backup, if yes, how can i do that in Tsql?
bsarahimAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
After creating the backup file, run RESTORE VERIFYONLY on it.  That's probably the best thing you can do short of a full restore.

Kent
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
For physical corruption you can test it only by restoring the backup. The RESTORE VERIFYONLY suggested by Kent is the way to do it.
For logical corruption you can BACKUP WITH CHECKSUM as this will make the backup to verify each page that's being backed up.
BACKUP DATABASE testdb 
TO DISK = 'C:\testdb.BAK'
WITH CHECKSUM, STATS, DESCRIPTION = 'Full backup for AdventureWorks'
GO

Open in new window

Mind that backups with checksum will take longer than a regular backup.
NOTE: If you're using compress backup then the CHECKSUM will be the default behaviour so no need to explicitly add it to the backup command.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bsarahimAuthor Commented:
Thanks both of you.. Can I estimate the backup time which is going to take with checksum.. ? any idea
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
It really depends on the number of data pages but I wouldn't expect a big difference unless the database is really huge.
0
bsarahimAuthor Commented:
It is 700-800 gb around.. thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
With that size I would turn on the compression for the database backups. It will reduce around 60%-70% of the backup size and time.
0
bsarahimAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.