Here's the process I thought that we would follow. We're using version 3.1.

We have a production database with nightly backups, and need developers to restore that to their development environment in order to have the latest production data. The database has > 1000 tables. We have linked using the Dedicated model.

I baselined (did the initial commit of) the source control repository by using Thursday's backup (linking our TFS folder to this database).

I made some changes on my local copy of the database (updated stored procedure XYZ). I checked this change in via SQL Source Control & verified that both Commit Changes and Get Latest tabs were clean. This stored procedure did not get updated on the Production database because it's not ready for production yet.

Friday morning I restored the Friday morning backup of the Production database (which does not contain my mods to XYZ). I did not un-link my local database from SQL Source Control

I opened the SQL Source Control window, expecting that I would see XYZ listed on the Get Latest tab (since it's newer in TFS than it is in the database).

However, it was actually listed on the Commit Changes tab, and the change script was going to UNDO my change. It is as if my outdated copy of XYZ is being treated as the latest copy. I checked the properties of other objects in SSMS, and they have old Created dates, so it's not like the restore is clobbering an object's Created date.

Thanks for your post. Are the extended properties being updated when you perform the restore, i.e. does the DB have a lower revision number after a restore than what's in your source control system?

I guess SQL Source Control *should* perform a get latest rather than a commit if the revision numbers are lower (that would be what I expect to happen, but I'll need to check with the developers), but if the revision number is the same or greater on the actual DB then I would expect the behaviour that you're experiencing.

The database being restored doesn't have any of the extended properties.

I had thought of this as a solution, so as part of the restore I went ahead and set the extended properties (SQLSourceControl Database Revision, SQLSourceControl Scripts Location, and SQLSourceControl Migration Scripts Location), setting the Revision value to the value of the revision when I did my initial commit.

Thanks for your post. I've double checked with the dev team, and there are some other factors coming into play that are messing this up for you.

In the simplest terms, performing the restore over the DB that was already linked caused the working base to be different to the restored DB (it was already successfully synced with the DB prior to the restore, and also synced with the source control system).

Since that happened and the working based and source control system were the same, SQL Source Control did its thing and made the call that you had made a change to the DB, that needed checked in.

If your working base had been in a different state to the revision in source control, then SQL Source Control would have prompted you for a get latest.

It's not really ideal behaviour to perform restores, but if you must do it the simplest thing is to unlink and then re-link the DB after it's been restored. When you do that, your working base will be at the same revision as that of the DB, but less than your source control system.

You should therefore, in that instance, be prompted to perform a get latest.