SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tables Refresh Solution


Tables Refresh Solution

Author
Message
SQLBuzz
SQLBuzz
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 320
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
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64966 Visits: 17979
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 Modens 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
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75107 Visits: 40985
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 Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222354 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kevin.christiansen
kevin.christiansen
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 320
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