Aside from the fact Houston has been getting a significant amount of rain this year, it seems that work and personal projects are also experiencing their share of challenges. I wanted to adjust the skin of my blog the day before yesterday and I wanted to try it out offline on a local installation. I also wanted to make a full backup of the database which served multiple purposes including being able to test changes locally. I had made a copy of the data previously using the SQL Server 2005 Export Data DTS mechanism, but it did not contain the views, stored procedures, or relationships.
So I embarked on a task to make a complete local copy of my database since it is stored online through web hosting. Since my current project is keeping my quite busy, I had to start the process late at night and, in hind sight, perhaps that was not wise. Try as I might, I could not convince the Export mechanism to reorder the table transfer sequence to match the required dependencies on the tables. I tried multiple times but I could not find a way to specify the order of the data transfer. Since some of the table required identity insert, I also tried to extract the actual SQL used to perform the transfer (it was late at night and I didn't want to spend the time to do it by hand) but that was unsuccessful as well.
Unfortunately, during one of my failed attempts to export the data I accidentally deleted the very data I was trying to export. It was late at night and I was not a happy camper. To top it off, after this I couldn't get to sleep either because I was so bothered by deleting the data.
Fortunately, I was able to recover the data and all was not lost however it did result in downtime of my blog for a day before I could restore the data. It does seem that I will be writing the data transfer SQL by hand since the SQL Server Export tool is doing such a poor job.