Best practice on importing Craft db backups?

Question

Our team's typical workflow is pull from repo, delete craft tables, import latest db backup, work locally.

We don't want everyone working on the dev server db for early development, because we'll be breaking the site too much for everyone else.

Which brings me to my question, should I be deleting tables before importing? If I don't delete the tables Craft has to do some cleanup magic on the backend (I don't know what it does). I do know that Craft creates tables for matrix types. Would not deleting tables potentially leave orphaned db tables? Obviously there could other side effects as well.

Opinions? Clarification?

Answer 1

The short answer is, yes, definitely drop your existing tables before importing a new .sql file. If you don't, you run a high risk of Craft developing weird issues of data mismatching. This is especially true if the two versions of the database were not created with the same exact version number of Craft.

NOTE: You may want to back up your existing database before dropping all of the tables.

The longer answer is, database syncing is an interesting and complicated problem, with many possible solutions. Check out this thread discussing the "holy grail" of database syncing across multiple environments...

What's the best practice for handling data migration and organization across development environments?

Ultimately, your data migration patterns are up to you. There are varying approaches, some involving Shell scripts and Git hooks. It's an evolving challenge for the Craft community (and other CMS's as well), so keep your eyes peeled for the next brilliant "eureka" moment!

Answer 2

They key is to get the database from the "backup database" function in the control panel that includes

DROP TABLE IF EXISTS craft_analytics_settings; ...

before it creates all the tables so you shouldn't have to delete any tables first, been working for us that way since the beginning.