This subsection introduces the different types of backups that can be performed for the SQL Server system. Both the Database and Windows backups described here, should be part of your backup strategy.
A SQL Server database is always comprised of data and log files. The data files contain the actual data whereas the log files contain information about all changes made to the database. The log files are necessary components of the database and may never be deleted.
There are three main types of SQL Server Backup: Full Database Backup, Differential Database Backup and Transaction Log Backup. All these backups can be made when the database is online and do not require you to stop the SAP system.
· Full database Backup
A full database backup contains all used data pages and the log files written during the backup. The transaction log is not truncated when you perform a full database backup.
Full database backups may result in a significant amount of disk I/O and should therefore be performed at a time when the workload is low.
You always need a full database backup to restore the database. You cannot restore a differential database backup or a transaction log backup without a full database backup. Typically a full database backup is performed once a day.
· Differential Database Backup
A differential database backup contains only those data pages that have been modified since the last full database backup. Apart from this, a differential database backup is identical to a full database backup.
The size of a differential backup is much smaller than the size of a full database backup. An example of integrating differential backups in a backup strategy is to perform a full database backup only once a week and to perform differential database backups daily.
Differential database backups are optional. The backup strategy will become more complicated if it includes differential database backups.
Advantage of Differential Backups
Performing differential backups after a full database backup has advantages.
It can reduce the number of full database backups that have to be performed. For example, a schedule requiring a daily database backup could be replaced by a schedule requiring 1 database backup at the beginning of the week followed by 6 differential backups on other days of the week.
Differential backups can also be used as a means of reducing the time necessary for a restore operation. By performing additional differential backups between full database backups, the number of transaction logs that have to be applied in the event of a restore operation is reduced. Restoring data with a database backup is considerably faster than restoring data by applying logs.
· Transaction Log Backup
Transaction log backups save the log files.
They are mandatory when you use the Full or Bulk-Logged Recovery Model since they are needed to truncate the log. Two subsequent transaction log backups do not contain redundant information.
Transaction log backups have almost no impact on performance and can therefore be performed when the workload is high.
· Log Shipping
With Microsoft SQL Server 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database.
You can use the Database Maintenance Plan Wizard to configure log shipping as a maintenance task that can be scheduled to run automatically at defined intervals.
Log shipping enables you to run a backup server and provides high availability as required by large enterprise systems. Log shipping requires extra administration and well-defined procedures to monitor the status of all log-shipping servers.
For details refer to SQL Server Books Online and SAP Note 493290.
Do not use log shipping to replace a robust and thoroughly validated disaster recovery plan. It merely provides a high availability solution for large enterprise systems.
A full Windows backup backs up the entire system including the database, SQL Server and Windows files. A Windows backup is only possible when the SQL Server is stopped because open files cannot be backed up.