Database snapshots

Database snapshots are very cool. I think the only problem with them is that the possible uses are so numerous that I don’t think people have quite decided yet what they are best for. One possibility is that they are going to be great for all conceivable uses but the danger is that they will get misused and then all kinds of things will happen. The technology used for database snapshots does not belong to SQL Server but Windows so there are a number of restrictions. The first thing to remember is that a database snapshot behaves like any other database but, of course, is read only. You can select from it, join its tables even restore a database to the point in the snapshot was taken which sounds great but then you think point in time copies of the database will be huge. Have a number of them and you server is going to run out of disk very quickly but if you take a snapshot of a database and then go and checkout the size of the data file in explorer you get a surprise. Even though the file states its size as, lets say, 2Gb, the same as the original data file of the database the snapshot was taken from, its size on disk will only be 64Kb.

Database snapshot use a copy-on-write mechanism via NTFS sparse files (NTFS, note the disk technology being used, very important).This means that the snapshot only contains the pages that have changed on the database. If I went and updated 100Mb of data in my database then my snapshot would increase in size by 100Mb due to the updated pages being copied into the snapshot. Also this is done before the data is committed so rolling back has no effect on what has been written to the snapshot. Very helpful and that is in its essence what a snapshot is, not a replacement for backups, not a way of retaining a copy of historical data for reporting purposes (although I can almost certainly see it getting used that way) but it is a good safety net when making data changes manually or even procedurally. The important thing is that it doesn’t promote lazy administration and hopefully the restrictions on this new feature (and there are plenty of them) should ensure that.