In this blog post we will discuss 7 points to remember while taking effective backup for SQL Server.
- Always take backup during off business hours, as backup operations generate IO activities.
- Take backup on a different drive from your data and log file, to get maximum IO efficiency from your database.
- Make sure to check the status of the backup job frequently. If the status of the backup job is failed, you may want to fix it as soon as possible.
- Check the database recovery model under Database Properties >> Recovery Model. You want to set it appropriately based on your business needs.
- Take frequent log backups so your log files do not grow too big.
- Set appropriate frequency of your database backup. (e.g. Full backup – daily, differential backup – hourly, log backup – every 10 minutes)
- Test your database backup by restoring it in your test environment.
I want to specifically stress on the 7th item. I frequently do consulting for organization for Performance Tuning. While we are doing SQL Server Performance Tuning work, we often end up taking about backup. I often see that most of the organizations follow many of the first 6 points, but the last 7th point is very often implemented in many organizations.
I have seen when disaster happens, DBA team trying to restore their database to production just to realize that there is some error in the backup operation. When they realize this error it is often too late.
It is always better to proactive about your database backup strategy. I recommend everyone to read following article which discusses backup timeline.
What is a Backup Timeline for Restore Process? – Interview Question of the Week #092
Reference: Pinal Dave (https://blog.sqlauthority.com)
