August 10, 2007 at 9:38 am
Hi iam very new to sqlserver one of my application vendor has opened a ticket to do the following
can any body help me to do this fast please its very urgent
The scenario is as follows they tried to migrate a data base from sqlserver 2000 to sqlserver 2005 and they got some errors . So they want to import the data from the 2000 data base to 2005 database again. For that he requested me to clean the existing databases on the 2005 server so that they can perform the import process again.
The request he sent is as follows:
August 10, 2007 at 9:49 am
Repeatedly run this statement untill you get no errors :
EXEC SP_msforeachtable 'DELETE ?'
The errors will come from FK constraints (and maybe some triggers).
Each run will drop one level of tables in the constraints chain. So eventually the db will be cleaned.
Make sure you also truncate the logs between each runs if the db is big (compared to the space left on the hd).
August 10, 2007 at 9:55 am
Clean means what ?
Empty database without any objects? In this case:
1. Generate database creation script including all data and log files and secutity objects.
2. Drop existing database
3. Run script generated in item 1.
If clean means database with all objects but tables are empty, disable all foregn keys relationships and then truncate all the table.
August 10, 2007 at 10:13 am
Good advice Mark.
I was following the I need it yesterday comment so I put in the quickest of all solutions to type in and execute... but if the db is huge, definitly not the fastest to execute.
August 10, 2007 at 10:23 am
Yes, and if db is huge he has to create 3 dynamic scripts:
1. Disable FKs
2. Truncate tables
3. Enable FKs
If to properly write these scripts, executing them in a batch will take seconds. And they can be reused if clients will want to re-import db again.
August 12, 2007 at 6:49 pm
Thanks Mark it really helped me a lot .
Your help is required for me in future as well.
Thankyou very much
Pavan Posani
August 13, 2007 at 7:23 am
The subtle part of the dialogue above is very simple. Clarify the request before acting. Knowing what they meant by 'clean' is crucial. Since they wanted to backup and restore from 2000 to 2005, removing all the user tables should have been sufficient. But there's always more than one way to skin the cat.
Also, if this is a vendor's requirement, why are they NOT providing you the necessary scripts for you to execute? That would remove any ambiguity from their request. The fact that they ran into problems suggests that either they didn't give you good instructions, didn't follow all of their own processes correctly, or have missed something that didn't bite them before.
In any event, more education should come out of this exercise - both you and the vendor should be wiser now than before this episode started.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply