An interesting question came up at the Redgate Summit a few weeks ago:

What approach do you take to maintain database releases/changes in-flight that haven't been applied to Production after them being wiped out after a database refresh?

I have to assume that the person asking this question has not taken even the first step on their journey towards automating database deployments. The reason I say that is because the answer to this question is also the one thing I advocate everyone do as step 1 on their database DevOps journey:

Get your database into source control.

Why Source Control

The next question we should ask is, why source control? The answer is both complex and simple. Let's start with the simple part because it has the most bearing on the question asked at Summit.

Source control, among all the other things that it does for you (more on that in a minute), acts as a point of truth. When you have your code in source control, and you always use source control for your deployments (this is key), you know that any changes not yet in production are present there in your source control system. Now, we can get into versioning of code versus branches, how best to manage releases, all the various aspects of source code management. All of that does have bearing. However, the core is quite simple. Source control is a version of truth for a moment in time. Production is also a source of truth. Another would be a backup from production. Yet another, and the one we'd be most interested in if we're talking about refreshing non-production environments, is a clean copy of production.

When you refresh a non-production environment with a clean copy of production, you have code that has been updated as part of active development. It's in your source control system. After the refresh, you use whatever mechanism you have to take the code from source control and use it to update the database. This ensures that the in-flight code is updated on that refreshed database. This simple fact is why we start with source control.

There are a whole slew of other reasons to get started with source control. Source control gives you an additional backup of your code. More importantly, it gives you a backup of new code, as well as old code. Source control also provides a way to undo changes. If you updated a procedure and then realize you made a mistake, having previous versions of the code in source control is a quick and easy way to undo that change. You get a history of changes from source control. That history shows how code has changed over time. It also provides an audit capability. Who, exactly, made that change? All of these reasons, plus acting as a source of truth, are why I advocate so strongly for starting with source control.

Conclusion

If you start with source control, you get that moment in time piece of truth. That is what allows you to deal with your changes that haven't yet been deployed to production, neatly answering the original question.

For more reading, we have a whole slew of articles on getting your database into, and out of, source control.