Copying 683 Tables Is Harder Than It Looks
-
I'm using the SQL Server Import and Export tool to refresh a dev database with production data (over 20GB worth). I've failed at least 10 times now. Each time, I hit a new issue. The last big one was that it doesn't like to force inserts into fields of type timestamp. Found all those and ignored those fields. Now I keep hitting errors when it tries to truncate tables with foreign keys. You would think an import tool could account for this. I expect another 600 or so failures before I get this working. :sigh:
The way we do it is to keep a script file. Any database changes get added to a script file so if we need to bring down a db from production we do that and then run the script to update the db to development code. Of course that doesn't help you now. :)
There are only 10 types of people in the world, those who understand binary and those who don't.
-
It worked! I now have a dev database with real data to play with. :-D
Woot!! Now recreate all your indices and keys before you get too excited...
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
Woot!! Now recreate all your indices and keys before you get too excited...
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
Pretty sure all the indexes are still intact. It's just the constraints that are buggered. I kept the output of all the deleted FK constraints, so I should be able to create a script against the old database to snag them and then recreate them on the new database (I copied the dev database before making changes).
-
The way we do it is to keep a script file. Any database changes get added to a script file so if we need to bring down a db from production we do that and then run the script to update the db to development code. Of course that doesn't help you now. :)
There are only 10 types of people in the world, those who understand binary and those who don't.
We have lots of teams and ex-coworkers who touch/have touched the DB. Trying to enforce any sort of policy would not be feasible for us.
-
Pretty sure all the indexes are still intact. It's just the constraints that are buggered. I kept the output of all the deleted FK constraints, so I should be able to create a script against the old database to snag them and then recreate them on the new database (I copied the dev database before making changes).
One thing I didn't ask earlier... why didn't you simply detach the other database, make a copy of the mdf and ldf files, and reattach it in your dev environment?
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
One thing I didn't ask earlier... why didn't you simply detach the other database, make a copy of the mdf and ldf files, and reattach it in your dev environment?
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
Some of the dev tables need to remain unchanged. For example, we have different integration with AD between our dev and our production environments. If we overwrote those tables on dev, we would no longer be able to login to our web environments. Also, we are always making changes to dev... don't want to screw up the work of other developers. The big one, though, are the permissions. I don't even want to try dealing with permission issues between environments.
-
I'm using the SQL Server Import and Export tool to refresh a dev database with production data (over 20GB worth). I've failed at least 10 times now. Each time, I hit a new issue. The last big one was that it doesn't like to force inserts into fields of type timestamp. Found all those and ignored those fields. Now I keep hitting errors when it tries to truncate tables with foreign keys. You would think an import tool could account for this. I expect another 600 or so failures before I get this working. :sigh:
The SSMS Export & Import tool is a POS. I use Access to import/export tables. Way better. That is the only reason I use Access.
-
One thing I didn't ask earlier... why didn't you simply detach the other database, make a copy of the mdf and ldf files, and reattach it in your dev environment?
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
yeah. or just restoring a backup? ;P
-
We have lots of teams and ex-coworkers who touch/have touched the DB. Trying to enforce any sort of policy would not be feasible for us.
Quote:
We have lots of teams and ex-coworkers who touch/have touched the DB. Trying to enforce any sort of policy would not be feasible for us.
Sounds like a mess. I feel sorry for you. :)
There are only 10 types of people in the world, those who understand binary and those who don't.
-
The SSMS Export & Import tool is a POS. I use Access to import/export tables. Way better. That is the only reason I use Access.
Please don't tell me you also use an Access database. :((
-
It worked! I now have a dev database with real data to play with. :-D
Party, lets have a party!
Gryphons Are Awesome! Gryphons Are Awesome!
-
I'm using the SQL Server Import and Export tool to refresh a dev database with production data (over 20GB worth). I've failed at least 10 times now. Each time, I hit a new issue. The last big one was that it doesn't like to force inserts into fields of type timestamp. Found all those and ignored those fields. Now I keep hitting errors when it tries to truncate tables with foreign keys. You would think an import tool could account for this. I expect another 600 or so failures before I get this working. :sigh:
I see your problem and feel your pain... Solution: Stop using SQL!
Sorry I couldn't resit that!