Microsoft introduced database snapshot in Sql Server 2005. It is used to keep a point-in-time snapshot of the database state at the time the snapshot was taken. It is also good for reporting purposes when database mirroring is implemented, because while a mirroring partner database is not accessible, one can produce a snapshot of that mirror. And the snapshot can be queried. For example, for a time insensitive report, one can point to the snapshot created from the mirror, thus decrease the load on the principal.
Another potential useful case is during change control. For mission-critical applications, new changes to databases are done during off-peak hours, maintenance window, and/or weekends. It is also important to be able to back out those changes when necessary.
I think database snapshot can be great for the case listed above. Right before the change, a snapshot can be taken, like so:
CREATE DATABASE MyDbSnap ON
(NAME = MyDb, FILENAME = ‘c:\MyDb.ss’)
AS SNAPSHOT OF MyDb
And if we do need to back out, we can run the script below:
RESTORE DATABASE MyDb FROM DATABASE_SNAPSHOT = ‘MyDb’
Obviously database snapshot is not a replacement for regular database backups, but it can be useful in cases like these.