SQL backup strategy recommendations

When you develop a SQL backup strategy, consider the following:

Table: Recommendations for backing up SQL

SQL Server backup strategies

Description

Protect the entire SQL Server

To make sure SQL is completely protected, back up the following on a regular basis:

  • The system drive that SQL is on.

  • The Windows registry and System State.

  • SQL databases or filegroups. You do not need to back up both.

  • Transaction logs.

When you upgrade, run new full database backups.

If you upgrade SQL, run new full database backups. You may not be able to restore backups from one version or service pack level of SQL to other versions.

Run consistency checks after backups.

Symantec recommends that you run a consistency check after a backup. If a database, transaction log, or filegroup contains errors when it is backed up, the backup will still contain the errors when it is restored, if it is restorable at all.

These consistency checks include the following:

  • A full consistency check, including indexes. This check will have significant impacts on SQL performance; therefore, it should be performed in off-peak hours.

  • A full consistency check with no index check. While not as thorough as a full consistency check that includes indexes, this check is faster and can be done during peak hours with little impact on system performance.

  • A physical check only. Another low-overhead check, this method checks only the integrity of the physical structure of the page and record headers, and the consistency between the pages’ object ID and index ID and the allocation structures.

Back up the master database whenever data is changed in the master database.

Back up the master database whenever procedures are run that change information in the database, especially after the following:

  • New databases are created.

  • Files are added to an existing database.

  • Usernames or passwords are added or changed.

If changes are not backed up before the master database must be restored, the changes are lost.

Run one backup at a time.

Do not schedule more than one backup to occur simultaneously against a database or its transaction log, or a filegroup.

Back up both system and user databases and transaction logs regularly.

Copies of the master and model databases are automatically created by Backup Exec whenever you back up the master and model databases. If these databases become corrupted or are missing, and SQL cannot be started, you can replace them with the copies of the master and model databases, and then start SQL. After SQL is running again, you can restore the latest copy of the master database using Backup Exec’s Automate master database restore option, and then restore any other databases, if needed.

If you use the Intelligent Disaster Recovery (IDR) option, then during an IDR recovery of the C: drive, it will automatically replace the damaged databases with the copies of the master and model databases that you made.

If you have filegroups, back them up instead of databases. Do not back up filegroups and databases.

When databases grow too large to be backed up all at once, filegroups can provide an alternative backup method. Different filegroups can be backed up at different times and frequencies. A combination of filegroup and log backups provides complete database protection.

SQL backup strategy recommendations