Take the 2-minute tour ×
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.

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
    
Kin's solution is better. –  ACDBA May 1 at 16:13

3 Answers 3

up vote 8 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
    
Kin, you are incorrect. A full backup resets the differential pointers. If the full backup than is lost e.g. because it was only meant to be a temporary, differentials take afterwards cannot be restored. Full and Log backups do not in any way interact. (But a lost Log backup will still break the log-chain.) –  Sebastian Meine May 1 at 20:00
1  
Well, I guess I did not read the question completely. The OP's concern is the log-chain, so your solution works. –  Sebastian Meine May 1 at 20:03
    
Excellent answer. I will move this into my test environments in the coming days. Thanks –  James Anderson May 2 at 10:00

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
    
This is more complicated and requires more work to do. Please refer to answer above for an easy solution. –  Kin May 1 at 16:10
1  
Agreed. I like your solution. –  ACDBA May 1 at 16:12

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