What's the easiest method to develop on SQL Server 2005 machine in your area when you get a database design and application design smoothed out after which deploy to some shared host?

Within the MySQL / phpMyAdmin world you possess an choice to export the table because the DDL Statement using the data symbolized as a lot of card inserts following a DDL. By using this you are able to drop a table, recreate it, and load it with data all from the query window.

Can there be anything near to this within the Microsoft world? Can there be any free/free tools to assist with this particular migration of information?

SQL Server Management Studio Express (that is free) includes the "Generate Scripts" option that TheTXI known to.

The Database Publication Wizard is really a more lightweight product (also free) that's particularly for producing export scripts for schema and data.

Caveat — neither is 100% reliable. I have sometimes needed to mess using the scripts to encourage them to run, particularly concerning the order of dependent objects. It is a discomfort, but nonetheless much better than beginning on your own.

As RedBeard pointed out, you ought to be monitoring your DDL scripts, particularly the "diff" scripts which will upgrade you against one revision from the database to another. For the reason that situation, these power tools wouldn't be helpful throughout the standard development cycle. However, should you weren't controlling your scripts by doing this, these power tools will help you get began, and are generally great for creating new instances, moving instances, evaluating pictures, and a number of other activities.

Generate Scripts of your database schema (tables, saved methods, sights, etc.) as well as do exports of the data (if required) after which run individuals scripts and import the information to your shared host. Based on that host, you have access to access through Management Studio and it might be a level simpler process.

A few options:

Keep an eye on your database's DDL scripts

You've got a group of scripts that may be performed to produce or improve your database. You can really pull DDL claims out of your SQL Server database itself. Try looking in the data_SCHEMA system sights.

Example, to obtain details about saved methods as well as their definitions, consider the ROUTINE_DEFINITION area (bear in mind you will find another methods that you simply did not define, but come built-directly into sql server):

Choose SPECIFIC_SCHEMA,SPECIFIC_CATALOG, SPECIFIC_Title, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.Programs

Use Visual Studio Database Team Edition (Includes Developer Team Edition)

Essentially it will the above mentioned for you personally and streamlines the setup and revision charge of your database. It enables you to definitely define data and structure, provides extensive Unit testing features too.

Backup and Restore of the local database

Backup the local database, upload it for your host, restore the database there.

Copy/Move your MDF/LDF files

Like the backup/restore, you have to remove your database, copy or move the files for your hosting company after which reattach there.

Make use of the SQL Server engine to add MDF/LDF files within the Application_Data folder of ASP.Internet

There must be a couple of good examples of methods this is accomplished. It goodies the database like a file, however it does need a SQL Server engine to become installed on the internet host.

For example, obtained from an ASP.Internet MVC template (web.config):

<connectionStrings>

  <add title="ApplicationServices" connectionString="databasesEquals.SQLEXPRESSIntegrated Security=SSPIAttachDBFilename=DataDirectoryaspnetdb.mdfUser Instance=true" providerName="System.Data.SqlClient"/>

</connectionStrings>

TheTXI's option would be good. Creating a backup and rebuilding additionally, it works, and it is sometimes (with respect to the Web service provider/host's lockdown of MS SQL) your best option.

You may also select a hosting that is shared company that delivers something like myLittleBackup (world wide web.mylittlebackup.com) to ensure that it is simple to install/reinstate your db around the shared server.