Refresh database IN development

1

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.

2

How do the devs deploy to the dev server currently?

3

Try

Merging !!

Hope this helps .. :slight_smile:

4

we dont have one.

5

how do you deploy to your other sql servers?
is it automated (powershell, gitlab deploy continuous), manual?

6

@yosiasz we dont have one.

7

@harishgg1 elaborate

8

In that case here is what I recommend

  1. Automate it or make it triggered by some other mechanism
  2. 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
  3. The automation kicks and copies the prod backup to a drive in the dev server and restores it
  4. Next the automation process applies the code devs have deployed to this newly restored db
1 Like
9

Please Google search

Screenshot_2020-03-22-11-08-07-95.jpg
Screenshot_2020-03-22-11-08-07-95.jpg1080×2340 535 KB

1 Like
10

@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?

11

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
12

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
13

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.

image

1 Like