A good backup strategy is not just about creating a backup. This is a process of many elements and conditions that should be filed to achieve the final goal, and this is the most efficient backup strategy plan. To create a good strategy, we need to analyze the following:
- Who can create backups?
- Backup media
- Types of backup
This issue is part of the security subject, and that is covered in Chapter 8, User Management. For now, let's just say that a SQL Server user who is authorized to execute backup operations needs to have a security role. Security roles are:
- sysadmin server role: Every user with sysadmin permission can work with backups. Our default sa user is a member of the sysadmin role.
- db_owner database role: Any user who can create databases by default can execute any backup/restore operations.
- db_backupoperator database role: Sometimes you need just one person to deal with every aspect of backup operations. This is common for large scale...