Find revision number of deployed database
It would be very useful if it was possible to see which revision number was last deployed to a database. Source Control 4 used to update the extended properties after deployment but version 5 no longer does this.
Not knowing the revision number makes it difficult to bring a Live db up to the same revision as a UAT db, and ensuring the required migration scripts also get executed on the Live db.
Any change this could be added somewhere? Extended properties seemed a good place to save it.
Anonymous
shared this idea
-
Andrew Whitcomb
commented
We also ran into this issue when upgrading to Source Control 5, and we implemented our own extended property to mange this. When our CI environment builds a database installer, it updates the custom extended property to the revision number that we are building. We also set a second extended property that contains the version number (major.minor.build.revision format) for the database.
If I want to see the value of the extended property for all the databases in one of our servers, I just run a query that looks at sys.databases in the master database, and then calls fn_listextendedproperty on each database to read the extended property. I end up with a result set listing each database and the revision level it is currently at. You could then use these results to execute SQLCompare through the command line to bring each database up to the desired revision level.