Page 145 - DCAP508_DATABASE_ADMINISTRATION

Unit 10: Database Backup and Restore

Notes
Figure 10.5: Backup Progress Dialog Box

Upon successful completion of the backup, the informational dialog box shown in Figure 10.6 is
displayed.
Figure 10.6: Backup Complete Confirmation

The above functionality can be accomplished through Transact-SQL commands executed from
SQL Query Analyzer. An example of such a command is illustrated below.
BACKUP DATABASE northwind
TO DISK = ‘d:\backups\northwind\nwind.bak’

10.3 Restoring Database

SQL Server supports restoring data on the following levels:

The database (a complete database restore): The whole database is restored and recovered,
and the database is offline for the duration of the restore and recovery operations.
The data file (a file restore): A data file or a set of files is restored and recovered. During a
file restore, the filegroups that contain the files are automatically offline for the duration
of the restore. Any attempt to access an offline filegroup causes an error.

The data page (a page restore): Under the full recovery model or bulk-logged recovery
model, you can restore individual databases. Page restores can be performed on any
database, regardless of the number of filegroups.

A complete database backup may be restored to the same or a new or different database on the
same (or a different) server. The restore operation may be initiated either through either SQL
Server Enterprise Manager or the Transact-SQL command window.
It is highly advisable to restore complete database backups at regular intervals, as this is the
only means that is currently available to verify the “restorability” of an SQL Server backup.

LOVELY PROFESSIONAL UNIVERSITY 139