How to generate scripts for production database?

Question

I have a development database where I have added new records to a number of tables.

I want to move these changes to production database. The problem is that the ID columns in both have different values.

For ex. Last StudentId in dev is 10 and in production it is 25. Therefore the corresponding FK IDs also different.

I have generated scripts but I am bit puzzled on how to execute on production because of this issue.

One other approach is that I have even exported data from tables and pasted to Excel. In Excel I will update the IDs and paste this to production table.

What is the best practice to do this safely?

Turn `IDENTITY_INSERT` on when you `INSERT` (if you want them to be the same on both instances)? — Thom A, Nov 28 '18 at 12:47
@Larnu is correct. Here's an example https://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008/7063527#7063527 — Jason Geiger, Nov 28 '18 at 13:01
@Larnu But what about child tables. I will have to manually changed FK IDs. Any way to automate this? — RKh, Nov 28 '18 at 13:10

score 1 · Accepted Answer · answered Nov 28 '18 at 14:14

1

The best practice is to write scripts that will not depend on hard-coded ID values, so that you can run the script in Dev, and it will generate some values based on the data there, and then you can run the exact same script in Production, and it will also work based on the existing data there.

answered Nov 28 '18 at 14:14

Tab Alleman

31,483
7
36
52

Can you provide few samples on the same? – RKh Dec 24 '18 at 14:56
1

For example, instead of ever writing a script with `WHERE UserID=1` you would use `WHERE UserName='TomSmith'` – Tab Alleman Dec 24 '18 at 15:01

How to generate scripts for production database?

1 Answers1