2

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.

5

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

Is there a way to force users to only take a full backup with COPY_ONLY so they don't interfere with diff backups

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.

5

There is no way to grant backup but restrict to certain options.

Stop giving them permissions to backup databases. Create a stored procedure that executes as owner and backs up the database with copy_only, and give these users exec rights on the stored procedure. If you need this for multiple databases you can create multiple procedures or you can use dynamic SQL - just make sure to QUOTENAME() the database name instead of blindly slapping it into the statement, or check it against a list of known databases you want to allow.

Barring that, education is the only practical route to preventing this. You can always audit and reprimand after the fact, of course.

2

You could create a simple audit report which shows who is doing the non-COPY_ONLY backups, eg

select user_name, min(backup_start_date) min_backup_start_date, max(backup_start_date) max_backup_start_date, count(*) records
from msdb.dbo.backupset
where is_copy_only = 0
group by user_name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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