CODEWITHSUNDEEP
sql-serverdatabase
Jack
Jack

Reputation: 520

Restore sql server database without deleting existing database tables

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?

Upvotes: 2

Views: 2540

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19204

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

Upvotes: 2

Rahul
Rahul

Reputation: 77896

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.

Upvotes: 2

Related Questions