I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development environments. If I have a failure and want to use the log backups to restore to a point in time I will also need the backup taken by the developer.

This wouldn't happen if the developers only used COPY ONLY backups. So my question is: Is there a way to restrict users to only be able to perform COPY ONLY backups?


3 Answers 3


You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions.


USE test_kin
CREATE ROLE [deny_log_backups]
USE [test_kin]
use test_kin
DENY BACKUP LOG TO [deny_log_backups]
USE test_kin
EXEC sp_addrolemember N'deny_log_backups', N'kin'

Now test it :

backup database [test_kin]
to disk = 'C:\crap_test\kin_test_full.bak'
with compression, stats =10, init

---- ### success for FULL BACKUP 

backup log [test_kin]
to disk = 'C:\crap_test\kin_test_log.log'


Msg 262, Level 14, State 1, Line 3
BACKUP LOG permission denied in database 'test_kin'.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

Good question that I can't find a good answer for, but here's one. What if you removed everyone from having the backup role and then created a task that used the execute as command for a specific user that drops the copy_only backup file out to a specific location?


What you might be able to do is 1) deny backups (log and/or full) to such users but still 2) allow them to start a job that perform copy-only backup of a specific database (running under an account that is granted backup permission). I have such a solution in place to automate a PROD to PRE-PROD automated restores of databases for investigation purposes; specific users have access to a job (in PRE-PROD) that does a copy-only backup in PROD, moves the file from PROD to PRE-PROD and then restore the moved backup on the PRE-PROD server. Thus making sure such users have no way to break the backup logic on PROD.

(When investigating issues, developers might be tempted to take a full backup to restore it on a test server by actually moving the file from source to test server without knowing that they are breaking the backup logic... so I agree that forcing taking copy-only backups is important.)

  • Why are you concerned for FULL backup with copy_only as the OP is concerned with only breaking the LSNs ?
    – Kin Shah
    Commented May 1, 2014 at 19:55
  • Well, taking a FULL backup and then moving the file away would break the logic, right? If a developer does that, moves the file to a dev server, then the next LOG backup would have its FirstLSN value match the CheckPointLSN of the missing FULL backup. How to you then restore without the missing file? Commented May 1, 2014 at 20:12
  • A full backup does not reset LSN. Only a log backup will do it. I guess you are mixing up things.
    – Kin Shah
    Commented May 1, 2014 at 20:13
  • 1
    Oh yes, you are right. that would matter if instead of log backups you do diff backups. Then moving a full away is risky. I am mixing things up, thank. Commented May 1, 2014 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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