Tuesday, November 27, 2007

Database snapshot files

Be aware of the fact that a database snapshot contains all the data of the referenced database at a certain moment. Not only the actual data, but also the metadata, the bookkeeping of Sql Server, is present in the snapshot.

This can give some unexpected results when you query tables like sys.database_files for example. Instead of getting the set of files the snapshot exists of, you get the info that was originally in the referenced database, being the files of that database, not the snapshot. That information and much more is available through master.sys.master_files.