Keep history after restoring database

alexanderschott

I have a database, which I use for development and test, and I have linked this to SQL Source Control. Everything is fine and I can see the history of changes on each object. The link is setup as a shared database.
So now, I would like to restore the database with a new copy of LIVE database.
Would I loose the history after restore? Or would I need to make an initial commit again?
All the changes which are stored in the SQL Source Control database should be applied to the restored database.

way0utwest

OK, I tried this. Backed up a db (_Dev), then restored as a new name (_Prod_Test). Added objects to the original db (_Dev), which is linked to TFS as a shared db. Linked the restored DB (_Prod_Test) to TFS, same folder, as shared, and Get Latest didn't find objects that I confirm exist in Source Code Explorer.

I restarted SSMS, no change.

I de-linked the new db (Prod_Test) from TFS and relinked as a dedicated db. Get Latest then showed all my objects.

I then deleted this db and added a new, empty db (_soctest). Linked to TFS as shared, and again, don't get any objects. I'll report as a bug.

way0utwest

OK, I got a note from the developers. The Shared mode doesn't use the Get Latest tab, as all changes are assumed to be in the db. This is because all developers see the same db, and all changes are assumed to be those that need to be committed. So the missing objects are seen as deletes that need to be committed.

To resolve this, link as dedicated mode. Then, if you need multiple developers on the restored db, delink and relink as shared.

way0utwest

The history comes from your VCS. Once you restore from LIVE, I assume that your VCS will be ahead of the dev database, so you'd want to pull changes from the VCS and apply to the restored db.

All history would still be the same in the VCS at this point as applying changes doesn't change anything in the VCS.

alexanderschott

Thank you for the answer.
It's true that the history remains in the VCS after I restore the database.
But if there are new objects in the VCS these don't appear when linking to the VCS from the restored database. I get this message when clicking "Get latest":

"There are not changes to get. Because you're using a shared database, then current version is always up to date with everybody's schema and data changes".


The "Agresso_M5_Staging" database is linked to VCS and contains all the new stored procedures.
When linking to source control from the "Agresso_M5_Staging_old" I don't get the new modules when showing "Get lastest".

But if I use SQL Compare, I get the full overview of objects, which are new or changed.
I would expect that the SQL Source Control would do this when linking.

way0utwest

Are both of these set up as shared database to your VCS? Which VCS do you use? Not sure it matters, but trying to check.

alexanderschott

I’m using Team Foundation Server 2015 as my VCS. Both databases are pointing to this one.

alexanderschott

This is a nice workaround.
Thank you!

way0utwest

You are welcome. Dont' forget to switch the radio button. It remembers the last setting.