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:
- Snapshots cannot be taken for system databases (not that big of a deal, in my opinion)
- Snapshots can only be created on an NTFS file system.
- As mentioned above, snapshots are read-only copies of the database, so changes to the data are obviously not p0ssible.
- Snapshots are I/O intensive and this should always be considered especially with limited resources.
- Snapshots are not supported for filestreams!
- Snapshots cannot be backed up or restored.
- Snapshots cannot be detached or attached.
- The source database cannot be detached, dropped or restored while there are snapshots present.
- 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!