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:

[sourcecode language=”sql”]
CREATE DATABASE MyDbSnap ON
(NAME = MyDb, FILENAME = ‘c:\MyDb.ss’)
AS SNAPSHOT OF MyDb
[/sourcecode]
And if we do need to back out, we can run the script below:

[sourcecode language=”sql”]
RESTORE DATABASE MyDb FROM DATABASE_SNAPSHOT = ‘MyDb’
[/sourcecode]
Obviously database snapshot is not a replacement for regular database backups, but it can be useful in cases like these.