Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 9 down vote accepted

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.

e.g.

USE test_kin
GO
CREATE ROLE [deny_log_backups]
GO
USE [test_kin]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
GO
ALTER USER [Kin] WITH DEFAULT_SCHEMA=[dbo]
GO
use test_kin
GO
DENY BACKUP LOG TO [deny_log_backups]
GO
USE test_kin
GO
EXEC sp_addrolemember N'deny_log_backups', N'kin'
GO

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'

 --- $$$ ERROR MESSAGE 

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.
share|improve this answer

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?

share|improve this answer

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.)

share|improve this answer
    
Why are you concerned for FULL backup with copy_only as the OP is concerned with only breaking the LSNs ? – Kin May 1 '14 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? – Benjamin RAIBAUD May 1 '14 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 May 1 '14 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. – Benjamin RAIBAUD May 1 '14 at 20:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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