Jaa

Limitations on Database Snapshots

  • Artikkeli

A database snapshot captures the point in time at which snapshot creation began, minus any uncommitted transactions. Before using database snapshots, you should understand the impact of database snapshots on the source database and the system environment, as well as limitations on the snapshots themselves.

Important

Database snapshots are available only in Microsoft SQL Server 2005 Enterprise Edition.

Limitations on the Source Database

As long as a database snapshot exists, the following limitations exist on the snapshot's source database:

  • The database cannot be dropped, detached, or restored. For information on how to delete a snapshot, see Dropping a Database Snapshot.

    Note

    Backing up the source database works normally; it is unaffected by database snapshots.

  • Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

  • Files cannot be dropped from the source database or from any snapshots.

  • The source database must be online, unless the database is a mirror database within a database mirroring session.

    Note

    All recovery models support database snapshots.

  • The source database cannot be configured as a scalable shared database.

  • To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.

Limitations on Database Snapshots

The following limitations apply to database snapshots:

  • A database snapshot must be created and remain on the same server instance as the source database.
  • Database snapshots always work on an entire database.
  • Because database snapshots are not redundant storage, they do not protect against disk errors or other types of corruption. Taking regular backups and testing your restore plan are necessary to protect a database. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.
  • When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.
  • Snapshots are read-only.
  • Snapshots of the model, master, and tempdb databases are prohibited.
  • You cannot change any of the specifications of the database snapshot files.
  • You cannot drop files from a database snapshot.
  • You cannot back up or restore database snapshots.
  • You cannot attach or detach database snapshots.
  • You cannot create database snapshots on FAT32 file system or RAW partitions. The sparse files used by database snapshots are provided by the NTFS file system.
  • Full-text indexing is not supported on database snapshots. Full-text catalogs are not propagated from the source database.
  • A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.
  • A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, and offline filegroups remain offline. For more information, see "Database Snapshots with Offline Filegroups" later in this topic.
  • If a source database becomes RECOVERY_PENDING, its database snapshots may become inaccessible. After the issue on the source database is resolved, however, its snapshots should become available again.
  • Reverting is unsupported for read-only filegroups and for compressed filegroups. Attempts to revert a database containing either of these types of filegroups fail. For more information on reverting, see Reverting to a Database Snapshot.
  • In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.
  • A snapshot cannot be configured as a scalable shared database.

Disk Space Requirements

Database snapshots consume disk space. If a database snapshot runs out of disk space, it is marked as suspect and must be dropped. (The source database, however, is not affected; actions on it continue normally.) Compared to a full copy of a database, however, snapshots are highly space efficient. A snapshot requires only enough storage for the pages that change during its lifetime. Generally, snapshots are kept for a limited time, so their size is not a major concern.

The longer you keep a snapshot, however, the more likely it is to use up available space. The maximum size to which a sparse file can grow is the size of the corresponding source database file at the time of the snapshot creation. For more information, see Understanding Sparse File Sizes in Database Snapshots.

If a database snapshot runs out of disk space, it must be deleted (dropped). For information on dropping a database snapshot, Dropping a Database Snapshot.

Note

Except for file space, a database snapshot consumes roughly as many resources as a database.

Database Snapshots with Offline Filegroups

Offline filegroups in the source database affect database snapshots when you try to do any of the following:

  • Create a snapshot
    When a source database has one or more offline filegroups, snapshot creation succeeds with the filegroups offline. Sparse files are not created for the offline filegroups.

    Note

    For information on the role of sparse files for database snapshots, see How Database Snapshots Work.

  • Take a filegroup offline
    You can take a file offline in the source database. However, the filegroup remains online in database snapshots if it was online when the snapshot was created. If the queried data has changed since snapshot creation, the original data page will be accessible in the snapshot. However, queries that use the snapshot to access unmodified data in the filegroup are likely to fail with input/output (I/O) errors.

  • Bring a filegroup online
    You cannot bring a filegroup online in a database that has any database snapshots. If a filegroup is offline at the time of snapshot creation or is taken offline while a database snapshot exists, the filegroup remains offline. This is because bringing a file back online involves restoring it, which is not possible if a database snapshot exists on the database.

  • Revert the source database to the snapshot
    Reverting a source database to a database snapshot requires that all of the filegroups are online except for filegroups that were offline when the snapshot was created.

See Also

Concepts

Creating a Database Snapshot
Database Snapshots
Dropping a Database Snapshot

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Updated the "Limitations on Database Snapshots" section to state that because database snapshot are dependant on the source database, so performing all your scheduled backups remains essential.

14 April 2006
New content:
  • Added that database snapshot are not supported on a scalable shared database.
  • Added that to create a database snapshot on a mirror database, the database must be in the Synchronized mirroring state.

5 December 2005
New content:
  • Added limitation about using database snapshots with log shipping.
  • Added a note about resource consumption.
Changed content:
  • Corrected technical error to clarify that you cannot bring a filegroup online when a database snapshot exists.