SQL Server backup

Explain the backup devices offered by SQL Server 2000. [Hint Disk devices, Tape Devices, Named pipe devices]

Disk devices: These are files that are stored on the hard disk or some other storage media. They can be of a variable size. The maximum size of the file can be as big as the disk space available. If the backup is performed on a disk remotely located, it must follow the naming convention \\Servername\Sharename\Path\File. It is always advisable to not back up a file on the same physical disk because if the disk fails, there is no way to recover the file.

Tape Devices: A tape device is functionally similar to Disk device except that it needs to be physically connected to the server and cannot be remote. If the tape drive becomes full, it prompts for a new tape and continues the backup.

Named Pipe Devices: Named pipe devices make use of some third party software for backups. They are typically used for remote tape storages.

SQL Server backup

Database backup methods

Full Backups

Full backups, as the name suggests is a back up of the files and folders. Frequent full backups results in a faster restore. However, full backups are slow. The storage space requirement is also high. They are the most comprehensive type of backups and can also be scheduled for backing up the selected files and folders on a periodic basis.

Differential Backups

Differential backup backups up files that have been changed since the last backup. This reduces the time to backup since only changed files need to be restored. Restoring differential back up is faster and storage requirements are less. The list of files changed since the last back up is recorded in a catalogue file with .bkc extension.

Transaction Log Architecture Backups

Transaction log records all transactions and the database modifications made by each transaction. Transaction log helps in:

• Recovering individual transaction in case of failures
• Helps in recovering incomplete transactions if a running SQL server fails.
• It helps to restore the database to the point where it failed.

The transaction log is a separate file or set of files. The transaction log cache is managed separately from the data pages making it robust.

File and Filegroup Backups

File or Filegroup backups support backing up and restoring individual files or filegroups. This is usually used in very large database where there may not be a lot of time to backup the complete database. File and file groups can be restored from a full database backup set. This allows a quicker recovery because only the file and the file groups that are damaged are restored first.