Backup strategies for SQL

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. Extremely large environments may need to run filegroup backups in order to split the full backup over several days. Log backups are required to be able to recover a system from a filegroup backup.

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 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.

We recommend 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.

Note:

The option to run a physical check only is not supported under SQL 7.0.

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.

For SQL 7.0 only: Create a copy of the master and model databases, and place the copies in the same directory that the databases are in.

Make copies of the master and model databases and place the copies in the same directory that the master and model databases are in. Then, if the master database becomes critically damaged and SQL cannot be started, instead of running the Rebuild Master utility or reinstalling SQL, you can replace the corrupted or missing databases 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 drive C, it will automatically replace the damaged databases with the copies of the master and model databases that you made.

See “To create copies of the SQL 7.0 master and model databases”.

For SQL 7.0 only: If you have filegroups, run at least one full database backup first, then back up the filegroups 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.

However, SQL 7.0 databases cannot be restored using only filegroup and log backups. SQL 7.0 databases must have at least one full database backup in order to be restored. If the database has been deleted or does not exist, restore the full database backup, and then restore all the filegroup and log backups.

Backup strategies for SQL

Объявления: