Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mariposa.azul.06
Starting Member
7 Posts |
 Posted - 2008-09-16 : 17:52:11
|
| I want to use the VERIFYONLY and CHECKSUM to check the integrity of my backups for a restore needs to happen I know my data is good.I currently have Maintenance Plans in place. My backups are going to a UNC path to my SAN disk. How can I do a RESTORE VERIFYONLY from disk = 'UNC path' and read the backup file? Without having to do a backup with format, checksum?Please let me know.thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-16 : 18:16:28
|
| There is no way to ensure that your backup is good unless you perform a restore. Due to this, you should setup a test system that periodically restores production backups.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
mariposa.azul.06
Starting Member
7 Posts |
Posted - 2008-09-16 : 23:15:34
|
Thank you Tara! |
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2008-09-17 : 11:16:02
|
| Actually, a successful restore doesn't always mean the database is fine. You could have some corrupted bytes in the backup file and the restore will still succeed, assuming you did not turn CHECKSUM on during the backup, which unfortunately is the default. To really confirm that the backup file is good, run DBCC CHECKDB on the database after the restore. Unfortunately, you can't run this on a database that you are using to test your transaction log backups, as the database will be in recovery mode.Which actually leaves using the CHECKSUM option during the backup, and running RESTORE VERIFYONLY on the backup files as the only practical way to test your backup files in this scenario.>> How can I do a RESTORE VERIFYONLY from disk = 'UNC path' and read the backup file?It's similar to your backup command e.g.RESTORE VERIFYONLY FROM DISK = '\\fileserver\backups\AdventureWorks.bak'>> Without having to do a backup with format, checksum?Don't understand this part. You'll need to use the CHECKSUM option to detect corrupted backup files.Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Inspect - SQL Server query analysis made simpleSQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2008-09-18 : 06:32:35
|
| >> RESTORE VERIFYONLY is not good enoughAgreed, if the backups were created using SQL Server 2000, or created using 2005/2008 without the CHECKSUM option. But with the CHECKSUM option, even a single byte that is off in the backup data will cause RESTORE VERIFYONLY to report an error.>> ... yet the backup is not restoreable.The inverse can also be true if the CHECKSUM option was not used during the backup. You might restore a database successfully from a corrupted backup file, but running DBCC CHECKDB will reveal consistency errors. This won't happen if you use the CHECKSUM option during the backups.Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Inspect - SQL Server query analysis made simpleSQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases. |
|
|
|
mariposa.azul.06
Starting Member
7 Posts |
Posted - 2008-09-18 : 16:00:00
|
| how come when i do the to disk = '\\fileserver\backups\dbname'i get an error of path not found? |
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2008-09-18 : 22:38:33
|
| Best guess would be that your SQL Server service startup account has no rights to that network share.Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Inspect - SQL Server query analysis made simpleSQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases. |
|
|
|
|
|
|
|
|
VERIFYONLY and CHECKSUM help...