• State Verified Answer
  • +1 person also asked this people also asked this
  • Locked Locked
  • Replies 6 replies
  • Answers 1 answer
  • Subscribers 27 subscribers
  • Views 8343 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SQL Attachability & Snapshots

laytonj
over 7 years ago

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.

  • 0 over 7 years ago
    Just to follow up on question two for anyone that reads this in the future the answer seems to be yes. Regardless of when you schedule the rollup time to occur the SQL machine will snapshot.
  • +2 over 7 years ago
    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.
  • 0 over 7 years ago
    It may be a truncation thing since it and rollup both occur together on the nightly tasks. I guess the question now is do I turn truncation off? It is on by default. I would rather not have a snapshot occur at rollup since that machine does not need another one at that point.
  • 0 over 7 years ago
    That is up to you. An extra snapshot is not a terrible thing in my mind. If you turn off truncation, make sure there is a process in place that truncates your SQL logs, if necessary
  • 0 over 7 years ago
    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.
  • 0 over 7 years ago
    I will probably try it just to make sure it works and then leave it off. This particular server is not really all that mission critical and is backed up two different ways.

    I left the log truncation on and simply adjusted the roll up time to oppose the backup of that machine by 12 hours thereby adding an additional snapshot at a more useful time.