Tables Refresh Solution

Question

Tables Refresh Solution

SQLBuzz

SSC-Addicted

Points: 497
More actions
July 1, 2013 at 11:46 am

#279789

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 2

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 SSC Guru Points: 1003853 More actions · Answer 3

I have a unique method for handling such a thing. 😛 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevin.christiansen SSC Rookie Points: 29 More actions · Answer 4

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 SSC-Addicted Points: 497 More actions · Answer 5

Thanks you ... I am testing in my local machine.