Microsoft® SQL Server® 2012 Bible

637

Chapter 24: Database Snapshots

24

A database snapshot is not the same as the source database. It is a different database and

has the same number of data files as the source database, but it does not have any trans-

action log file. When it is initially created, the data files in the snapshot database do not

contain any user data and are almost empty. That is why creating a database snapshot does

not take a long time.

Copy on First Write

Database snapshots use a copy-on-first-write method for each source database page updated

for the first time after you create the database snapshot. For every database snapshot, SQL

Server creates an in-memory bitmap. It has a bit for each data page indicating if the page is

copied to the snapshot.

Every time you make an update to the source database, SQL Server checks the bitmap to

see if it has been copied to the snapshot. If it is not copied, SQL Server copies the data page

from the source database to the database snapshot and then makes the update. Next time if

the same page is updated, it is not copied because the database snapshot just contains the

data as it existed on the source database when you created the snapshot.

This is referred to as copy-on-first-write technology, as shown in Figure 24-1. If a data page

on the source database is never updated, it is never copied to the database snapshot.

FIGURE 24-1

Database snapshot using copy-on-fi rst-write Technology.

Update Source Database

Data Page Data Page

Database Snapshot

Data Page at the time the

snapshot was created

Updated Data Page

Unallocated Page

Using Database Snapshots

You can create, query, or drop database snapshots just as with regular databases. The main

difference is that you can create them only from an existing database as a point-in-time,

c24.indd 637c24.indd 637 7/31/2012 9:25:48 AM7/31/2012 9:25:48 AM

http://www.it-ebooks.info