Page 158 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes AdventureWorks2008R2_snapshot_0600
AdventureWorks2008R2_snapshot_1200
AdventureWorks2008R2_snapshot_1800
Alternatively, if the creation time of these daily snapshots varies from day to day, a less precise
naming convention might be preferable, for example:
AdventureWorks2008R2_snapshot_morning
AdventureWorks2008R2_snapshot_noon

AdventureWorks2008R2_snapshot_evening

Limiting the Number of Database Snapshots

Creating a series of snapshots over time captures sequential snapshots of the source database.
Each snapshot persists until it is explicitly dropped. Because each snapshot will continue to grow
as original pages are updated, you may want to conserve disk space by deleting an older
snapshot after creating a new snapshot.

Notes If you want to revert to a database snapshot, you need to delete any other snapshots
from that database.

Client Connections to a Database Snapshot

To use a database snapshot, clients need to know where to find it. Users can read from one
database snapshot while another is being created or deleted. However, when you substitute a
new snapshot for an existing one, you need to redirect clients to the new snapshot. Users can
manually connect to a database snapshot by means of SQL Server Management Studio. However,
to support a production environment, you should create a programmatic solution that
transparently directs report-writing clients to the latest database snapshot of the database.

Did u know? SQL Server Management Studio does not support the creation of database
snapshots.

10.5 Summary

A copy of data that can be used to restore and recover the data is called a backup. Backups
let you restore data after a failure. With good backups, you can recover from many failures.
The scope of a backup of data (a data backup) can be a whole database, a partial database,
or a set of files or filegroups. For each of these, SQL Server supports full and differential
backups
Database backups are easy to use and are recommended whenever database size allows.
SQL Server supports the following types of database backups such as full backups, partial
backup, file backup etc.

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.

152 LOVELY PROFESSIONAL UNIVERSITY