SQL Server Backup Best Practices

The absolute worst time to find out that your recovery plans don’t work is right in the middle of a critical system restore. Follow these SQL Server backup best practices to ensure that you really can restore your system when (not if) it goes down.

Perform Full Backups Daily

A full database backup is the foundation for every DBA’s data protection plan and in most cases should be performed daily. SQL Server supports online backups, allowing end users and SQL Server jobs to be active while the backup operation occurs. Even so, large databases can take a long time to back up. Strategies for reducing the backup window include backing up to disk and utilizing backup data compression.

Perform Frequent Transaction Log Backups

Next most important is to back up the transaction log, which contains all of the recent activity in the database and can be used to restore a database to a given point in time. Backing up the transaction log also truncates it, keeping it from becoming full. Like database backups, transaction log backups can occur while the system is active. Organizations with active databases might back up the transaction log every 10 minutes while those with less active databases might need to back up the transaction log only every half hour or every hour.

Regularly Back Up System Databases

Your backup strategy is incomplete without a plan to back up SQL Server system databases (master, model, msdb). These databases contain system configuration information as well as SQL Server job information that needs to be restored as part of a total system restore. Back up system databases daily for frequently changing instances, weekly for more stable installations.

Back Up the Host OS Daily

SQL Server runs on top of the OS and an event such as a hardware failure could require a complete system restore, beginning with the OS. Therefore, daily backups of the host OS are a good idea. At a minimum, back up the host system partition following any system updates or configuration changes.

Practice Recovery Operations

Changing business requirements can affect your plans, quickly making backup strategies obsolete. Test your strategies regularly in different scenarios, including both system and individual database restores, to ensure your backup plans really work when you need them.