The db_backupoperator role:The whole purpose of the db_backupoperator is to have sufficient rights to create backups of a database. Restore permissions, however, are not granted to the db_backupoperator role.The db_backupoperator also has the ability to execute the CHECKPOINT command. This command forces any dirty pages to be written to disk. According to Books Online, this can minimize the recovery time since all operations are guaranteed to be written to disk and SQL Server doesn’t have to reapply any changes that might have been in memory and didn’t get written to disk. Also, if the database is in simple recovery mode, a CHECKPOINT command will cause the transaction log to truncate. With that said, I cannot remember a time when I’ve actively used the CHECKPOINT command. One of the main reasons why is when SQL Server is shutdown in an orderly manner (i.e. not a server crash or a case where SQL Server was brought down with the command SHUTDOWN WITH NO WAIT), it will automatically issue a CHECKPOINT against each database to minimize the recovery time during the next SQL Server startup. As a result, there’s often not a need to execute the CHECKPOINT command.I will say that I don’t use this role a whole lot. Most of the backup jobs I run are controlled by SQL Server Agent or an external job scheduler and write backups to disk. These backups are then grabbed by a third-party product backup product and written to tape. Since the SQL Server Agent or job scheduler has sysadmin level access, I don’t have much need for this particular role. The reason I use automated processes like SQL Server Agent is to generate the proper backups on a regular schedule. If I have a junior DBA manually executing backups and he or she forgets one, it’ll probably end up being the very backup I need later on. Better to have it done automatically.
paul Tech 
Access Security
Posted - 2011-10-20 : 16:06:55
