Refresh table data from Prod to UAT

Question

Refresh table data from Prod to UAT

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
March 13, 2017 at 4:19 pm

#335727

Hello Folks,
What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.
Many thanks!
Drop the FKs
Truncate the tables
Set IDENTITY_INSERT on for the destination table, if you're not worried about the id values then skip this
Load the tables from production using select queries, a linked server would work here
Turn off the IDENTITY_INSERT if you switched it on
Create the FK constraint

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

SQL!$@w$0ME - Monday, March 13, 2017 4:19 PM
Hello Folks,
What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.
Many thanks!

1) Backup Production db
2) Restore over top of UAT db

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

Phil, I don't need a database refresh. Just looking for best options to copy/sync table data (few tables).

joeroshan SSChampion Points: 10377 More actions · Answer 3

It might be difficult to suggest an approach by an outsider who do not know any thing about the database and the application it supports.
But even then the basic approach would,

Backup the UAT database
Disable/Drop the FKs
Truncate the tables
Load the tables from production using import export wizard/linked server
Enable/create constraint

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ricky Valencia SSCommitted Points: 1907 More actions · Answer 4

Hi

If you just want to sync few tables, how about setting up Transaction Replication

--Transactional Replication
https://msdn.microsoft.com/en-us/library/ms151176.aspx

-- Setting Up Transactional Replication: A Step-by-step Guide
https://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step

Best Regards,
Ricky

thbs SSC Veteran Points: 225 More actions · Answer 5

Or use a tool like
http://www.red-gate.com/products/sql-development/sql-data-compare/

Phil Parkin SSC Guru Points: 246695 More actions · Answer 6

SQL!$@w$0ME - Monday, March 13, 2017 10:43 PM
Phil, I don't need a database refresh. Just looking for best options to copy/sync table data (few tables).

Had you original post included this detail, I would not have responded as I did. But it didn't.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Perry Whittle SSC Guru Points: 233854 More actions · Answer 7

SQL!$@w$0ME - Monday, March 13, 2017 4:19 PM
Hello Folks,
What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.
Many thanks!

Use some code to do what has already been mentioned

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 8

Perry Whittle - Tuesday, March 14, 2017 11:09 AM
SQL!$@w$0ME - Monday, March 13, 2017 4:19 PM
Hello Folks,
What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.
Many thanks!
Use some code to do what has already been mentioned
Drop the FKs
Truncate the tables
Set IDENTITY_INSERT on for the destination table, if you're not worried about the id values then skip this
Load the tables from production using select queries, a linked server would work here
Turn off the IDENTITY_INSERT if you switched it on
Create the FK constraint

Thanks Perry!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 9

Phil Parkin - Tuesday, March 14, 2017 5:48 AM
SQL!$@w$0ME - Monday, March 13, 2017 10:43 PM
Phil, I don't need a database refresh. Just looking for best options to copy/sync table data (few tables).
Had you original post included this detail, I would not have responded as I did. But it didn't.

Sorry for any confusion I made. I mentioned as "table refresh" in the heading.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 10

joeroshan - Monday, March 13, 2017 11:01 PM
It might be difficult to suggest an approach by an outsider who do not know any thing about the database and the application it supports.
But even then the basic approach would,
Backup the UAT database
Disable/Drop the FKs
Truncate the tables
Load the tables from production using import export wizard/linked server
Enable/create constraint

Thanks Roshan!

richlion2 SSCrazy Points: 2484 More actions · Answer 11

How about using "bcp" which is an export/import tool?
You still have to disable triggers, truncate tables before you do the import.
You can however pick and choose which tables you want to refresh (I assume that is why the Backup/Restore is not your option) and
build it into a simple .CMD script to execute.

You also didn't mention if you have any encrypted procedures, views or triggers. If you can DISABLE triggers, you should be file. Older SQL versions only had DROP TRIGGER, which defied the task.

I recently ran into a database with encrypted code and a third party had to do the job as I didn't have the code for the triggers.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 12

richlion2 - Wednesday, March 15, 2017 4:37 AM
How about using "bcp" which is an export/import tool?
You still have to disable triggers, truncate tables before you do the import.
You can however pick and choose which tables you want to refresh (I assume that is why the Backup/Restore is not your option) and
build it into a simple .CMD script to execute.
You also didn't mention if you have any encrypted procedures, views or triggers. If you can DISABLE triggers, you should be file. Older SQL versions only had DROP TRIGGER, which defied the task.
I recently ran into a database with encrypted code and a third party had to do the job as I didn't have the code for the triggers.

Thank you!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 13

Perry Whittle - Tuesday, March 14, 2017 11:09 AM
SQL!$@w$0ME - Monday, March 13, 2017 4:19 PM
Hello Folks,
What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.
Many thanks!
Use some code to do what has already been mentioned
Drop the FKs
Truncate the tables
Set IDENTITY_INSERT on for the destination table, if you're not worried about the id values then skip this
Load the tables from production using select queries, a linked server would work here
Turn off the IDENTITY_INSERT if you switched it on
Create the FK constraint

Hi Perry, Should I disable all triggers before data load using linked server or export import and enable after the load?

richlion2 SSCrazy Points: 2484 More actions · Answer 14

SQL!$@w$0ME - Wednesday, March 15, 2017 6:07 AM
Perry Whittle - Tuesday, March 14, 2017 11:09 AM
SQL!$@w$0ME - Monday, March 13, 2017 4:19 PM
Hello Folks,
What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.
Many thanks!
Use some code to do what has already been mentioned
Drop the FKs
Truncate the tables
Set IDENTITY_INSERT on for the destination table, if you're not worried about the id values then skip this
Load the tables from production using select queries, a linked server would work here
Turn off the IDENTITY_INSERT if you switched it on
Create the FK constraint
Hi Perry, Should I disable all triggers before data load using linked server or export import and enable after the load?

It depends on how your application works, however whichever method you use as a general rule when importing tables after clearing it's best to disable all triggers on relevant tables, import and then re-enable triggers. It's pretty much a general approach that is true in the majority of import scenarios, especially if you have triggers where you don't know what they do. Triggers are designed by developers to serve a specific purpose within the application, updates are in specific order, etc. If you start a bulk import with triggers eanabled the import may completely go wild and destroy the well built-in consitency of your database, could also lead to corruption (meaning sensless data structure). Disabling Primary Keys is also advisable, as you may not know the exact order in which your tables will have to be imported. It's best to test such an export/import and develop the correct procedure.