Database woes
Why oh why must Microsoft release such a shoddy management tool for SQL Server 2005? The tool I'm referring to is, of course, the "wonderful" SQL Server Management Studio. The first problem is that it's written in .NET. Now I love .NET, truly, it makes writing programs and web sites so much easier than other frameworks, and I love programming in C#. The problem really is the DataGridView control in .NET 2.0. It renders horribly slow so moving around data sets with it is a pain in the arse. Anyway, that's a small issue compared to my biggest gripe with Management Studio. The stoopid tool just cannot export or import data for anything but dead simple databases.
So here's my dilemma. Trying to move the Community Server database for this blog and my forums to a new web host. Management Studio offers Import / Export data, Copy Database, and Backup / Restore. None work but I'll take you through my ordeal as to why.
Import / Export Data
The first choice really for me because Enterprise Manager for SQL Server 2000 worked wonderfully doing this. Unfortunately Microsoft, in their infinite wisdom, decided to remove the ability to transfer objects, you can only transfer tables and views. Who thought up this crap!? So to get around this deficiency you have to right-click your database, choose Tasks, and Generate Scripts to get a script that creates all the objects in your database sans any data. You run said script on your destination database and then you go ahead with the Import / Export operation.
Not so fast there partner. If you happen to have, say, 103 tables to copy that's a lot of data to move so the Import / Export wizard wisely advises you to check the "Optimize for many tables" checkbox. So you do and, and this is important, if this is not your first time moving data then your target database will already have data in it so you need to also check the option to delete all rows and enable identity insert. So you go ahead with the transfer and you get an error on the first table: cannot insert duplicate rows, violation of primary key. "What?", you say. "I thought I told you to delete all the rows first, if there's no data how can there be duplicates?", you think. No use getting angry cuz there's nothing you can do about it, the stoopid wizard is just not going to delete the rows first so it's always going to fail.
Okay, so after much much, and I mean much, swearing you calm down enough to realize you can just delete all the rows in the target database yourself since the wizard can't do it. I used this script for that, thanks to all newsgroup posters, you guys rock.
-- Disable all constraints
select 'alter table ' + table_schema + '.' + table_name + ' nocheck constraint all'
from information_schema.tables
where
objectproperty(object_id(table_schema + '.' + table_name), 'IsMSShipped') = 0
and table_type = 'BASE TABLE'
-- Generate delete all rows
select 'delete from ' + table_schema + '.' + table_name
from information_schema.tables
where
objectproperty(object_id(table_schema + '.' + table_name), 'IsMSShipped') = 0
and table_type = 'BASE TABLE'
-- Reenable all constraints
select 'alter table ' + table_schema + '.' + table_name + ' with check check constraint all'
from information_schema.tables
where
objectproperty(object_id(table_schema + '.' + table_name), 'IsMSShipped') = 0
and table_type = 'BASE TABLE'
This gives you a result set that you can just copy and paste into a new editor window and execute. First it disables all the constraints on your tables so you can actually delete all the rows in them without warnings and errors, secondly actually deletes them, and thirdly after your data is across reenables constraints. Neat, good. So now I can actually use the Import / Export wizard to transfer the rows and not worry about deleting all the rows first, but enabling identity insert is still important. So the wizard actually completes!! A fool and his elation are soon parted however when he realizes tables with identity columns have all new values in them... 
Stoopid wizard didn't enable identity insert so now my site doesn't work anymore! 
Upon further testing in transferring only one table with identity insert on reveals the wizard does actually do this, but apparently only if you transfer one table. It actually could be higher than that but no way was I going to transfer 103 tables one-by-one.
At this point I've spent damn near the entire day, literally, working on this before finally coming to the conclusion the Import / Export wizard is just useless. To make a long story short I ended up using xSQL Data Compare to get my data across, finally. Another tool of mention is SQL Manager 2005 because it can generate a script that will insert all your data for you! Both products have freeware lite editions but I used them in their trial periods to get full functionality, hehe.
Copy Database
This Management Studio option is just useless because you have to be in the sysadmin server role to use it. Next.
Backup / Restore
Sadly, the data transfer options of Management Studio are so poor that backing up and restoring your entire database on the web host has become the de facto standard for moving your data. This works but does require to coordinate with the web host. Unless you happen to be at one of the web hosts that offers this as an automated feature. Of course, if you're in my case and can't even get your web hosting company to even talk to you *cough ASPnix* then even this option is not for you.
SQL Server Integration Services (SSIS)
Some people might want to chime in that the Enterprise Manager option of transferring objects is still there but just moved to SSIS. Well, yes, but it don't work. I tried everything I could think of to transfer data and I always had my transfer task highlight in yellow with some warning. I gave up.