Developing locally on SQL Server 2005, then deploying to shared hosting
What is the best way to develop on a SQL Server 2005 machine locally when you design and design a database and then deploy to a shared host?
In the MySQL / phpMyAdmin world, you have the option to export a table as a DDL statement, with the data presented as a bunch of inserts following the DDL. Using this, you can drop the table, recreate it, and load it with all the data from the query window.
Is there anything close to this in the Microsoft world? Are there any free / open source tools to help with this data transfer?
a source to share
SQL Server Management Studio Express (which is free) includes a Create Scripts option that is referenced by TheTXI.
The Publish Database Wizard is a lighter product (also free) that is specifically designed for creating export scripts for both schema and data.
Caveat - not 100% reliable. I sometimes had to tinker with scripts to get them to work, especially with regard to the order of the dependent objects. It's painful, but still better than starting from scratch.
As RedBeard mentioned, you should keep track of your DDL scripts, in particular the "diff" scripts that will update you from one database revision to the next. In this case, these tools will not be useful during the normal development cycle. However, if you haven't managed your scripts in this way, these tools can get you started and are also good for creating new instances, migrating instances, comparing snapshots, and a lot of other things.
a source to share
Generate scripts for your entire database schema (tables, stored procedures, views, etc.), as well as export your data (if needed), then run those scripts and import the data to your shared host. Depending on this host, you can access it through Management Studio and it will be even easier.
a source to share
Several variants:
Watch out for your database DDL scripts
You have a set of scripts that you can run to create or update your database. In fact, you can pull DDL statements from your SQL Server database. Review the INFORMATION_SCHEMA system views.
For example, to get information about stored procedures and their definitions, take a look at the ROUTINE_DEFINITION field (remember that you will find some other procedures that you did not define, but connect to the sql server):
SELECT SPECIFIC_SCHEMA,SPECIFIC_CATALOG, SPECIFIC_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
Using Visual Studio Database Team Edition (ships with Team Team Edition)
Basically, it does it above and makes it easier to configure and version control your database. It allows you to define data and structure, it also has many unit testing functions.
Backing up and restoring a local database
Backing up a local database, uploading it to your host, restoring the database.
Copy / Move MDF / LDF Files
As with backup / restore, you need to detach your database, copy or move files to your web host, and then reconnect to it.
Use the SQL Server engine to attach MDF / LDF files in the ASP.NET App_Data folder
There should be a few examples of how this is done. It treats the database as a file, but it requires the SQL Server engine to be installed on the web host.
As an example taken from the ASP.NET MVC template (web.config):
<connectionStrings>
<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>
a source to share