Create Data base SnapShots with our own file formats

Question

Hi All,

We want to create a c#/VC++ application by using which we should be able to create sql server database SNAPSHOTs in our own file format (in online manner).

And later we should be able to get data from those snapshots files in offline manner using our application ( i.e. we should be able to get number/names of tables/columns...etc details).

Does anybody know how to do that?

Thanks

Answer 1

If you're purely interested in the names of tables and columns, a simple solution would be to query INFORMATION_SCHEMA.COLUMNS:

select * from INFORMATION_SCHEMA.COLUMNS

You could store the output of this query in a file format of your choice - it should give you enough information to visualise the structure of the tables at a later date.

The INFORMATION_SCHEMA also includes other views on indexes, constraints etc., which you could join together to get a more complete picture of the database structure. It is included in SQL 2000 SP3 and all later versions.

Alternatively, if you have them, you could use the DDL scripts for your database from you source control system, and parse those for a historical view of the schema.

Answer 2

I'm fairly certain it's in fact impossible.

The fundamental mechanism powering snapshots is copy-on-write. COW allows you to make a cheap copy of existing data, efectively by not copying the data until an actual change is made, and then copying only the modified parts. Hence, any unmodified data will be shared between the original and the apparent copy.

A database snapshot is merely the original version, while the "current" state of the database is the COW copy. You can't set that up yourself, because you can't just make a COW copy of the database. And even if you could make such a copy, the entire point of a COW copy is that it is a copy i.e. in the same format. So it wouldn't be in your format.