I'm designing a refresh from prod to dev. I would like to know how do I keep some of the db objects the Developers created, i dont want to lose them. Let's say the refresh happens daily.
How do the devs deploy to the dev server currently?
Try
Merging !!
Hope this helps .. 
we dont have one.
how do you deploy to your other sql servers?
is it automated (powershell, gitlab deploy continuous), manual?
In that case here is what I recommend
- Automate it or make it triggered by some other mechanism
- Devs deploy their code to a version control tool like gitlab. Or they deploy it to a folder in the network after it has been peer reviewed
- The automation kicks and copies the prod backup to a drive in the dev server and restores it
- Next the automation process applies the code devs have deployed to this newly restored db
1 Like
@yosiasz, how do i keep the objects that has been created in Development? e.g: Developer creates a new stored procedure. How do i keep it before the automatic refresh is triggered?
Keep it saved as a SQL script in a folder somewhere or gitlab.
Dev should never create their sql objects directly manually on a server but rather using .sql file extension scripts
1 Like
The objects could be anything from defaults to fully partitioned tables, indexes, stored procedures, etc, and you could (likely WILL) have overlapping keys between prod and dev . I know of no tool or method that will accomplish this safely never mind easily.
As a bit of a sidebar, if your developers aren't using scripts to deploy their objects and data, there's pretty much no hope for this.
2 Likes
You can either create your own deployment ecosystem or use free version of gitlab. For starters I recommend a scheduled windows task that runs powershell that reads a folder, or use gitlabs CICD or some other products out there, dime a dozen these days
Also make sure all of your scripts are re-runnable.
I do it the following way, folder structure. You can do it the way you want. It helps keep neat for me.
1 Like