Restore a Db from a backup on another server to a server where the Db is linked to source control

bitshift2

Want to restore a backup of a Db from production over the top of the Db on a development server.  The development server Db is linked to a Git branch.  All changes have been committed and pushed to my remote.  Now I want to refresh the dev Db from production.

Should I unlink the Db first or - simply do the restore as usual and then re-apply the changes?

David Atkinson

I don't think you need to unlink.

Are you automating the production refresh process? If so, before you restore your backup, use sqlcompare.exe to take a schema snapshot (.snp file) of the dev DB, and only then do the backup restore over the top, and now use sqlcompare.exe to sync using the snapshot as the source and the devDB as the target, which will restore all of the changes. 

You can also use the same principle by checking out the latest scripts folder revision from Git and using this as the source, but I prefer the snapshot solution as this doesn't require you to make sure all changes are pushed from dev to the Git repo before you perform the refresh.

bitshift2

Well, in the time I since posted this a few minutes ago - I did the restore, then chose "get latest from source control".  Now, my local branch and remote were in sync together, and this should have resulted in myself seeing differences between the Db and my source control.  However, it showed there were no changes.

So then, I un-linked and re-linked and did this again (get latest from source control) and THEN my changes showed.

David Atkinson

If you restore over the top, this will be interpreted as making changes to your dev DB. In theory you should see nothing in 'get latest' as the changes have been made to the dev instance and will therefore appear in the commit tab. Unlink and relink will 'reset' the tool and instead it will ask you to choose which way you want to sync.