SQL Attachability & Snapshots

Question

Hi All, 
 A two-parter on a 2008r2 server running an instance of SQL that we are backing up. 
 1) I think I generally understand the attachability check concept but we do not have a full install to test it against not can I really justify purchasing one for the task. The software that uses it on the server in question was a bundled package for document management. It may or may not work to check itself but so far I haven't tried it. How important is this step? All the backup flag as yellow (unchecked). How often does it really need to be done? If it is critical what do you do if it fails? 
 2) This same server seems to snapshot every night after roll-up time regardless of the schedule I set it at. This is the only server in the lot that does this but it is the only one that has SQL on it. Is this triggered somehow by the roll-up or is it by default or what? It seems like this behavior is not by design. 
 Thanks.

Emte · Accepted Answer

Rollup. This maybe related to log truncation (nightly job) vs rollup? Log truncation will only run after a successful backup. For example, if you force a log truncation job from the GUI, a backup will kick off then perform truncation after. 
 
Attachability. The benefits of this feature are questionable to me. If you have a critical SQL application(s) then making sure the attachability check is done probably makes sense. But if its not critical, then paying for a full sql install probably wont make sense. 
 
You can use a remote SQL for attachability but this causes its own issues (performance) 
 
Attachability does not do any deep scan of the SQL data. It simply tries to attach the DB to SQL to see if this basic (but important) step can be done. Its possible for SQL to run and backup just fine but contain some inconsistency that will cause the attach process to fail. You can typically fix this by running some sql maintenance commands against the DB but if you were running attachability, you would know before hand this was an issue and could fix the DB before there was an issue.

Tim Seymour · Answer

1) Attachability check is simply an after backup check of the SQL data in the recovery point. It runs as a nightly job and checks one recovery point per day. Since each incremental relies on the previous chain, if the check works on one recovery point then all recovery points prior should be ok as well. It is not necessary to run the check. You could simply test your backups on a schedule of some sort to validate them that way. This is an additional feature to simplify backup checks. 
 
Personally I recommend turning on attachability checks and have it use the agents SQL instance. That saves time, money, and reduces the potential for version mismatch issues and errors. 
 
2) If you have SQL log truncation enabled it will trigger a backup during the nightly jobs. That's how SQL logs are truncated. We call a full VSS shadow copy backup and take an incremental backup. When that completes, VSS marks SQL as having a full backup and then SQL can truncate it's logs up to that point. If you disable SQL log truncation like scashman said, it will stop taking that backup but you need to have another method for truncating logs.