Backup strategies for SQL

Last published : Apr 02, 2026
Backup Exec incorporates online, nondisruptive SQL database protection as part of everyday backup routines, which increases the chance of data recovery and minimizes data loss without inhibiting daily database activity. Using database, differential, and log backups provides a good balance between backup windows and minimizes the amount of time that will be spent recovering a database if the need arises.
To decide which backup methods to use for the best data protection, consider the following for typical environments:
  • In small environments, consider running a daily full database backup every evening and daily transaction log backups.
  • In mid-sized environments, consider running a weekly full database backup and daily transaction log backups along with daily differential backups except on the day when the full backup is run.
  • In large environments, consider running daily differential database backups, weekly full database backups, and transaction log backups as necessary. Many shops run full backups on a weekly basis, preferring to run differential backups throughout the week to keep backup run time to a minimum.
The trade-off with running fewer full backups and running more differential backups occurs at recovery time when you must recover using the full database backup as well as the last differential database backup, and all log backups made after the last differential database backup.
What will work best for you will be based on the size of your environment, the number of transactions processed each day, and the expectations of your users when a recovery is required.
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 ensure 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.
- 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 before backups. Veritas recommends that you run a consistency check before a backup. If a database or transaction log contains errors when it is backed up, the backup will still contain the errors when it is restored, if it is restorable at all.
See Configuring Backup Exec to run a consistency check before every SQL backup .
Back up your system databases regularly. Back up the master database and service packs that are installed 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.
Back up transaction logs on databases that are configured for the full recovery model. Back up the transaction logs on databases because the transaction logs continue to grow if you do not back them up.