Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tables Refresh Solution Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:09 PM
Points: 5, Visits: 68
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

Post #1469173
Posted Monday, July 1, 2013 12:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,471, Visits: 12,329
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)
Post #1469184
Posted Monday, July 1, 2013 12:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469193
Posted Monday, July 1, 2013 6:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 37,101, Visits: 31,653
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1469255
Posted Tuesday, July 2, 2013 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 7:21 AM
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
Post #1469489
Posted Wednesday, July 3, 2013 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:09 PM
Points: 5, Visits: 68
Thanks you ... I am testing in my local machine.

Post #1470156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse