July 8, 2011 at 7:40 am
I need some feedback regarding the option in Maintenance Plan which is "Verify the integrity of backup upon completion" on MSSQL 2005
Is this what I can use to verify the integrity and the existence of the backup on the tape / disk after the back up.
Thanks.
July 18, 2011 at 3:19 pm
That option performs a "RESTORE VERIFYONLY" after a backup has been taken, and returns the result.
You can read more about RESTORE VERIFYONLY here: http://msdn.microsoft.com/en-us/library/ms188902%28v=SQL.90%29.aspx
While this does verify that your backup completed successfully, it is not 100% proof against errors. There are types of errors that this check will not detect, but will prevent the backup from working when you actually try to restore the db from it.
The only way to be completely certain that your backup is valid is to perform a checksum after it has been written, (optionally, depending on workload and resources) copy the backup to a new location, and do a full restore, and perform a DBCC CHECKDB. Once that is done, copy the backup to your backup media (tape, fileserver, etc) and re-run the checksum to verify the data after it has been copied.
This does require a good amount of scripting work, but there are a number of commercial solutions that do all of that work for you. I do not have personal experience with it, but RedGate's SQL Backup Pro solution is a popular option, and carries other benefits as well.
July 24, 2011 at 9:33 pm
SQL Verify from Yohz Software does all of the stuff that clubbavich mentions i.e.
- compute file checksums
- copy backup files to secondary locations
- restore backup files, and optionally run database consistency checks
- compare file checksums to ensure consistency
As far as I know, SQL Backup does not do checksums, nor automated restores of backup files i.e. given a list of backup files for different databases, restore them individually and report any errors.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 26, 2011 at 10:32 am
You're right, Ray. SQL Backup Pro does not include those features. I meant to recommend Redgate's SQL Backup and Restore Bundle, which does include that functionality.
That said, comparing the two products, SQL Verify is much cheaper and includes almost all of the same features as the bundle by Redgate. The only advantages to the Redgate bundle that I can see is that it integrates encryption into the backup process, and does a "Virtual Restore" which is supposed to be faster. While both features are nice, I get the feeling that SQL Verify has no problems kicking off scripts after it has verified a backup, and encrypting it at that point shouldn't be difficult.
July 27, 2011 at 4:03 am
Both products (SQL DBA Bundle and SQL Verify) approach verification of backup sets differently.
From the DBA bundle, you can use SQL Backup or SQL Hyperbac to restore the backup sets (usually) faster than you would a native backup. If you had backed up using checksums, you can be pretty certain that the restored database structures are consistent, otherwise you can just run DBCC CHECKDB on the restored databases. Or if space is an issue, you can use SQL Virtual Restore to mount the backup set as a virtual database. Again, if you can mount the database without errors, you can be fairly confident that the backup set is usable.
However, if you were to make copies of the backup files to other boxes, say to file servers, you really should also test (e.g. restore) them to ensure that they are usable. However, installing SQL Server and copies of the DBA bundle on every box holding copies of the backup files isn't always feasible or cost effective. SQL Verify addresses this by helping you compute file checksums on known good backup files, and helps you compare those checksums against checksums on new copies of the backup files. In this way, you don't even need to install SQL Server on your file servers to validate the backup files. And because disk errors/corruption may occur anytime, you can schedule SQL Verify to compute and compare checksums at periodic intervals to ensure that your backup files are still valid.
Hope this helps.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 27, 2011 at 5:23 am
So bottom line Ray, if you take backup with checksum, after a successful checkdb(), then run verify (which passes) and then successfully do a test restore you can't possibly be screwed (short of hd failure)?
Of course assuming you don,t move the file after that.
How many nested ifs does that make :laugh:?
July 28, 2011 at 8:20 am
Yes, there are a number of extra steps needed to ensure a backup works 100% of the time. It's unfortunate that Microsoft hasn't included a "virtual restore" functionality to SQL Server, as that's probably the most complicated step. Or at least an enhancement to verify-only.
Also, you can copy the file after you finish your verification, you just need to re-do the checksum on the copy to ensure it matches the original file.
July 29, 2011 at 1:16 am
Ninja's_RGR'us (7/27/2011)
So bottom line Ray, if you take backup with checksum, after a successful checkdb(), then run verify (which passes) and then successfully do a test restore you can't possibly be screwed (short of hd failure)?
I feel that if you're going to perform a restore, you might as well skip the verification part. A backup set that was created using the checksum option, and if parts of the backup data is now corrupted, will fail both verification and restore. However, the inverse is not true, where a backup set which passes verification, may not necessarily be restore-able, if the damage is to the backup set headers/footers.
You may still want to perform the verification part if you want to minimize the chances that a restore would fail and to avoid a situation where the database is unavailable to users while you find a good backup set to restore from.
E.g. backup data in backup set A is damaged, and if you restore using it, it replaces the existing database A and stops midway. Now database A is unavailable until you find another usable backup set to restore from. If you had performed the verification first, you would have known not to restore using that backup set, and minimize disruption to users who use that database.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 29, 2011 at 3:02 am
My train of thoughts is that I can't leave a copy of the DB to restore immediately so I have to wait up to 24 hours before actually attempting the restore. With that in mind it feels safer to do verification.
Now the big questions remain. What do you do if that tlog backup doesn't pass verification right after you take it? How can you undo / redo that whitout stopping the prod db?
August 1, 2011 at 9:54 am
Ninja's_RGR'us (7/29/2011)What do you do if that tlog backup doesn't pass verification right after you take it? How can you undo / redo that whitout stopping the prod db?
If a tlog backup goes bad, I would quickly take a full or differential backup (depending on how much data has changed since the last full backup), so that I have a new staging point to restore subsequent tlog backups. I don't think there's anything that can be done to salvage the tlog backup that's gone bad.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 1, 2011 at 10:04 am
Ray Mond (8/1/2011)
Ninja's_RGR'us (7/29/2011)What do you do if that tlog backup doesn't pass verification right after you take it? How can you undo / redo that whitout stopping the prod db?
If a tlog backup goes bad, I would quickly take a full or differential backup (depending on how much data has changed since the last full backup), so that I have a new staging point to restore subsequent tlog backups. I don't think there's anything that can be done to salvage the tlog backup that's gone bad.
Thanks for confirming.
Well we do have an option here. We have backupexec on top of the backup folders, so if I catch it in time I can go back for a copy in the full or diff backup of that application (which keeps those files for another 2 weeks).
On top of that being on 2 different raided san. It would really be bad luck to lose any of those files (not to mention really annoying!) 😉
If it happens, I buy a lottery ticket!
August 1, 2011 at 10:10 am
Ray Mond (8/1/2011)
Ninja's_RGR'us (7/29/2011)What do you do if that tlog backup doesn't pass verification right after you take it? How can you undo / redo that whitout stopping the prod db?
If a tlog backup goes bad, I would quickly take a full or differential backup (depending on how much data has changed since the last full backup), so that I have a new staging point to restore subsequent tlog backups. I don't think there's anything that can be done to salvage the tlog backup that's gone bad.
I'm not sure we're talking the same thing.
I was not reffering to goes bad after being good.
I was talking about being bad right after taking it... like failing verification 1 sec after taking it.
August 1, 2011 at 1:59 pm
From msdn:
If a log backup becomes missing or damaged, start a new log chain by creating a full or differential database backup and then backing up the transaction log to start a new log chain. We recommend that you retain transaction logs backups that come before a missing log backup, in case you ever want to restore the database to a point in time within those backups.
The reason this happens is that when you create a transaction log backup, it adds an entry to the msdb.dbo.backupset table indicating that backup's first LSN, and last LSN, which is basically a start and end point defining what has been covered by that log backup. If the backup fails verification, this entry is still in that table and is labeled as a valid backup. The next time SQL server goes to take a transaction log backup, it looks for the most recent "last_LSN" and does a backup after that point in time. As a result, you have a chunk missing from your backup chain, and all your tlog backups after that missing chunk are useless.
August 1, 2011 at 8:31 pm
Ninja's_RGR'us (8/1/2011)
I'm not sure we're talking the same thing.I was not reffering to goes bad after being good.
I was talking about being bad right after taking it... like failing verification 1 sec after taking it.
That was exactly what I was referring to in my reply. If the tlog backup failed verification, all subsequent tlog backups after that are pretty much useless, without a new starting point. The log chain is broken. To start a valid chain of tlog backups again, we'll need to take a new full or differential backup.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 2, 2011 at 6:03 am
Ray Mond (8/1/2011)
Ninja's_RGR'us (8/1/2011)
I'm not sure we're talking the same thing.I was not reffering to goes bad after being good.
I was talking about being bad right after taking it... like failing verification 1 sec after taking it.
That was exactly what I was referring to in my reply. If the tlog backup failed verification, all subsequent tlog backups after that are pretty much useless, without a new starting point. The log chain is broken. To start a valid chain of tlog backups again, we'll need to take a new full or differential backup.
Ah darn. Ok, so how often does it happen? I've never heard of bugs in that arena. Have you?
Would it be <annally> safer to take that backup on 2 different san arrays just in case 1 of them happens to fail? Then delete the 2nd copy as soon as the verify + restore passes?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply