Important Points before backing up the SQL Server Databases.
Backing up SQL Server databases is one of the important job to protect the data.
Consider the below points while planning and backing up the databases.
1. One of the important step is the Backup plan. Most of the DBA only consider the user database backup during backup plan but DBA has to consider the other things as well like (Jobs, Analysis Services, Integration Services, Reporting Services, Users, System databases and other SQL server properties which is configured for an instances in Environment.
2. Once the Backup Plan Designed you should include the physical backup storage location protection policy, reason is that storage has all the backup of databases. In case if someone got the data than it would be very dangerous to your organisation. He can restore the data from backup file and can access the confidential information which you never want to share with any one. Ideally there are third party tools available in the market which allow backup encryption.
3. Always create one physical backup file for a database. By doing this we can maintain the consistency of backups in case you are maintaining one single backup file and overwrite it every time and due to any reason corruption occur at DB level than it will impact the entire backup and you will not be able to restore the entire data from the corrupted backup file so always create one single backup file while doing database backup.
4. Always store database backup to another location other than SQL Data and Log file location. In case any of (MDF and LDF) file drive corrupted than you will be in a state to restore it from the last available backup.
5. Never grant rights to multiple users for creating backups but you can grant permission to monitor the backup status.
6. Always monitor the backup status, size and time. in case backup fail than trigger a notification so that Database team aware about the backup failure status. By doing this you can do the capacity planning.
7. Always take the system database backup as and when any changes happen at instance level or database level.
8. With backup strategy you must plan the Restore test as well to check the backup consistency.
9. Maintain the backup retention policy as well according to organisation retention policy.