How can we improve SQL Source Control?

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.

10 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    James PenmanJames Penman shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Andrew WhitcombAndrew Whitcomb commented  ·   ·  Flag as inappropriate

        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.

      Feedback and Knowledge Base