636
Part V: Enterprise Data Management
■ (^) You can create database snapshots only on the same SQL Server instance where the
source database exists.
■ (^) Database snapshots depend on the source database, so if the source database is
unavailable for any reason, all its database snapshots also become unavailable.
■ (^) The source database cannot be dropped, detached, or restored as long as it has
any database snapshots. But source database backups are not affected by database
snapshots.
■ You cannot backup or restore a database snapshot. Nor can you attach or detach a
database snapshot.
■ You cannot revert to the database snapshot if you have multiple database
snapshots. You need to drop all the database snapshots except the one that you
want to revert to.
■ (^) Database snapshots do not support FILESTREAM.
■ Full-text indexing is not supported on database snapshots and full-text catalogs are
not propagated from the source database.
■ In a log shipping configuration, database snapshots are allowed only on the
primary database and not on the secondary or warm-standby database.
■ Database snapshots are IO-intensive and may impact the performance of the
system.
If these limitations are acceptable, the Database Snapshot feature can be an excellent way
to create point-in-time, read-only copies of your production databases.
What’s New with Database Snapshots in SQL 2012?
The new AlwaysOn feature in SQL 2012 supports database snapshots on the primary or secondary
database in an availability group. For more information on database snapshots in availability groups
refer to http://technet.microsoft.com/en-us/library/hh213414.aspx.
How Do Database Snapshots Work?
As discussed earlier, a database snapshot is a point-in-time, read-only, static view of the
source database as it existed at the time of the database snapshot creation. Understanding
the mechanics and technology behind database snapshots can help you understand how it
all works.Mechanics of Snapshots
When you create a database snapshot, SQL Server runs recovery on the database snapshot
and rolls back uncommitted transactions to make the database snapshot transactionally
consistent. The transactions in the source database are not affected.c24.indd 636c24.indd 636 7/31/2012 9:25:48 AM7/31/2012 9:25:48 AM
http://www.it-ebooks.info