Issue With Restoring Database After Checking In Changes

CraigEddy

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.

What are we doing incorrectly? Or is there a bug in 3.1?

Thanks,
Craig

peter.peart

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.

Pete

CraigEddy

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.

This only seemed to make things worse, though.

peter.peart

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.

HTH!

Pete

CraigEddy

Thanks Pete.

One question: will all of this unlinking/re-linking cause an explosion of files in our local TFS workspaces?

One thing I noticed when I was evaluating SQL Source Control prior to our purchase of the product was that there seemed to be an inordinate number of local TFS workspaces created.

Thanks again,
Craig

peter.peart

It will definitely do so in your user profile (as we create a separate workspace every time we link).

We've put together a little, unsupported utility to help clean those out for you, though. It's available from the below link:

ftp://support.red-gate.com/utilities/SQ ... leaner.zip

HTH!

Pete

pk_davidson

We have the same issue as described in this thread.
Obviously, if we had some of the other red gate tools, it wouldn't be as much of an issue with keeping data synched up.

So I was pleasantly surprised to be able to find this thread as I wasn't even sure how to craft a query...

I am curious, does the cleaner utlity also apply to subversion or other SCCS ?

It would be nice if there were some way to deal with this situation without having to unlink and relink... I guess SQL Data Compare is that answer.

CraigEddy

After we do our restore from "Production" and run some schema scripts which cover items not in source control, we use SQL Compare.

Comparing "from" the Source Control latest version "to" the restored database and letting SQL Compare deploy the changes seems to clear everything up.

When we go to the SQL Source Control window in SSMS, it's "clean" (nothing on Commit Changes, nothing on Get Latest).

pk_davidson

Sounds good...
But it requires having SQL Compare :?

While that's in the pipeline, in the meantime, I just have to remember to unlink before a restore then relink...