One of the sessions we did yesterday at IT Forum was an Instructor-Led Lab on database snapshots, an Enterprise Edition feature of SQL Server 2005. Database snapshots are not particularly well known in the DBA community and there are many misconceptions about them. The session generated a bunch of questions, some of which I’ll answer here.


Q1) Can you change the data in table in a database snapshot?


A1) No.


Q2) Can you change the permissions in a database snapshot?


A2) Nope, sorry.


Q3) Can you backup a database snapshot?


A3) Afraid not.


A4) Can you detach a database snapshot?


A4) Err, nope.


You may have noticed a pattern here :-) Basically, the only things you can do with a database snapshot are select from it, and revert to it – i.e. rollback the entire database on which it is based to the point-in-time at which the database snapshot was created. Database snapshots are not updateable in any way, and at the time I left MS, there were no plans to change that in the future. Regardless of these limitations, database snapshots are very useful. I searched both our blogs for some examples to link to and couldn’t find any so I’ll put together a post on using them sometime over the next week or so.


The final question I *was* able to answer successfully for a very happy conference attendee:


Q5) Can you create a partial snapshot on a database, say a single filegroup, for reporting?


A5) No – HOWEVER, if you’re on Enterprise Edition, you can make use of partial database availability. Hopefully you have your database structured so the primary filegroup has nothing but system tables in, and you have the table you’re interested in isolated in a seperate filegroup – let’s call it filegroup X. (He answered ‘yes’ to all three!). If that is that case, backup the primary filegroup and filegroup X. Then restore the primary filegroup followed by filegroup X in a separate location. As long as the primary filegroup of a database is online in SQL Server 2005 Enterprise Edition, then the database is online and any other online filegroups are available – partial database availability. This means you’ve effectively created a point-in-time snapshot of a single filegroup, albeit at the expense of having to have a full copy of the filegroup.