Database refresh from Production to Dev

Question

We want to restore some databases from Production server to Dev server. We want to create a job that automatically restores a fresh copy of these databases to Dev server on 10th of each month. Could you please advise on how to do this.

Answer 1

I'd suggest putting a SQL Agent job on the Dev server (rather than Prod) that pulls the latest Prod backups from a shared network folder and restores them. That keeps the load off Prod and avoids granting Prod any rights on Dev. You'll want to make sure the Dev SQL Agent service account has read access to that share.

For each database, the job would grab the newest full backup by its actual file timestamp rather than by filename, so you're never guessing which copy is freshest.

For each database in your list, the job kicks off active sessions (single-user with rollback), restores over the existing Dev copy with replace, relocates the data and log files to Dev's paths automatically, then returns it to multi-user.

Fixing orphaned users so Dev logins map correctly, and setting the recovery model to Simple on Dev so you're not accumulating log backups you don't need there.

A single monthly schedule set to day 10, running in a quiet window such as 1 AM, attached to the job and pointed at the Dev instance.

Answer 2

It is almost impossible to give an answer to that question, because there is so much It depends in that question. For instance, is there a daily full backup from production that you can use?

At my client we restore a copy of production to two different test environments nightly. In our case, we don't rely on the backups taken by the IT department, because their backup schedule does not meet our needs. So we have a backup job on the production server at 3:40 in the morning. (For us the time of day is important, but it may not be equally important if you are only going to this monthly.) This backup job writes the backup to a file share on the dev/test server.

We used to have Agent jobs to restore the database, but we have changed this to DevOps pipelines, because for one of the environments we also want to deploy the most recent bits from our main branch. For your case, I will assume that DevOps pipelines is over the top, but that you can simply schedule a job that runs the restore. Exactly how that script would look like depends on your needs. What we do in our SQL script is that we restore the database as OurDBNew and once the restore has completed, we rename the existing database OurDB to OurDBOld and then we rename OurDBNew to OurDB. This is to avoid that the database is inaccessible for longer time. What we also do is to copy permissions and users from OurDBOld to the new OurDB. This can be practical if you have assigned different permissions in the Dev environment and want to keep them.

As for setting up the schedule, that should not be difficult. We do this only daily basis, but the UI in SSMS makes it easy to set up jobs that runs only once a month.