System Restore Point: In Microsoft Window Operating
system , user can create a restore point just point in time. That feature allow
user to revert to the status of computer system at point in time restore point
created w.r.t system files, application installed, window registry and
If a developer/dba requires such thing in term of sql
server database, Then Database Snapshot.
What is it?:
Database snapshot is static
and read only view of a database. A Database can have multiple snapshots.
Database snapshot can be created on any drive partition. The Database snapshot cannot
take size on disc as the Database size.
Snapshot can be used for reporting purposed. In case of
user error on source database, revert the source database can be performed
using snapshot in state when snapshot was created.
Reason why to take Database
Data: To generate reports based on historical data.
As it record data at point in time , it is created. Suppose you require
reports quarterly, monthly basis , you can create database snapshots
quarterly , monthly basis and can query to generate report on that
database snapshots instead on real database. i.e to investigate a
Database: Using Database snapshots with database mirroring
permits to make the data on mirror server accessible for reporting.
data against administrative error: Before doing bulk
update, create a database snapshot on database. In case of any mistake done,
you can use snapshot to recover by reverting the database to the snapshot.
Example: Once one of my team members was performing a update on
production database server, he forgot out where clause in update command
that causes the whole day goes waste in reverting data using various
select operation. In that time, we didn't have any database snapshot then
we can revert the database and with minimal action we should be running
db...We can also recover a single database object from snapshot.
data against user error:Accidental attract on
database such as drop table.
test database: Create a Database
snapshot and perform testing of application. After successful testing do
revert database, no test data will remain in database. Mostly we don't
care for that case as well.
Query to generate Database Snapshot.
You should require enterprise Edition of Sql Server.As Soon as query execute the Database name MYBlog_04042015
will appear under Database Snapshots with its all object.
File size of the Database Snapshot be very small as comparable to the Source Database.
Dropping Database Snapshot:
Any user with DROP database permission can perform
drop a database snapshot. Command is same as dropping datatabase
Drop Database [DatabaseSnapshotName]
Drop Database MYBlog_04042015
Dropping a database
snapshot deletes the database snapshot from SQL Server and deletes the sparse
files that are used by the snapshot. When you drop a database snapshot, all
user connections to it are terminated
Limitation on Source Database:
As log as a database snapshot exists, the following
limitations exist on the snapshot's source database:
database cannot be dropped, detached, or restored. Backup of database work
normally. There is not imparting on backup of a database.
Source Database must be online
gets reduced due to increased I/O on the source db resulting from a copy
on write protection. Every time a page is updated.
create a database snapshot on a mirror database, the database must be in
the synchronized mirroring state.
Limitation on Snapshot Database:
The following limitations apply to database
A database snapshot must be created and remain on
the same server instance as the source database.
Snapshots are read-only.
Snapshots of the model, master,
and tempdb databases are prohibited.
You cannot drop files from a database snapshot.
You cannot back up or restore database snapshots.
You cannot attach or detach database snapshots.
Database snapshots always work on an entire
You cannot change any of the specifications of the
database snapshot files.
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
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
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
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
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
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
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
Conclusion: Database Snapshots is a very good way for safeguard against administrative error , user/developer error.You can use the Database snapshot in development environment as well to track daily object changes.