2

I want to copy production server database to Development server, I am using backup and restore to take production server db to Development server.

I have restored it successfully and added tables and SP in restored DB. Again next day I have to restore same database, after restore my existing tables and SP will get deleted.

I can not use SP and extra tables on production server DB. I want to copy it to development server with a real time data and on development server I can do anything without impacting to production server db.

Can anyone suggest better way to doing this?

Improve this question

2 Answers 2

Reset to default
2

You are saying you want to merge changes.

  1. Use a database diff tool (such as database compare in visual studio 2013 to generate a 'difference' script between your dev and prod database. For example you run this tool against dev and prod and it spits out a bunch of create procedure, view, table etc. scripts

  2. Generate insert scripts for all dev - only tables

  3. Restore prod over dev

  4. Execute the scripts from step 1 and 2 in dev

There is often some human intervention required in any kind of merge. For example what if your prod tables have a column that is a different data type in dev, and your SP's are expecting this?

The other option is to build integrations that just load data from prod into dev, but this requires maintenance as changes occur in dev and prod

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Yes, you should take a incremental backup rather than a full backup, since a full backup has already been restored to your Dev database. Then while you re-store it should get the newly created DB objects or data.

Improve this answer

2 Comments

thanks rahul, after incremental backup restore i don't want to changes in my destination DB, only i will update my data.
Yes, take a look here technet.microsoft.com/en-us/library/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.