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!

Advertisement

About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 8, 2012, in Maintenance and tagged . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: