What is the best way to develop on SQL Server 2005 machine locally while you get your database design and application design smoothed out and then deploy to a shared host?
In the MySQL / phpMyAdmin world you have an option to export the table as the DDL Statement with the data represented as a bunch of inserts following the DDL. Using this you can drop a table, recreate it, and load it with data all from a query window.
Is there anything close to this in the Microsoft world? Is there any free/open source tools to help with this migration of data?
SQL Server Management Studio Express (which is free) includes the "Generate Scripts" option that TheTXI referred to.
The Database Publication Wizard is a more lightweight product (also free) that is specifically for generating export scripts for both schema and data.
Caveat — neither is 100% reliable. I've sometimes had to tinker with the scripts to get them to run, particularly regarding the order of dependent objects. It's a pain, but still better than starting from scratch.
As RedBeard mentioned, you should be keeping track of your DDL scripts, in particular the "diff" scripts that will upgrade you from one revision of the database to the next. In that case, these tools would not be useful during the normal development cycle. However, if you haven't been managing your scripts this way, these tools can help you get started, and are also good for creating new instances, migrating instances, comparing snapshots, and a host of other things.