Differential databases (not backups)

Question

When working with virtual machines often it can be very useful to have a base read-only VM then have several smaller VM's that use that larger VM as a base and write their changes to their own writeable copy.

Is something similar available in Microsoft Sql Server?

The situation I am in is we host a demo copy of our product that our clients can connect to for about 30 days or so. When we create a demo account we have to create a new copy of the base database the program uses and have the software point at that. Each image is a little over 2GB, but on average the demo users will only change about maybe 100MB worth of data in the database.

What I would like to do is have a read-only database that acts as a base then have the demo databases create a "differencing database" and write out it's data and log information to that differencing database. Is this possible? I have been searching through the MSDN documentation but I have yet to find anything and unfortunately google searching has been fairly useless as searching for Sql server differential database gets polluted with all the pages about doing differential backups (doing Sql server differential database -backup did not return any useful results either, all I got was information about differential deployment scripts).

Answer 1

No, there is not really any such thing. Of course if the VMs are actively connected on a reliable pipe, a view could always coalesce the original data in the base VM with the changed data in the new VM.

Answer 2

The closest thing is a database snapshot. You take a snapshot of your database, that will act as the 'base image'. Then customers go nuts on the database and hack it to pieces. When all said and done, you revert the database back to the snapshot and voila, everything is back to its original state. See Revert a Database to a Database Snapshot.

You do have space savings between the snapshot and the DB, because the copy-on-write semantics of the snapshot. But unfortunately this only works on a single customer, you cannot have one snapshot serving as origin for multiple writable customers, which is what you ask. Snapshots can server something like a demo boot, where your restore it every morning.