March 13, 2017 at 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!
March 13, 2017 at 5:18 pm
SQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello 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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 13, 2017 at 10:43 pm
Phil, I don't need a database refresh. Just looking for best options to copy/sync table data (few tables).
March 13, 2017 at 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,
March 14, 2017 at 1:30 am
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
March 14, 2017 at 4:18 am
Or use a tool like
http://www.red-gate.com/products/sql-development/sql-data-compare/
March 14, 2017 at 5:48 am
SQL!$@w$0ME - Monday, March 13, 2017 10:43 PMPhil, 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 14, 2017 at 11:09 am
SQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello 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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 14, 2017 at 3:18 pm
Perry Whittle - Tuesday, March 14, 2017 11:09 AMSQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello 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!
March 14, 2017 at 3:19 pm
Phil Parkin - Tuesday, March 14, 2017 5:48 AMSQL!$@w$0ME - Monday, March 13, 2017 10:43 PMPhil, 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.
March 14, 2017 at 3:19 pm
joeroshan - Monday, March 13, 2017 11:01 PMIt 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!
March 15, 2017 at 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.
March 15, 2017 at 6:05 am
richlion2 - Wednesday, March 15, 2017 4:37 AMHow 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!
March 15, 2017 at 6:07 am
Perry Whittle - Tuesday, March 14, 2017 11:09 AMSQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello 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?
March 20, 2017 at 5:27 am
SQL!$@w$0ME - Wednesday, March 15, 2017 6:07 AMPerry Whittle - Tuesday, March 14, 2017 11:09 AMSQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello 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.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply