Andrew Fraser DBA

5 Backup

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

Backup can be done through third party backup agents for SQL Server. But the preferred backup methodology is to use SQL Server’s own backup utility. This will dump backup files to disk, which will then be backed up by the filesystem backup to tape. The option to delete old backups should be ticked. Backups should be kept for e.g. 4 weeks for small databases, with just a single backup kept on disk for large databases. The disk backup files can still be retrieved as operating system files from tape backup if required.

Backups can be written direct from SQL Server to

• disk file
• tape
• pipe

Most commonly SQL Server uses backups to disk file.

All SQL Server backups are online (hot). While a backup is in progress, it is impossible to:

• create or modify databases;
• autogrow files;
• create indexes;
• perform non-logged operations.

There are three kinds of SQL Server backup:

1. Full backup – backs up data files and the part of the transaction log that took place during the full backup. Equivalent to a full online backup in oracle.
2. Differential backup – backs up the extents which have been modified since the last full backup, and the part of the transaction log that took place during the differential backup.
3. Transaction log backup with “truncate the transaction log” option – backs up and then truncates the transaction log. Equivalent to a log switch in an oracle instance in archivelog mode.
4. Transaction log backup with “backup the tail of the log” option – backs up the transaction log without truncating. This would be run immediately before attempting a restore in order to keep a copy of the current transaction log.

Database maintenance plans are used to perform backups (see section 10 below), except for one-off ad-hoc backups. Common practice for small databases would be for a full backup followed by a transaction log backup is scheduled weekly, with a transaction log backup only every other night. More critical or high performance databases might have transaction log backups scheduled continually throughout the day every 30 minutes or so – that might seem strange because of the ‘backup’ terminology, but actually transaction log backups are just the equivalent of oracle log switches.

There is a “copy only” backup option – this is a normal backup, except it does not affect backup catalog for differential or transaction log backups. That means it cannot serve as a base for restoring differential or transaction log backups either. It is useful when taking one-off backups for cloning databases.

Options on backup include “verify backup when finished”, compression with Enterprise Edition, and the “backup the tail of the log” (notruncate) option for transaction log backups.

Lab 5 – run a one-off backup.

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)