December 28, 2019 at 6:42 pm
I am new to DBA role. I have requirement below. I have a database environment named SystemApp deployed on Development DB Server. In production server, the database with same name exists which is more updated with latest table definitions & other DB changes. I need to replicate all these changes to our development database. I am doing the following tasks in the order in which it is listed. Kindly look into whether I am doing the correct way. Please correct if I am doing wrong way or something more needs to be added to this list. Thanks.
1) First I will take the DB backup of the database SystemApp deployed on Development DB Server.
2) Take the scripts of DB & DB objects of the database SystemApp deployed on Production DB Server using Tasks >> Generate Scripts option.
3) DROP the database SystemApp deployed on Development DB Server.
4) Run the database scripts of Production DB in Development DB Server.
Will this work ? Kindly guide me on this.
December 29, 2019 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 30, 2019 at 3:06 pm
How often does this data need to be replicated? Why do you feel the need to drop the development database to refresh the data? Are there permission differences between the development and production table?
December 30, 2019 at 3:25 pm
I am new to DBA role.
Really? You've been asking questions on here for about nine years.
How did your production database come to be more updated than the development one? Usually it would be the other way round. If you have the scripts that you used to update production, run them on the development database. If you don't, it's probably time to think about getting your code under source control.
The way you are proposing to go about this is manual and prone to human error. If you decide to proceed like that in any case, the best way to know whether it will work is to try it. I imagine you'll need an additional step between 3 and 4 to restore the database.
John
December 30, 2019 at 6:12 pm
If I had to do this on a regular basis and couldn't simply do a restore (because of PII of the data simply being too large for a Dev environment, etc) along with rebuilding the Dev users and repointing any synonyms or pass-through views, I'd invest in RedGate's SQL Compare product or maybe even their SQL Clone product (which can also mask or replace PII data).
I do agree that if Dev is so different than Prod, you might have a problem with your code deployment process because things should always be in Dev first.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2019 at 8:36 am
I take this opportunity to talk about the probable root cause of schema difference between Dev and Prod. Unless we work on root cause, there would many such recurring opportunities to fix the version difference between various environments.
I think the difference between Dev & Prod happened due to unavailability of proper Source Code Versioning process, which is a must. Source Code Versioning is done majorly for App Code but is widely ignored for Databases.
SSDT along with GIT/TFS can be leveraged for this purpose.
Long time ago, I wrote an article on SSDT – The untold story. I hope this article would make sense for the readers of this thread.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply