<p>I have several branches in TFS (dev, test, stage) and when I merge changes into the test branch I want the automated build and deploy script to find all the updated SQL files and deploy them to the test database.</p>

<p>I thought I could do this by finding all the changesets associated with the build since the last good build, finding all the sql files in the changesets and deploying them. However I don't seem to be having the changeset associated with the build for some reason so my question is twofold:</p>

<p>1) How do I ensure that a changeset is associated with a particular build?</p>

<p>2) How can I get a list of files that have changed in the branch since the last good build? I have the last successfully built build but I'm unsure how to get the files without checking the changesets (which as mentioned above are not associated with the build!)</p>

So I can understand the intuitive appeal of this approach, but I don't think it's the right way to go.
For one thing it's going to be difficult. But the second problem is that TFS doesn't have a good way to record deployment data.
For the first question, I'm not sure what that means. For the second question you can use the build labels and tf history today list of the changed files.
As an alternative, you could reconsider how you want to manage SQL changes. I use a low-tech method of keeping the current pending changes in one directory, and then after deploying moving the files to a different directory. This method can be enhanced by keeping a deployment history table in the database. You may also want to look into vsts DB addition, the current
CTP
has a lot of new features around managing database changes. I also hear that
Red Gate
has nice database management tools as well.