November 14, 2007 at 11:26 am
I am not a DBA and my company does not have any DBA's, so please forgive me if this question is ignorant.
I have data in my production SQL Server database that uses identity fields for primary key on Table A and Foreign Key in Table B. Can this data be copied from my production server to my developement server so that the Identity values are preserved? If so, how?
Thanks
November 15, 2007 at 6:43 am
How about a backup then restore?
November 15, 2007 at 6:56 am
It can't be done. Backup-restore is the option.
November 15, 2007 at 7:16 am
I was not sure if there was a way to keep the identity fields in synch from database to database. I will use a Backup and Restore.
Thanks
November 16, 2007 at 6:52 am
When I want to copy data from production to test, I set up a DTS package that uses the Transform Data Task. There's an option on that task called Enable Identity Insert. Uncheck that box, and the identity fields should come over unchanged.
Mattie
November 16, 2007 at 7:05 am
Thanks. I was not aware of that option.
November 16, 2007 at 7:54 am
But that option might give u error if u have constraints set up on ur destination database.There is very high risk of breaking things there because if u r just copying one table that is referenced by other tables which has identity than u might have foreign key constraint problem.
November 16, 2007 at 8:01 am
True enough, and the more tables you have, the more you have to look at the order you do things in. You may have to disable RI, or let the Transform Data task do it for (or to) you, because if you let the task create the table, a lot of RI doesn't come through.
The thing with the backup/restore option is that it's great the first time. Then you change your SPs, change some other tables, and now all you want is the most recent transaction table data. That's the situation where a DTS package may be more useful.
Mattie
November 17, 2007 at 4:59 pm
A solid way of what you are trying to accomplish is to create a SQL Script that inserts the data for all fields:
Such AS
-- Record 1
INSERT INTO [YourDB].[dbo].[YourTable]([Id], [Field2], [Field3])
VALUES(1, 'Some Value', 'Some Value')
-- Record 2
INSERT INTO [YourDB].[dbo].[YourTable]([Id], [Field2], [Field3])
VALUES(1, 'Some Value', 'Some Value')
You can use an IDE like Toad for SQL Server to autogenerate the Insert Script. I've used Toad for Oracle and worked great to get data from production to test.
Turn off the identity field off before inserting the records then enable the identity field back on
Hope this helps!!
November 19, 2007 at 6:27 am
I have not heard of Toad for SQL Server. I am going to have to look into this as well as the all of the other suggestions.
Based on the number of different suggestions that I have gotten I am second guessing using identity types in the first place. This does not appear to be as straight forward for a non DBA as I would have liked (I know there is a great debate between natural keys and identity types. Please don't let my last comment rekindle that debate).
Thanks for all of the advice. You have given me a lot to think about.
November 20, 2007 at 11:44 am
The fact that the column in question happens to be an identity datatype is the easy part to get around and does not really have a bearing on the referential integrity issues raised above. You will still have the same potential FK issues using existing 'natural' columns when synching two databases as you will the identity columns.
The method I find easiest is to use MattieNH's suggestion in using a DTS package, but add a first step to drop FK's and disable all constraints first (using scripts you can find in this forum), and a final step to rebuild the FK's and constraints (also using scripts you can find in this forum).
maddog
November 20, 2007 at 12:38 pm
This sounds like a good plan.
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply