Restore of database is comparing to first version of database

swkuhn

Using Sql Automation in VS 2017 connected to VSTS Git and SQL 2017.

We've updated to Sql Automation.  About the same time we updated from Readyroll to SQL Automation I backed up a dev database from Sql 2008 R2 and restored to SQL 2017 on a different dev server.  The restore went fine and compares exactly with the db on Sql 2008 R1.  SQL Automation connected to the new instance of SQL Server 2017 just fine.

The problem is that SQL Automation wants to update all db tables to the "Original Version" of the tables. Many tables have been identified as needing updated to a now incorrect old definition of the tables.  I've confirmed that the files in the Shema-Model folder are correct.  However, the baseline definition is the one being used for the Refresh compare, which is not correct.  The current version in source control should be used in the case that I restored the database from a backup on one server to another server.

How to I convince SQL Automation to use the current version of the db for Refresh compare instead of the "Original Version"?

way0utwest

Is your shadow database the same as the "original" version?

swkuhn

Yes, it looks like the shadow database got created as the original version, not the current version

way0utwest

The Shadow db is what is compared. This should be updated to the current version. You could use Compare to do this, but I think you could specify a new Shadow to "reset" the comparison

swkuhn

With Readyroll, we could create a correct shadow db by delete and then build.  It seems that now a delete of the shadow db and build creates an original shadow db, which is now not correct.  I can not find a setting to instruct Sql Automation to create a correct shadow db.  I can not think of a scenario that I would want a shadow database created as the original baseline.

swkuhn

How do I go about specify a new Shadow to reset the comparison?  As far as I can tell, it is always getting created as <dbName>_<username>.

What I really want to do is use the target database as my golden database to compare against source control and import from and update source control.  This was the case when my database was on the other sql server. It just worked.  Refresh would tell me what tables changed and I could import and generate a migration script. 

But now it is trying to do the reverse, update my target database to a very old version of the database.  Something has gone very wrong.

way0utwest

I was thinking to use SQL Compare,or even run a deployment to the Shadow database, using the SCA package that's created. Just point the .ps1 to the shadow database and update things.

I haven't gotten in this situation, and not sure I can recreate it. Haven't had time today, but perhaps I can try to upgrade an old project and see what happens.