I have a pretty standard backup scheme implemented: Fulls on Friday, Differentials nightly, Transaction Logs throughout the day. These are all automated tasks.

Occasionally we will need to take "one off" full backups for various reasons.

A lot of times, my users that are taking the one offs remember to use the copy only option, but sometimes they do not. This presents the obvious problem of the following nightly differential being based off of the "one off" full backups.

Is there a way to prevent any user (except for my backup operator) from making backups without the copy only option?

  • 1
    Yeah... don't let non-admins do backups at all. Backups can cause i/o issues on a server, which the user might not be aware of. Add to that the space issues that you could cause and I can't think of a good reason that an admin shouldn't be doing this task on behalf of the users. – Ben Thul Feb 15 '12 at 22:32
  • This doesn't answer his question, and if anything should be comment. Maybe the users are devs that need backups of the database to do dev-y type things to. You have no way of knowing. – Holocryptic Feb 15 '12 at 22:36
  • Indeed... so, if we have issues with out database that a vendor needs to look at, it is very common for the vendor to request a backup. it <does> tax the server, but it is an acceptable slowdown for the /possibility/ of whatever issue we're working with to get fixed. (: I agree that in an ideal world I'd only let the DBA take backups, but I'm not always available to do that. – Rob Feb 16 '12 at 14:12
  • It is an answer, just not a technical answer. What the poster is asking for will cause a problem eventually. So are you saying that we should just answer users' questions regardless of whether what they're asking for is a good idea or not? Seems like a bad policy to me. – Ben Thul Feb 17 '12 at 20:01

I'm not a SQL expert, but I'm going to throw this out there anyways. Can you create an automated task that the users can call that carries the copy only option?

  • I'm liking this idea... so, make them not a backup operator, but have the script impersonate a backup operator and perform the backup for them.. I guess this is probably the best solution.. – Rob Feb 16 '12 at 14:14

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.