SQL Server 2008 differencing databases

So here is the deal:

Background

• A Hyper-V VM can handle a differencing disk mode, where one can set the original VHD file in a read-only state and create a new vhd which keeps track of, and persists, the changes. The advantage here is you can easily create new VMs without having to reinstall Windows, etc.

Problem

• What I am looking for is something similar, but for SQL Server databases. We do all of our development locally and then we have a box that has X instances run on it (1 for each developer). We then have a process which copies the production backups that are made and restores them to these instances. After this is complete, it checks-out a branch that a developer chooses (of SQL scripts) and runs the scripts on the instance. This way they can test their code on production data prior to it actually hitting production. However, it is a real pain to have a copy of all our production dbs for each instance-- it would be nice to have 1 set开发者_如何学运维 of them and have a differential option which just persists the changes made. Is this possible or am I dreaming?

Possible solution

• One solution I thought of is just to use an actual differencing disk VHD. I would create a base VHD that has our production backup databases, which would be modified/created night with the production database. I then would have it modify/create differencing disks and apply the scripts to each differencing disk. This way we have 1 copy of the dbs, and the developer's changes are recorded to a separate differencing disk. However, I was hoping to accomplish this in SQL server.

---

Basically the conclusion I have come to is to try and automate the process of differencing disks as below:

1. Create a new VHD on a network share- we'll call this NAS1.
2. Mount the VHD from NAS1 on a machine that acts as a SQL processor (we'll call this SQLPROCESS1.
3. SQLPROCESS1 performs the following actions.
   1. Copy BAK SQL files from production to SQLPROCESS1 (this might take a while, but this entire #3 could be put into a threaded application, so it could be copying multiple and restoring at the same time).
   2. Restore files on SQLPROCESS1 and point data files (mdf, ldf) to reside on the new VHD.
   3. Optional: Change SQL dbs to SIMPLE backup mode and use SHRINKFILE since we'll be using them solely for development (and don't need backups). This can save us a lot of space.
   4. Detach all dbs.
   5. Detach the VHD.
4. Create differencing disk from parent on NAS1.
5. Copy differencing disk X number of times (as needed per instance or developer).
6. Optional: We use a central server called TEST1 for testing and this is where we are going to mount each differencing disk-- 1 per instance or developer.
   1. We'll first need to detach all dbs from each instance.
   2. Then we'll need to unmount/detach the existing differencing VHDs if there are any.
7. Attach differencing disk(s).
8. Reattach all dbs in SQL Server.
9. Optional: run SQL scripts from a code repository branch as specified per developer.

References:

• http://obligatorymoniker.wordpress.com/2010/08/21/how-to-create-a-differencing-vhd-that-refers-to-a-parent-vhd-that-is-on-a-network-share-from-windows-7/

To automate I'd use a simple set of batch files, VBS, or PowerShell.

Edit: Just tried this and it works great! Developers now have their own instance and it only records their changes.