Is there a way to force users to only take a full backup with COPY_ONLY so they don't interfere with differential backups?
Our developers need to take backups every now and then but they don't always remember to use the COPY_ONLY option and then they move the backup away so it causes a few issues.
Best Answer
No there is no option or restriction which we can enforce so that a user can take copy_only backup when ever he tries to take any backup but what you can do is create a procedure which backups database with copy only option and grant execute on the procedure to various users and then remove backup database privilege from the user.
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. So move accordingly. Its really not a good practice to allow general users to backup database as per there mood but I guess requirements vary as per different firms.For more details read Security section in This Link
Let me tell you there is no concept of copy only differential backup. It only applies to full backup and transaction log backup.
The other option is through social engineering by educating the developers about possible issues which can occur with normal full and log backup may be about if they take log backup it might break LSN chain and Log shipping would fail and they would be responsible. There is also a simple option to ask developers to use inbuilt script kept at some shared location(hope you have one in your firm) and make sure you have copy only clause in that scripts. I guess everybody would find it helpful to use already written script instead of writing by own or taking by GUI.