Depending on your SQL Server environment, you can run full backups only, copy-only full backups, full plus log backups, full plus differential backups, or file and group backups. Your backup strategy might also be to back up data to Tivoli® Storage Manager or local shadow volumes.
A full backup strategy is best for SQL Servers that are relatively small because the entire database is backed up each time.
This strategy is the appropriate strategy for system databases such as master, model, and msdb because of their typical small size. Each backup takes longer to run. However, the restore process is the most efficient because only the most recent (or other appropriate) full backup is restored.
A copy-only full backup contains a copy-only version of a full backup. Such backups are separate to the regular sequence of conventional SQL Server backups.
Copy-only full backups do not affect the transaction logs or the sequence of backups, such as differential backups or full backups. You might use this strategy to periodically create copy-only full backups for long-term retention without affecting existing backup schedules or retention policies for disaster recovery.
Full backup plus log backups is a commonly used strategy when the normal backup schedule or network capacity cannot support a full backup each time.
To minimize the impact on the backup schedule and network traffic during peak usage times, you can implement a periodic full backup followed by a series of log backups. For example, you can schedule full backups on the weekend and log backups during the week. You can implement full backups during low usage times and when increased network traffic can be tolerated. You can complete a point-in-time restore operation to restore a transaction log to a specified date and time.
The restore process becomes more complex, however, because a full backup, and subsequent log backups, must be restored. In addition, transactions within the logs must be applied which increases processing time. The recovery process takes longer as more transactions are applied.
You can apply legacy log backups after a full VSS backup is restored. You must leave the database in a recovering state by specifying /recovery=no on the command-line interface. Alternatively, ensure that the Recovery option in the GUI Restore Databases or Restore Groups/Files is not selected when you restore the VSS backup.
A full plug differential backup strategy can be used between full backups. A differential database backup is cumulative and can save both time and space.
Space is saved because the backup consists of only the changed portions of a database since the last full backup. Time is saved because you can avoid applying all individual log backups to the operation within that time. The space and time saving applies to restore operations as well; only the last differential backup of the latest version must be restored.
Although VSS supports full backups only, you can apply legacy differential backups to the VSS full backup. You must leave the database in a recovering state by specifying /recovery=no on the command-line interface. Alternatively, ensure that the Recovery option in the GUI Restore Databases or Restore Groups/Files is not selected when you restore the VSS backup.
A full plus differential plus log backup strategy reduces the number of transactions that must be restored and applied. Restore operations are faster as a result.
If, for example, you complete a full legacy or VSS backup weekly, and a differential backup nightly, and a log backup every four hours, the restore processing would include the full backup, a differential backup, and at most five log backups. However, if you only complete a full plus log backup scheme on the same cycle, the restore processing would include a full plus up to 41 log backups (six days multiplied by six log backups per day plus up to five backups on the day the full backup is completed). Although VSS supports full backups only, legacy log backups and legacy differential backups can be applied to the VSS full backup
Use a file backup strategy when it is impractical to back up an entire database because of size and associated time and performance issues.
When a group is created on the SQL Server, database files are identified with that group. The group that is used for the group backup depends on the group to which the database files are defined.
File or group options can save backup and restore processing time when certain tables or indexes have more updates than others and must be backed up more often. It is time-effective to contain such data in their own filegroup or files and to back up only those items.
The PRIMARY filegroup must be restored before a user-defined filegroup is restored. To ensure that you are able to restore the PRIMARY filegroup backup, create a full backup or a group backup of the PRIMARY filegroup before you create the user-defined backup. Consult your Microsoft SQL Server documentation for more details on SQL Server backup strategy and planning.
Except for logical log files, you can back up your transaction logs after you back up a data file or file group.
When you determine policy for your backups, consider the following differences between backing up data to Tivoli Storage Manager storage versus VSS disks.
A Tivoli Storage Manager backup operation stores the backed up data on Tivoli Storage Manager server storage. Although this type of backup typically takes longer to process than a backup to local shadow volumes, a Tivoli Storage Manager backup is necessary when long-term storage is needed.
Saving Exchange Server or SQL Server data on tape for archival purposes is an example of needing long-term storage. Tivoli Storage Manager backups are also necessary for disaster recovery situations when the disks that are used for local backups are unavailable. By maintaining multiple backup copies on Tivoli Storage Manager server storage, a point-in-time copy is available if backups on the local shadow volumes become corrupted or deleted.
Backups to Tivoli Storage Manager server storage are dictated by time, not by versions.
Sufficient local storage space must be available on local shadow volumes for a VSS backup strategy to be successful. To accommodate your backup operations, ensure that enough available storage space is assigned to the volumes.
Environment and storage resources also impact how many backup versions are maintained on local shadow volumes (for VSS fast restore and VSS instant restore) and how many backup versions are maintained on Tivoli Storage Manager server (VSS restore and longer term storage).
Create different sets of policies for backups to both local shadow volumes and to Tivoli Storage Manager server storage. If you use a VSS provider other than the Windows VSS System Provider, make sure to review the documentation for that specific VSS provider.
You can manage backups to local shadow volumes by time and versions. However, because local snapshots are created more frequently, and VSS storage provisioning and space limitations apply, it is more effective to base policy for local backups to be on version limits. In addition, in Database Availability Group (DAG) environments, all of the DAG members must use the same local VSS policy.
Commonly used backup strategies are as follows: