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:
-
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:
AspDotNetDev wrote:
Now I keep hitting errors when it tries to truncate tables with foreign keys.
You need to look at your data structures, and import the tables in the correct order... this will resolve this issue for you. eg: If you have Orders and OrderDetails, don't import OrderDetails before importing Orders.
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!!
-
AspDotNetDev wrote:
Now I keep hitting errors when it tries to truncate tables with foreign keys.
You need to look at your data structures, and import the tables in the correct order... this will resolve this issue for you. eg: If you have Orders and OrderDetails, don't import OrderDetails before importing Orders.
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!!
I wasn't aware I could change the order in the import tool. In any event, some saintly individual posted this online, which should solve my FK issues:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
Thank you, random stranger!
-
I wasn't aware I could change the order in the import tool. In any event, some saintly individual posted this online, which should solve my FK issues:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
Thank you, random stranger!
Well, that didn't work, but I have high hopes for this working:
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
Fingers crossed.
-
Well, that didn't work, but I have high hopes for this working:
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
Fingers crossed.
Hopes crushed again, but I have even higher hopes for this:
while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
SELECT @sql
exec (@sql)
endThanks to this. Hey, it's dev, I don't need to stinkin constraints. :~
-
Hopes crushed again, but I have even higher hopes for this:
while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
SELECT @sql
exec (@sql)
endThanks to this. Hey, it's dev, I don't need to stinkin constraints. :~
And?
Gryphons Are Awesome! Gryphons Are Awesome!
-
And?
Gryphons Are Awesome! Gryphons Are Awesome!
34 million rows copied so far, and about 50 million rows to go. Might actually work this time (probably going to take another half hour to finish). :)
-
And?
Gryphons Are Awesome! Gryphons Are Awesome!
Not sure why the network graph is so varied. I was hoping it would just saturate the connection until it finished, but there are a bunch of lulls.
-
And?
Gryphons Are Awesome! Gryphons Are Awesome!
It worked! I now have a dev database with real data to play with. :-D
-
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!
