Copy Only - Security Policy

Question

Post reply

Copy Only - Security Policy

drylowens

Grasshopper

Points: 17
More actions
October 9, 2013 at 6:42 am

#284423

Hi all,
I'm looking to create a policy that will stop any manual backups being performed, unless they are copy only. I haven't had much success so far. Does anybody know if this is possible? Is there a facet for this?
Cheers in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

lkennedy76 SSChampion Points: 13683 More actions · Answer 1

If you accomplish this please share! 🙂

MCSE SQL Server 2012\2014\2016

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I don't believe it's possible. Even if there was a policy for it, policies are implemented by triggers and they're AFTER triggers and you can't roll a backup back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

drylowens Grasshopper Points: 17 More actions · Answer 3

Sadly, I think your are probably right. I may have to try and come at it from a different angle.

jimbobmcgee Ten Centuries Points: 1108 More actions · Answer 4

I also often have this need (e.g. when I'm running a differential backup scheme and don't want the 'chain' to be broken).

The closest I get to achieving this is to manually DENY BACKUP on each of the databases that are under the full/diff policy, then add an SP_BACKUP stored procedure to the master database that does the BACKUP WITH COPY_ONLY in dynamic SQL run with EXECUTE AS LOGIN='sa'.

(You may be able to hook CREATE_DATABASE with a DDL trigger to apply the DENY automatically -- I've never looked into it. I'd rather someone up high just yielded and gave us DENY BACKUP ANY DATABASE already!)

Then I educate the users (and re-educate, and re-educate again) to use that procedure instead of the BACKUP command/GUI.

Of course, I expect this only works because I have a very limited set of people who have any business performing out-of-band backups, anyway (e.g. the deployment team), and they have CONTROL SERVER (which gives them system-wide permission to impersonate SA, while honouring the DENY BACKUP).

That and I'm not trying to strictly enforce the denial, merely put up enough of a roadblock that the error reminds the deployment team that they must use a different approach, if they have absent-mindedly gone into 'next, next, next mode'.

(Let's not get into the semantics of whether a team that needs this kind of hand-holding should have that level of access to the server or not, or whether slipping into 'next, next, next' mode on a production server should be punishable by death!)

Of course, it may be sufficient to put such an SP_BACKUP procedure into the database in question (maybe in a dedicated support schema) and allow it to EXECUTE AS USER='dbo', rather than go the server-permissions route. I've not tried.

If it is, though, I expect you could hook CREATE_DATABASE to create your stored procedure and set your DENY permission. It really depends how often you spin up a new database as to whether or not it is worth your time.

HTH

J.

drylowens Grasshopper Points: 17 More actions · Answer 5

Thanks J

I was thinking of going with a stored procedure as well, but I just know this is going to be hard sell. Problem I have is that mostly everybody uses copy only (we are constantly, as in a daily basis, backing up, lifting and shifting) and its really only the odd occasion that a non-copy only backup goes through. I know I will just be told, "but we always use copy-only anyway!"

I have an alert so, I can fix the backup chain with a new full, if it does happen. But its a hassle we could all do without!

Thanks again for the suggestions.

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

If they're really doing that many copy only (presumably Full) backups, then why not switch everything to Full or Bulk-logged recovery model and tell them you can always use PITR recovery?