Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RobWafle
Starting Member
38 Posts |
 Posted - 2002-05-30 : 13:20:19
|
| Alrighty..I re-programmed my entire database to use "surrogate" keys instead of "natual" keys. We also pre-programmed the applications to use stored procedures instead of "ad-hoc dymamic sql" .. to gain the advantages of security and pre-compilation.. and more.. e.g. Instead of joining on 3 million rows of varchar(50) primary keys I am now joining on 3 million rows of int (4) keys.. my database is much faster.. I am very happy!However,It took probably about three people about three months of full time work to add the appropriate columns, primary keys, foreign keys, etc.. I would like to do the following:1) Copy my existing development database.2) ERASE all the DATA in the COPY. -- I'm stuck here. I'd like to have a script that I can run to generate a scrpt that will turn on "cascade delete" for "every" PK/FK in the database.. (you can't turn them all on because of conflicts.... but I think i could manage to edit the script).. Now, I think i can create this script by using the diagram editor in enterprise manager and manually turning on cascade delete for every table.. and choosing the "save change script" function.. 3) Copy the data into the new database and schema. --I'm ok here, I have to write a script to do this first for tables with no dependencies, then second for tables with dependencies, and third for the tables that depend on those.. and so on.. I would guess this is kinda called a data migration. I only need to do this for one database, but I need to do it in a SHORT period of time, and I need to perform this operation in a test enviornment so I can predict the amount of time it takes me to copy the data, learn what problems may come up, document and automate the process as much as possible. Until I know we can sucessfully copy the data, I don't want to ask my employees to come in with me on the weekend/late hours to perform the migration.Does anyone have any ideas or suggestions? I think that DTS can help me do this. I've written some DTS packages in the past, but I never manage to use any of the features in the DTS package (such as transform data task) ... but I just opened that window and it seems promising.. last time I did a DTS package I was reading data from some crazy file created by a mainframe.. couldn't exactly use the transform data task.. Anyone have any feedback, am I going in the right direction?Thank you in advance for your help!! |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-30 : 13:52:27
|
| if I understand correctly, you don't want to COPY the dev database, rather you want to COPY the schema, so that you can run you dataconversion script to move your prod data into the new schema . . . .If I'm right there, you have two (maybe one) options. If you are on sql 2k, you can write a dts pkg and use the copy sql objects task to move your schema into your new database. . . . if you are not on 2k, you can script out all your tables (including pk,fk,indexes, other constraints, blahblahblah) in em . . .you can then run that script into your new database . . .doing a COPY (read: backup/restore) and then trying to remove the data is really the hard way to skin this cat . . .<O> |
 |
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-06-11 : 14:25:03
|
| yeah.. i watched too many transformers episodes as a kid.. I was sort of stuck on the idea of transforming my old database into my new database when I really just wanted to make an empty copy of my new database and then copy the data from the old schema into the new schema.. |
|
|
|
|
|
|
|
|
Data Migration - Cascade Delete