• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 48
  • Last Modified:

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?
0
bsarahim
Asked:
bsarahim
  • 3
  • 3
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
bsarahimAuthor Commented:
Thanks both of you.. Can I estimate the backup time which is going to take with checksum.. ? any idea
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now