Limitations and Requirements of Database Snapshots
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 MicrosoftSQL Server 2005 Enterprise Edition and later versions.
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.
Database snapshots are dependent on the source database. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. 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 database snapshot cannot be configured as a scalable shared database.
FILESTREAM filegroups are not supported by database snapshots. If FILESTREAM filegroups exist in a source database, they are marked as offline in its database snapshots, and the database snapshots cannot be used for reverting the database.
Note
A SELECT statement that is executed on a database snapshot must not specify a FILESTREAM column; otherwise, the following error message will be returned: Could not continue scan with NOLOCK due to data movement.
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
Other Resources
Change History
Updated content |
|---|
Documented that FILESTREAM filegroups are not supported by database snapshots (in the "Limitations on Database Snapshots" section). |