4

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?

4
  • 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, 2012 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. Feb 15, 2012 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, 2012 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, 2012 at 20:01

1 Answer 1

4

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?

1
  • 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, 2012 at 14:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .