Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tables Refresh Solution


Tables Refresh Solution

Author
Message
SQLBuzz
SQLBuzz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 286
I have 2 database tables.
1) From Production
2) From Dev.
The task is to move the data refresh from Production to Dev Server. But the table schema itself is changed is getting changed and also Primary key & unique key was not implemented properly.
Can anyone help, what will be best process to the data fresh ?
I had tried to copy the table from Prod to dev and check the count of the tables everything looks goods but the table schema , primary key constraint and related key definition are getting changed
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
SQL_TASK (7/1/2013)
I have 2 database tables.
1) From Production
2) From Dev.
The task is to move the data refresh from Production to Dev Server. But the table schema itself is changed is getting changed and also Primary key & unique key was not implemented properly.
Can anyone help, what will be best process to the data fresh ?
I had tried to copy the table from Prod to dev and check the count of the tables everything looks goods but the table schema , primary key constraint and related key definition are getting changed



What do you mean by:
But the table schema itself is changed is getting changed and also Primary key & unique key was not implemented properly.


It is very unclear what you are trying to do here. Are you trying to copy data from Prod to Dev using SSIS? With t-sql? If you can explain the issue a bit more clearly we can surely come up with a solution.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
SQL_TASK (7/1/2013)
I have 2 database tables.
1) From Production
2) From Dev.
The task is to move the data refresh from Production to Dev Server. But the table schema itself is changed is getting changed and also Primary key & unique key was not implemented properly.
Can anyone help, what will be best process to the data fresh ?
I had tried to copy the table from Prod to dev and check the count of the tables everything looks goods but the table schema , primary key constraint and related key definition are getting changed


this is where change management for DDL is absolutely mandatory; if you don't have every script yourself, you need to have the ability to reverse engineer/discover the DDL changes between the databases. a tool like Redgates SQL Compare can help you do that.

this is what i would typically do:
1. Compare the two database, or get the appropriate Change Scripts from source control so they can be applied.
2. Backup the database on production.
3. Restore that backup on Dev.
4. Apply the schema changes(DDL) from step 1.

then the developers would have the latest data, but with their prototype schema changes in place.

I know i built my own crappy little tool for schema changes exactly for this reason; it's a lot of work, especially if the developers don't submit scripts until the very end;

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45129 Visits: 39923
I have a unique method for handling such a thing. :-P I tell the Devs when I'm going to do the refresh and that they have until then to copy anything they haven't checked into source control. That includes any test data that they should BCP out.

I make a backup of the dev database and then I overwrite it from a prod backup at the appointed time. The rule is that any requests to recover anything from that dev backup must be written on a $20 bill and wrapped around a "Jimmy John's" #9 sandwich for each object recovered from that backup.

We also don't allow "promotion by accident". All changes to all objects/data must be submitted in the form of a script that must also pass peer review and DBA review. It then get's successively promoted to QA, then UAT, and finally Prod.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kevin.christiansen
kevin.christiansen
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 41
If you can create a Linked Server to Production, this works for me.

-- Pull Production Data over to Tmp Table
SELECT * INTO #Table1 FROM [Production Link Server].[Table1] WITH(NOLOCK)
SELECT * INTO #Table2 FROM [Production Link Server].[Table2] WITH(NOLOCK)

-- Drop any FK on tables if needed

-- Clean out Tables
TRUNCATE TABLE [Table1]
TRUNCATE TABLE [Table2]

-- Create any FK on tables if needed

-- Repopulate Table1
SET IDENTITY_INSERT [Table1] ON -- Use only if Table1 has Identity Column
INSERT INTO [Table1]
(Table1 Columns)
SELECT * FROM #Table1
SET IDENTITY_INSERT [Table1]OFF

-- Repopulate Table2
SET IDENTITY_INSERT [Table2] ON -- Use only if Table2 has Identity Column
INSERT INTO [Table2]
(Table2 Columns)
SELECT * FROM #Table2
SET IDENTITY_INSERT [Table2]OFF

-- Cleanup
DROP TABLE #Table1, #Table2
SQLBuzz
SQLBuzz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 286
Thanks you ... I am testing in my local machine.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search