Page 141 - DCAP508_DATABASE_ADMINISTRATION

Unit 10: Database Backup and Restore

Self Assessment Notes

Name the following:
1. A backup contains all the data in a specific database or set of filegroups or files, and also
enough log to allow for recovering that data.

2. A backup that is based on the latest full backup of the data and is a full backup of read/
write data.
3. A backup of all the full data in the primary filegroup, every read/write filegroup, and any
optionally specified read-only files or filegroups.
4. A backup that contains only the data extents that were modified since the most recent
partial backup of the same set of filegroups.
5. A full backup of all the data in one or more files, or filegroups.

10.1.2 Backup Media

SQL Server databases may be backed up to either a disk or tape media. Backup may be performed
through SQL Server Enterprise Manager or a Transact-SQL command.
1. Disk Backups: A database may be backed up to disk file or a disk backup device. Any
database can be backed up to a random disk file at any time. The file may either be
initialized or the backup may be appended to an existing backup file.
2. Tape Backups: A database may be backed up to a local tape drive. SQL Server formats the
tape backups using Microsoft Tape Format (MTF). This means that a tape may hold other
backups formatted using MTF in conjunction with SQL Server backups. Tape backups
provide certain features that are not available when using disk backups.
3. Continuation Media: If the tape to which the backup is being written fills up, SQL Server
Enterprise Manager pops a dialog box and prompts for the next tape (if using the Transact-
SQL command, a message is logged to the SQL Server error log to mount the next tape and
a retry attempt is made roughly every five minutes to see if a new tape was mounted). This
is in contrast to disk backups, where inadequate disk space terminates the backup operation.
4. Restart Option: If there is a power failure or the server shuts down unexpectedly while
the backup/restore is being performed, the operation may be restarted from the point at
which it was interrupted.

10.1.3 Permissions Required for Backup and Restore

Any logon that requires permissions to perform backup or restore operations should be provided
membership in the following SQL Server roles:
Server Role : sysadmin
DB role : db_backupoperator, dbo_owner

Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner

LOVELY PROFESSIONAL UNIVERSITY 135