Sync Develepment and Production Databases

Question

Sync Develepment and Production Databases

NoviceJatt

SSCommitted

Points: 1671
More actions
September 15, 2010 at 1:41 pm

#240486

Hi,
How can I sync the development and production databases. i.e how to keep the contents same other than backup and restore.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

Novicejatt (9/15/2010)
Hi,
How can I sync the development and production databases. i.e how to keep the contents same other than backup and restore.
Thanks

Which env you want to sync from? If it is Prod to DEV then you can schedule a job to restore the Prod backup on the dev, but in this scenario it will overwrite what ever changes done in DEV.

Use SSIS to move only the incremental data from Prod to DEV.

"More Green More Oxygen !! Plant a tree today"

steveb. SSC-Forever Points: 46733 More actions · Answer 2

steveb.

SSC-Forever

Points: 46733

September 16, 2010 at 3:02 am

#1222000

you could also use data compare from red-gate

NoviceJatt SSCommitted Points: 1671 More actions · Answer 3

NoviceJatt

SSCommitted

Points: 1671

September 16, 2010 at 2:42 pm

#1222620

Hi,

I want to sync the Dev to Prod.

Thanks

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Novicejatt (9/16/2010)
I want to sync the Dev to Prod.

If you really want to overwrite production with the whole content of development the easy way is backup/restore.

Having said that, it is really unusual to move the whole content of Development on top of Production. Usually specific DDL or Code changes are applied/promoted from Dev to Prod; I'll suggest to rely in a source control plus change control combo.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

NoviceJatt SSCommitted Points: 1671 More actions · Answer 5

Hi,

I have to add new data to database every day .... so was thinking not to add the data directly to production coz I might messup the prod database .... instead add the data to devlopment database and then sync it with the production...

Thanks

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

IMHO, adding data to development so as not to mess up production is a bad idea. You'll start to think that you don't have to be as careful because its' DEV.

What data are you adding that is an issue? Typically if you are looking to verify or clean data, you add it to some staging tables, check it, then use established routines/SSIS/T-SQL to move this data into production tables.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Novicejatt (9/16/2010)
I have to add new data to database every day .... so was thinking not to add the data directly to production coz I might messup the prod database .... instead add the data to devlopment database and then sync it with the production...

This is a very unusual strategy indeed.

If for any reason this has to be done please create a new database and call it "Staging" - do not use Development for Production purposes.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

NoviceJatt SSCommitted Points: 1671 More actions · Answer 8

Hi,

I'm just trying to be carefull ..... but thank you all.

Imran Ashraf-452633 SSCommitted Points: 1969 More actions · Answer 9

As per previous post what I would suggest is that you have a staging DB on Prod and then import your data to the relevant tables on your Production DB. If you script it right then it can be put into a SSIS package or T-SQL Scripted jobs. Entirely up to you.

Varinder Sandhu Mr or Mrs. 500 Points: 559 More actions · Answer 10

I agree with Steve Jones - Editor (Post #987787)

Varinder Sandhu,
http://www.varindersandhu.in/