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.