Best Practices for SQL Server Backup Management
The thing about backup management is that, by the time you discover there’s a flaw in your backup and recovery system, it’s usually too late to do anything about it. Many DBAs and database owners have found themselves grounded during critical system restore procedures because of some glitch no one noticed before.
This article provides important tips to ensure that your SQL backup plans don’t fail you at critical moments:
1. Daily backup schedule
Performing full daily database backups is a procedure that should be a part of every data protection plan in organizations. SQL servers have provisions for online backups, which allow admins, end users and various SQL Server queries to run actively in the process of a backup operation.
Even then, larger databases will obviously require longer backup times, hence the need for frequent updating. If you want to further reduce the time taken for backup, you can employ strategies like backup data compression and running backups to disk.
2. Transaction log backups
After the data itself, the transaction log backup is the next most important thing to keep updated. It contains details of all activities recently carried out within a database, and is useful when the DBA wishes to restore a database to a specified point in time. Frequent backups on the log also prevent it from getting full, as older records are commonly truncated.
Backup of the transaction log can also take place while the SQL server remains active. Enterprises with very active databases can back up their transaction logs in 10-minute intervals while the less active ones can have longer intervals – half-hour to hour-long intervals for instance.
3. System databases
Your SQL Server System databases should also be part of your backup plan otherwise you’re running an incomplete strategy. System databases such as the model, MSDB and Master databases hold vital information about system configuration and SQL Server jobs. This information is also necessary should there be need for a complete system restore.
System installations that are more stable can be backed up weekly, but those that have continuously changing instances should have a daily backup schedule.
4. Host OS
The SQL Server runs on an operating system. If a major hardware failure occurs, this may necessitate carrying out complete system restorations, which starts with the operating system on which the SQL Server runs. Therefore, a robust backup system should also include frequent backups of host operating systems.
It is recommended that these be run daily, but organizations that make less changes can have a less frequent interval. At the least, the host OS partition should be backed up after all configuration changes and/or system updates.
5. Bonus tip: Recovery operation drills
As the business requirements change and grow, standing backup strategies may be rendered incomplete or altogether obsolete. Organizations should frequently test their backup and recovery strategies with multiple case scenarios to ensure that they will work at the critical moment. This includes individual database restore plans as well as total system restorations.
