X hits on this document

758 views

0 shares

0 downloads

0 comments

50 / 200

50

Backup and recovery concepts Overview

SQL Server system database types

A SQL Server instance cannot be backed up as a single entity. The largest granularity of a SQL Server backup is the database. SQL Server has system and user databases. The system databases are:

Master

This is the “brains” of your installation, containing a great deal of the metadata describing your instance. Be sure to retain an up-to-date backup of the master database. In particular, back up the master any time you have made changes to your SQL Server installation, including when you have created or modified other databases. Note that you can only do full database backups on the master. You cannot back up its component files, perform differentials, or backup up its transaction log. Recovery of the master database requires special considerations.

See “Disaster recovery” on page 119.

msdb

The msdb is used by the Microsoft SQL Agent for scheduling, alerts, and for recording the backup history. It is amenable to all types of backup providing that it has the full or bulk-load recovery option set.

Model

The model database serves as a template for new databases when the Create Database statement is executed. It is also amenable to all types of backup, providing that it has the full or bulk-load recovery option set.

tempdb

The tempdb is for temporary databases used by applications. It cannot be backed up and does not appear in the NetBackup for SQL Server backup browser.

Database backups

Three types of backup operations can be performed on databases:

Full

The database, including all of its component files are backed up as a single image. The log file is included in a full database backup.

Note: The transaction log is not automatically truncated following a full backup. Thus a common practice to preserve disk space is to manually truncate the transaction log following a successful full backup.

Differential All of the changes since the last full are backed up to a single image.

Document info
Document views758
Page views758
Page last viewedTue Jan 24 11:59:53 UTC 2017
Pages200
Paragraphs4189
Words49499

Comments