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.
Summer School Savings SALE is live!
Happy Fourth of July to all who celebrate! I’m looking forward to a great view Thursday evening looking west along the Skagit Valley with some
2 thoughts on “Conference Questions Pot-Pourri #5: Database snapshots, and creating a snapshot of a single filegroup”
Hi Paul,
It’s a very interesting series of posts, thank you for that! Could I also add a question from here?
I’d like to get into understanding what would happen if during database backup, mirroring or replication any database schema changes have occured (e.g. ALTER TABLE)? Thanks!
Hi Leonid,
Database schema changes are no problem for backups and database mirroring. For replication, some size-of-data operations are not supported. The way to get around this is to put the schema alteration inside a stored-proc which exists on the replication publisher and subscriber(s) and execute the SP instead of the actual schema alteration itself. The execution of the SP will be replicated and cause the schema alteration to occur on the subscriber(s).
Thanks