Blog Archives

Database Snapshots, A Polaroid Into Your Soul

Database snapshots were introduced with SQL Server 2005 and are only available with Enterprise and Developer edition.  Disclaimer is out-of-the-way, now onto the good stuff!

You can use snapshots to provide a snapshot into the soul of your data at a particular point in time.  The snapshot is read-0nly, of course and is great for reporting or auditing purposes.  Snapshots are much quicker than the previous method of restoring a backup to another database and setting it to read-only access.  There is also some considerable space savings in a snapshot versus another copy of your MDF/LDFs. 

Keep in mind the following limitations if you do decide to explore snapshots:

  1. Snapshots cannot be taken for system databases (not that big of a deal, in my opinion)
  2. Snapshots can only be created on an NTFS file system.
  3. As mentioned above, snapshots are read-only copies of the database, so changes to the data are obviously not p0ssible.
  4. Snapshots are I/O intensive and this should always be considered especially with limited resources.
  5. Snapshots are not supported for filestreams!
  6. Snapshots cannot be backed up or restored.
  7. Snapshots cannot be detached or attached.
  8. The source database cannot be detached, dropped or restored while there are snapshots present.
  9. If the source database is unavailable, then so will the snapshot be unavailable.

I feel like I am leaving off a couple of more limitations, but these are the ones I deal with mostly.  I will blog more on this subject at a later time.  Enjoy!

%d bloggers like this: